免责声明：本文档可能包含第三方产品信息，该信息仅供参考。阿里云对第三方产品的性能、可靠性以及操作可能带来的潜在影响，不做任何暗示或其他形式的承诺。

 

概述

本文主要介绍RDS PostgreSQL如何通过postgres_fdw和dblink插件实现跨库查询。

 

详细信息

提示：

  • postgres_fdw和dblink插件在RDS PostgreSQL 11之前的版本中都不支持跨库查询。
  • 执行创建extension的SQL语句时，在不同版本中所需账号不同。PostgreSQL 9版本不限制账号，只要是高权限账号，或者有createdb及createrole权限即可。但PostgreSQ 10及之后版本，则需要是控制台创建的账号才可以。

 

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)

 

适用于

  • 云数据库 RDS PostgreSQL 版