postgres_fdw的使用案例

远端数据库创建测试表：
postgres=# select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.22 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4)
(1 row)
postgres=# show port;
 port 
------
 5432
(1 row)


postgres=# select * from test;
 id 
----
  1
  2
(2 rows)
修改远端数据库pg_hba.conf，添加如下行：
host    all         all         192.168.6.10/24       md5
修改远端数据库postgresql.conf中参数：listen_addresses = '*' 
修改后重启数据库


************************************************************************************
本地数据库执行创建fdw过程：
highgo=# select version();
              version               
------------------------------------
 HighGo Database 3.1.4 Linux 64-bit
(1 row)


highgo=# select kernel_version();
                                                 kernel_version                               
                  
----------------------------------------------------------------------------------------------
 PostgreSQL 9.4.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 
4.4.7-17), 64-bit
(1 row)


highgo=# show port;
 port 
------
 5866
(1 row)
highgo=# create extension postgres_fdw;
CREATE EXTENSION
highgo=# 
highgo=# create server postgres_fdw_server foreign data wrapper postgres_fdw options (host '192.168.6.10', dbname 'postgres', port '5432');
CREATE SERVER
highgo=# 
highgo=# create user mapping for highgo server postgres_fdw_server options (user 'postgres',password 'postgres');
CREATE USER MAPPING
                                                ^
highgo=# create foreign table test01 (id int) server postgres_fdw_server options (table_name 'test');
CREATE FOREIGN TABLE
highgo=# 
highgo=# 
highgo=# select * from test01;
 id 
----
  1
  2
(2 rows)


**************************************************
验证：
在远端数据库插入数据：
postgres=# insert into test values (3); 
INSERT 0 1
postgres=# select * from test;
 id 
----
  1
  2
  3
(3 rows)




在本地数据库查询：
highgo=# select * from test01;
 id 
----
  1
  2
  3
(3 rows)