【RDS】RDS PostgreSQL如何通过postgres_fdw和dblink插件实现跨库查询

postgres_fdw插件实现跨库查询

使用postgres_fdw插件实现跨库查询步骤如下。

安装postgres_fdw插件

1、登录RDS PostgreSQL实例，执行如下SQL语句，安装postgres_fdw插件。

create extension postgres_fdw;

2、执行如下SQL语句，查询RDS PostgreSQL实例的所有拓展插件，确认成功安装postgres_fdw插件。

select * from pg_available_extensions;

创建远程Server服务器

1、执行如下SQL语句，创建远程Server服务器。

create server [$Server_Name] FOREIGN data wrapper postgres_fdw OPTIONS(host '[$Host_Name]', port '[$Port]', dbname '[$DB]');

注：

[$Server_Name]为远程Server服务器名称。

[$Host_Name]为另一个实例的内网域名。

[$Port]为另一个实例的内网监听端口。

[$DB]为另一个实例需要远程的库名。

2、执行如下SQL语句，确认创建成功。

SELECT * from pg_foreign_server;

**创建用户匹配信息 **

执行如下SQL语句，给远程Server服务器创建一个用户。

create user mapping for [$Local_User] server [$Server_Name] options(user '[$User]',password '[Password]');

注：

[$Local_User]为当前登录的用户名。

[$User]为远程实例的用户名。

[Password]为远程实例的密码。

创建外部表

参考如下SQL语句，创建外部表，该表要与远程实例中的表名和表结构要相同，其中外部表的字段可以少于远程表，但是字段名要完全一致。

CREATE FOREIGN TABLE [$Table_Name](id int,remark text) server [$Server_Name] options (table_name '[$Table_Name]');

注：[$Table_Name]为需要进行跨库查询的表名。

跨库查询

执行如下SQL语句，进行跨库查询。

select * from [$Table_Name];

dblink插件实现跨库查询

提示：当在ECS实例上自建Postgres数据库时，dblink插件不支持不同实例间的跨库查询。

1、登录RDS PostgreSQL实例，执行如下SQL语句，创建dblink插件。

create extension dblink;

2、执行如下SQL语句，创建到远程库的连接。

select dblink_connect('[$Server_Name]','host=[$Host_Name] port=[$Port] dbname=[$DB] user=[$User] password=[$Password]');

注：如果使用DBlink访问相同实例的不同库，则不需要添加host和port选项。

3、参考如下SQL语句，进行跨库查询。

select *
from dblink
  ('[$Server_Name]','select *
   from [$Table_Name]') as [$Table_Name]([$Type]);

注：[$Type]为表的字段值和类型格式，如下所示。

id int, name varchar(20)