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

 

概述

本文主要介绍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 版