Configure the fdw--between postgres9.3 to realize the mutual visits between different postgres databases

The following is the installation, configuration, use of FDW to achieve Postgres database mutual access method, reproduced annotated Source:

1, source installation FDW support (requires database source installation)

Cd/usr/local/postgresql-9.3.2/contrib/postgres_fdw
Make
Su
Make install
2. Create FDW extension (log in to Super User database)
\c Postgres Postgres
Create extension POSTGRES_FDW;


3, Authorization (the use of the module to grant user test)
GRANT USAGE on FOREIGN DATA WRAPPER POSTGRES_FDW to test;


4. Create server (log in to database with normal user, create server, connect target database address: 192.168.109.10, Database: D_test, Port: 1921)
\c d_test Test
Create server Srv_test foreign data wrapper POSTGRES_FDW options (host ' 192.168.109.10 ', dbname ' d_test ', Port ' 1921 ');


5. Create User mappings (map the remote database user test and password to the created server)
Create user mapping for test server srv_test options (user ' test ', password ' test ');


6. Create remote table (create local table, point to remote database, can specify schema:test1 of remote table)
Create foreign table L_T3 (C1 int,c2 varchar) server srv_test options (schema_name ' test1 ', table_name ' T3 ');

7. Testing
SELECT * from L_t3;
INSERT into L_T3 values (2.2222,2);

Configure the fdw--between postgres9.3 to realize the mutual visits between different postgres databases