跨库操作dblink和postgres_fdw

插件介绍：
使用dblink和postgres_fdw可以实现跨库操作其他PostgreSQL库。

系统要求

PostgreSQL 9.5+
与要连接的其他PostgreSQL网络连通

dblink

1、新建dblink插件。

CREATE EXTENSION dblink;

2、连接远程数据库

--SELECT dblink_connect('<connname>', '<connstr>');
SELECT dblink_connect('mydb', 'dbname=postgres port=5432 host=localhost');
 dblink_connect 
----------------
 OK
(1 row)

该函数有两个参数:connname和connstr，其中connname是可选参数。
connname：要用于这个连接的名字。如果被忽略，将打开一个未命名连接并且替换掉任何现有的未命名连接。
connstr：数据库连接串信息，格式为：hostaddr=<IP或域名> port=<端口号> dbname=<数据库名> user=<用户名> password=<密码>
3、执行sql命令

--执行查询
SELECT * FROM dblink('mydb', 'select * from test') as test(id integer, info varchar(8));
 id | info 
----+------
 1 | a
 1 | b
(2 rows)
--执行插入
SELECT dblink_exec('mydb', 'insert into test values (3,''c'');');
 dblink_exec 
-------------
 INSERT 0 1
(1 row)
SELECT * FROM dblink('mydb', 'select * from test') as test(id integer, info varchar(8));
 id | info 
----+------
 1 | a
 2 | b
 3 | c
(3 rows)

4、查询连接状态

SELECT dblink_get_connections();
 dblink_get_connections 
------------------------
 {mydb}
(1 row)

5、关闭远程连接

SELECT dblink_disconnect('mydb');

更多内容请参考dblink

postgres_fdw

1、新建postgres_fdw插件

CREATE EXTENSION postgres_fdw;

2、然后使用CREATE SERVER创建一个外部服务器

CREATE SERVER foreign_server
 FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (host 'x.x.x.x', port '5432', dbname 'foreign_db');

3、使用CREATE USER MAPPING一个用户映射,每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。

--指定要映射到外部服务器的本地数据库用户名local_user
CREATE USER MAPPING FOR local_user
 SERVER foreign_server
 OPTIONS (user 'foreign_user', password 'password');

4、为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表

CREATE FOREIGN TABLE foreign_table (
 id integer,
info varchar(8)
)
 SERVER foreign_server
 OPTIONS (schema_name 'some_schema', table_name 'some_table');

5、查询

SELECT * FROM foreign_table;
 id | info 
----+------
 1 | a
 2 | b
 3 | c
(3 rows)

更多内容请参考postgres_fdw