2017.9.15 postgres使用postgres_fdw实现跨库查询

postgres_fdw的使用参考来自：https://my.oschina.net/Kenyon/blog/214953

postgres跨库查询可以通过dblink或者postgres_fdw来完成，二者的比较可以参看文档：https://my.oschina.net/u/270810/blog/174791

1.安装fdw扩展

create extension postgres_fdw;

查询postgres的所有拓展，验证fdw安装成功：

select * from pg_available_extensions; 

2.创建远程server

server_remote是给这个远程server取得名字。options中是连接远程server需要的信息，包括地址、端口和需要连接的数据库名。

create server server_remote 
FOREIGN data wrapper postgres_fdw 
OPTIONS(host '168.2.237.207', port '5432', dbname 'aasccs');

查看所有远程连接，验证server创建成功。

SELECT * from pg_foreign_server;

3.创建用户匹配信息

表示在server_remote下为角色postgres创建一个用户匹配信息，options里是用户名和密码。

create user mapping for postgres server server_remote options(user 'postgres',password 'yourpassword');

4.在本地创建外部表

现在远程连接中需要连接的数据库为aasccs，需要用到的表为t_resource和t_permission，因此在完成上述创建后，就可以在本地创建外部表，表中的记录则完全和远程表中一样。

注：本地t_resource表中字段列数可以少于远程t_resource表，但是字段名必须完成一致，否则会提示错误。

CREATE FOREIGN TABLE t_resource 
(   fd_resid SERIAL,
        fd_res_name TEXT,
        fd_res_desc TEXT,
        fd_tenantid INTEGER, 
        fd_service TEXT, 
        fd_display_name TEXT, 
        fd_modify_date TEXT, 
        fd_modify_person_id INTEGER
)
server server_remote 
options (table_name 't_resource');

CREATE FOREIGN TABLE t_permission
(   id SERIAL,
        fd_groupid INTEGER,
        fd_res_name TEXT,
        fd_resid INTEGER,
        fd_get BOOLEAN,
        fd_post BOOLEAN,
        fd_put BOOLEAN,
        fd_delete BOOLEAN,
        fd_modify_date TEXT, 
        fd_modify_person_id INTEGER
)
server server_remote 
options (table_name 't_permission');

创建完后，可以看到本地也生成了t_resource表和t_permission表。

 5.测试

（1）在本地数据库中运行查询sql

select * from t_resource;

结果：跨库查询成功。

（2）在远程t_resource表中插入一条记录

结果：本地t_resource表中同步多了一条记录。

（3）在本地t_resource表中插入一条记录，使其自动生成id

结果：主键报异常。原因是虽然本地t_resource表中拥有了远程表中一样的数据，但是新增时，主键却依然从1开始自增。而不是接着原来的id顺序。（使用sql新增也同样）

在创建本地t_resource表时尝试加入主键信息，但是创建时候报错了。因为跨库的时候，创建如果使用自增id，会报错。

（4）在本地t_resource表中插入一条记录，其依赖的外键t_tenantid不存在

结果：新增失败，提示外键不存在。所以可见本地表仍然保持了远程表的外键信息。

 

（5）在本地t_resource表中插入一条记录，唯一键冲突

结果：新增失败，提示唯一键冲突。所以可见本地表仍然保持了远程表的唯一键信息。

（6）在本地t_resource表中插入一条数据，避开前面几个冲突，成功插入

结果：远程t_resource表同步多了一条数据。

（7）测试的结论

跨库查询成功；

主键、外键和唯一键都保留了下来。但是新增时主键自增是从本地表的1开始，而不是接着远程表的id，造成新增时id冲突；

远程t_resource和本地t_resource的新增都会同步对方。