postgres_fdw外部表实现手册

部署postgres_fdw访问远程数据库

测试机：
1,192.168.1.34 pg9.6.4版本 用作远端
2,192.168.1.26 pg9.6.4版本 用作本地
远端： 在远端创建test123库，建立表aubu_t1（id int）,插入1,2,3,4；
本地端：

1，安装扩展包
在源码目录/postgresql-9.6.4/contrib/postgres_fdw

make  && make install 

复制

2，先用postgres超级用户进入数据库安装模块

aubu=# create extension postgres_fdw;
CREATE EXTENSION

复制

3，查看已安装的模块

aubu=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)

复制

4，创建server

aubu=# grant usage on foreign data wrapper postgres_fdw  to aubu;
GRANT
aubu=> \c aubu aubu                 
You are now connected to database "aubu" as user "aubu".

复制

该server作用是在本地配置一个连接远程的信息，下面的配置是要连接到远程DB名称是postgres数据库

aubu=> create server pgsql_srv foreign data wrapper postgres_fdw options (host '192.168.1.34',port '5432',dbname 'test123');

复制

//如果是GPO，不需要加双引号

5,create mapping user
–for后面的postgres是本地登录执行的用户名,option里存储的是远程的用户密码

aubu=> create  user mapping for public  server pgsql_srv options(user 'postgres',password '');
CREATE USER MAPPING

aubu=# \des
         List of foreign servers
   Name    | Owner | Foreign-data wrapper 
-----------+-------+----------------------
 pgsql_srv | aubu  | postgres_fdw
(1 row)
aubu=# select * from pg_foreign_server ;
  srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                  srvoptions                  
-----------+----------+--------+---------+------------+--------+----------------------------------------------
 pgsql_srv |    16395 |  16394 |         |            |        | {host=148.66.23.34,port=5432,dbname=test123}
(1 row)
aubu=# select * from pg_user_mappings;
 umid  | srvid |  srvname  | umuser | usename |         umoptions         
-------+-------+-----------+--------+---------+---------------------------
 16413 | 16396 | pgsql_srv |      0 | public  | {user=postgres,password=}
(1 row)

复制

更新sever \ user mappings 操作语法：：：

alter server sitdb1_srv options(set host '13.xx.xx.xx');
alter user mapping for public  server db133_srv options(set password '663^66=>re664');
aubu=# \deu+
                  List of user mappings
  Server   | User name |           FDW Options            
-----------+-----------+----------------------------------
 pgsql_srv | public    | ("user" 'postgres', password '')
(1 row)
aubu=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)

复制

6,创建外部表语法
create foreign table ：：

aubu=> create foreign table ft_test(id int) 
server  pgsql_srv  
options (schema_name 'public',table_name 'aubu_t1');
CREATE FOREIGN TABLE

复制

–如果不指定options,数据库会自动匹配相同的表名和表结构，如果有一项不匹配就会报错表或字段不对应的错误
–options可以指定对应的schema和表名等

7,测试

aubu=# select * from ft_test;
 id 
----
  1
  2
  3
  4
(4 rows)

aubu=# insert into ft_test values (5)
aubu-# ;
INSERT 0 1
aubu=# select * from ft_test;
 id 
----
  1
  2
  3
  4
  5
(5 rows)
aubu=# delete from ft_test where id =1;
DELETE 1
aubu=# select * from ft_test;
 id 
----
  2
  3
  4
  5
(4 rows)

aubu=# update ft_test set id = 22 where id = 2;
UPDATE 1
aubu=# select * from ft_test;
 id 
----
  3
  4
  5
 22
(4 rows)

复制

至此配置完成。

三、相关系统表

select * from pg_extension;
select * from pg_foreign_data_wrapper;
select * from pg_foreign_server;
select * from pg_foreign_table;

复制

更新：：：：：

alter user mapping for public  server db133_srv options(set password '663^66=>re664');

复制

四、清理扩展

postgres=# drop foreign table tbl_kenyon;
DROP FOREIGN TABLE
postgres=# drop user mapping for postgres server server_remote_71 ;
DROP USER MAPPING
postgres=# drop server server_remote_71 ;
DROP SERVER
postgres=# drop extension postgres_fdw ;
DROP EXTENSION

复制

五、相关问题

1.ERROR: user mapping not found for “postgres”
检查一下user mapping用户信息，执行用户需要与user mapping的第一个用户相匹配

2.pg_fdw=> select * from tbl_kenyon limit 2;
ERROR: could not connect to server “pg_remote_71”
DETAIL: FATAL: password authentication failed for user “usr_pg_fdw”
检查一下options里面的用户密码与远程用户密码是否匹配