两台主机，192.168.11.67
192.168.11.70
67机器上的
[postgres@pg contrib]$ psql
psql (9.4.4)
Type "help" for help.
postgres=# select * from pg_foreign_data_wrapper;
fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
--------------+----------+------------+--------------+--------+------------
dblink_fdw | 10 | 0 | 16965 | |
postgres_fdw | 10 | 16968 | 16969 | |
(2 rows)
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+--------------------------------------------------------------
dblink | 1.1 | public | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
(3 rows)
postgres=# create server server_remote_70 foreign data wrapper postgres_fdw options(host '192.168.11.70',port '5432',dbname 'postgres');
CREATE SERVER
postgres=# select * from pg_foreign_server ;
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
------------------+----------+--------+---------+------------+--------+------------------------------------------------
server_remote_70 | 10 | 16970 | | | | {host=192.168.11.70,port=5432,dbname=postgres}
(1 row)
postgres=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
------------------+----------+----------------------
server_remote_70 | postgres | postgres_fdw
(1 row)
--for后面的postgres是本地登录执行的用户名,option里存储的是远程的用户密码
postgres=# create user mapping for postgres server server_remote_70 options(user 'postgres',password 'postgres');
CREATE USER MAPPING
postgres=# select * from pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+------------------+--------+----------+-----------------------------------
17022 | 17021 | server_remote_70 | 10 | postgres | {user=postgres,password=postgres}
(1 row)
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+---------------+----------
public | a | table | postgres
public | tbl_fdw | foreign table | postgres
(2 rows)
postgres=# CREATE FOREIGN TABLE tbl_fdw(id int) server server_remote_70 options (schema_name 'public',table_name 'tb1');
CREATE FOREIGN TABLE
postgres=# select * from tbl_fdw ;
id
----
1
2
3
(3 rows)
70中的数据
postgres=# \d tb1
Table "public.tb1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
postgres=# select * from tb1;
id
----
1
2
3
(3 rows)
| 
转播
