Postgresql 9.3 New features for external data encapsulation

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

This week PostgreSQL released 9.3 Official editions that contain writable external data encapsulation, data page checksums, fast failover, and streaming-only remastering, among others. It's an honor, the company's first time let me try the external data can be written to write this feature. Originally PostgreSQL 9 provides the ability to read the database remotely, and now it's time to join the remote writable. So by FDW (Foreign Data Wrappers), it's easy to tell us how to operate a remote database in a local database. This is similar to the Dblink described earlier, but FDW provides more transparency and standard syntax, and better performance than Dblink.

Step One: Install POSTGRES_FDW extensions

postgres=# Create extension POSTGRES_FDW;

If you are prompted for "CREATE EXTENSION", the installation is successful.

Step Two: Create a remote database server

First create a new database in the remote database vm05 Zhang and datasheet world.

postgres=# CREATE database Zhang;

postgres=# C Zhang

zhang=# CREATE TABLE World (greeting text);

Then psql log into the local database to create Vm05-zhang

postgres=# Create server Vm05_zhang foreign data wrappers POSTGRES_FDW options (host ' 192.168.0.235 ', dbname ' Zhang ', Port ' 3 500 ');

Create server command syntax:

CREATE SERVER server_name [TYPE ' Server_type '] [VERSION ' server_version '] FOREIGN DATA wrappers Fdw_name [OPTIONS (op tion ' value ' [, ...] ]

SERVER_NAME: The name of the remote database that must be unique in a database.

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

Fdw_name: The external data is divided into names, here we are POSTGRES_FDW.

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

Step three: Create a mapped user

The Create user mapping specifies the user who connects to the remote database, which is encapsulated with the foreign server remote servers that are created. Users with Foreign server permissions can create mapped users.

postgres=# Create user mapping for public server Vm05_zhang options (password ' xxx ');

Create User Mapping command syntax:

CREATE USER MAPPING for {user_name | USER | Current_User | public}

SERVER server_name

[OPTIONS ' value ' [, ...]]

User_name: Existing users are mapped to remote server. User and Current_User match current users and can choose public without the availability of a specific user.

SERVER_NAME: The name of the remote database server that currently requires the user to map to, and the second step has been created.

Options: Other optional image, including username, password, etc.

For example: CREATE USER MAPPING for Bob SERVER foo OPTIONS (USER ' Bob ', Password ' secret ');

Step Fourth: Create a remote table

Notice here that the columns to create the table match the columns of the remote table, preferably the same type. The columns of the remote table can be less, and the order is not important, because the FDW match by name.

postgres=# Create foreign table Vm05_world (greeting text) server Vm05_zhang options (table_name ' world ');

Step Fifth: View effects

Insert three data into the Vm05_world table

postgres=# INSERT into Vm05_world values (' Take me to your leader ');

postgres=# INSERT into Vm05_world values (' Take me to your leader ... ');

postgres=# INSERT into Vm05_world values (' Hello world!!!!! ');

View the data in the Vm05_world table

postgres=# select * from Vm05_world;

Greeting

---------------------------------

Take me to your leader

Take me to your leader ....

Hello WORLD!!!!!

(3 rows)

In fact this is on the VM05 server, we log in vm05 to see if there is data.

zhang=# SELECT * from the world;

Greeting

---------------------------------

Take me to your leader

Take me to your leader ....

Hello WORLD!!!!!

(3 rows)

You can see that the insert data is successful, of course we can also vm05_world the update and delete from table if the mapped user has this permission.

This POSTGRES_FDW external data encapsulation can be written in formal test completion, useful for operating multiple tables. Summary of the relatively superficial, there is no clear reference to PostgreSQL official documents:

Https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Writeable_Foreign_Tables