Postgresql9.3 New Features of external data encapsulation

Postgresql9.3 New Features of external data encapsulation postgresql released the 9.3 official version this week, which includes writable external data encapsulation, data page checksums, fast failover, streaming-onlyremastering, and so on. It is a great honor for the company to let me try this function that can be written by external data package immediately. Postgresql9

New Features of Postgresql 9.3 external data encapsulation this week postgresql released the official version 9.3, which includes writable external data encapsulation, data page checksums, fast failover, streaming-only remastering, and so on. It is a great honor for the company to let me try this function that can be written by external data package immediately. Originally postgresql 9

New Features of Postgresql 9.3 external data encapsulation

This week, postgresql released the 9.3 official version, which includes writable external data encapsulation, data page checksums, fast failover, streaming-only remastering, and so on. It is a great honor for the company to let me try this function that can be written by external data package immediately. In the past, postgresql 9 provided the ability to remotely read the database, and now it is added to remote writability. Therefore, fdw (Foreign Data Wrappers) allows us to operate a remote database in a local database. This is similar to the previously introduced dblink, but fdw provides more transparent and standard syntaxes and delivers better performance than dblink.

Step 1: Install postgres_fdw Extension

S = # create extension s_s_fdw;

If "create extension" is displayed, the installation is successful.

Step 2: Create a remote database server

01 // create the database zhang and data table world in the remote database vm05.

02

03 postgres = # create database zhang;

04

05 postgres = # \ c zhang

06

07 zhang = # create table world (greeting text );

08

09 // then psql log onto the local database to create a vm05-zhang

10

11 postgres = # create server vm05_zhang foreign data wrapper s_s_fdw options (host '192. 168.0.235 ', dbname 'zhang', port '123 ');

Create server command syntax:

Create server server_name [TYPE 'server _ type'] [VERSION 'server _ version'] foreign data wrapper fdw_name [OPTIONS (option 'value' [,...])]

Server_name: name of the remote database, which must be unique in a database.

Server_type, server_version: optional, may be useful for some external data encapsulation.

Fdw_name: external data package name. Here we are postgres_fdw.

Options: other optional parameters, including the database address, database name, and port.

Step 3: Create a ing user

Create user mapping specifies the USER who connects to the remote database. The data is encapsulated together with the created foreign server remote server. Users with foreign server permissions can create ing users.

1 postgres = # create user mapping for public server vm05_zhang options (password 'xxx ');

Syntax of the create user mapping command:

Create user mapping for {user_name | USER | CURRENT_USER | PUBLIC}

SERVER server_name

[OPTIONS (option 'value' [,...])]

User_name: The existing user maps to the remote server. The USER and CURRENT_USER match the current USER. If no specific USER is available, you can select PUBLIC.

Server_name: the name of the remote database server to which the user is mapped. Step 2 has already been created.

Options: other optional items, including the user name and password.

Example: create user mapping for bob SERVER foo OPTIONS (user 'bob', password 'secret ');

Step 4: Create a remote table

Note that the columns used to create a table must match those in the remote table, preferably of the same type. The number of columns in the remote table can be less, and the sequence can be less important, because fdw matches by name.

1 postgres = # create foreign table vm05_world (greeting text) server vm05_zhang options (table_name 'World ');

Step 5: view results

01 // insert three pieces of data in the vm05_world table

02

03 postgres = # insert into vm05_world values ('take me to your leader ');

04

05 postgres = # insert into vm05_world values ('take me to your leader .........');

06

07 postgres = # insert into vm05_world values ('Hello World !!!!! ');

08

09 // View data in the vm05_world table

10

11 postgres = # select * from vm05_world;

12 greeting

13 ---------------------------------

14 Take me to your leader

15 Take me to your leader .........

16 Hello World !!!!!

17 (3 rows)

18

19 // actually, this is on the vm05 server. We log on to vm05 to check whether there is any data.

20

21 zhang = # select * from world;

22 greeting

23 ---------------------------------

24 Take me to your leader

25 Take me to your leader .........

26 Hello World !!!!!

27 (3 rows)

We can see that the data is successfully inserted. Of course, we can also perform the update and delete from tables vm05_world. If the ing user has this permission.