PostgreSQL数据外部表使用（postgres_fdw）

阿里云文档请查看：https://help.aliyun.com/document_detail/142422.html

一、postgres_fdw简介

postgres_fdw模块提供了外部数据包装器postgres_fdw，它可以被用来访问存储在外部PostgreSQL服务器中的数据。

这个模块提供的功能大体上覆盖了较老的dblink模块的功能。但是postgres_fdw提供了更透明且更兼容标准的语法来访问远程表，并且可以在很多情况下给出更好的性能。

二、postgres_fdw插件安装

1、安装插件

pg自带postgres_fdw插件，但是PG默认没有安装插件，需要执行语句进行插件的安装：

-- 安装postgres_fdw插件
CREATE EXTENSION postgres_fdw;

2、查询已安装插件

我们可以通过以下语句查询数据库已安装的插件，执行结果将返回数据库所有已安装的插件。

SELECT * FROM pg_extension;

3、创建外部服务器对象

我们使用postgres_fdw插件，需要创建对应的外部服务器对象。它用来表示你想连接的每一个远程数据库。指定除了user和password之外的连接信息作为该服务器对象的选项，创建语句如下：

create server server_pgsql_fdw foreign data wrapper postgres_fdw options(host '192.168.17.35',port '5432',dbname 'postgres');

如上SQL语句中我们指定了server对象对应的服务为postgres_fdw，指定了需要连接的数据库IP，端口号和数据库。

4、创建用户映射

我们在上面已经创建了外部服务器并指定了要连接的外部数据库的信息，但是我们并没有指定外部服务器的用户信息，所以我们接下来要创建一个用户映射。每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项。

create user mapping for public server server_pgsql_fdw options(user 'postgres',password '123456'); 

5、创建外部表

我们已经创建了外部表服务和用户映射，接下来我们将要创建一个外部表，用来访问外部数据库的表数据。外部表的列必须匹配被引用的远程表。但是，如果你在外部表对象的选项中指定了正确的远程名称，你可以使用不同于远程表的表名和/或列名。

CREATE FOREIGN TABLE "public"."fdw_user" (
  "id" varchar(36) COLLATE "pg_catalog"."default" NOT NULL,
  "user_name" varchar(255) COLLATE "pg_catalog"."default",
  "moblie" varchar(255) COLLATE "pg_catalog"."default",
  "create_time" date
)
SERVER "server_pgsql_fdw"
OPTIONS (schema_name 'public',table_name 'user')
;

现在你只需要从一个外部表SELECT来访问存储在它的底层的远程表中的数据。你也可以使用INSERT、UPDATE或DELETE修改远程表，如果你执行了修改操作，本地外部表和远程表中的数据均会发生修改。

注意：一个外部表可以被声明比底层的远程表较少的列，或者使用一种不同的列序。与远程表的列匹配是通过名字而不是位置进行的。

三、外部表其他操作

删除外部表服务：

DROP SERVER foreign_server;

修改外部表服务选项

ALTER SERVER foreign_server OPTIONS (host '127.0.0.1', dbname 'postgres1');
ALTER SERVER foreign_server VERSION '10.4' OPTIONS (SET host '127.0.0.1');

删除用户映射

DROP USER MAPPING  FOR 'user_name' SERVER foreign_server;

修改用户映射

ALTER USER MAPPING FOR user_name SERVER foreign_server OPTIONS (user 'foreign_server', password '123456');

修改外部表

ALTER FOREIGN TABLE "public"."fdw_user" ADD COLUMN "aaa" varchar(255);

修改外部表选项

ALTER FOREIGN TABLE public.fdw_user OPTIONS (ADD opt1 'value1', SET opt2, 'value2', DROP opt3 'value3');

———————

—————————

版权声明：本文为CSDN博主「zss世子」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/u012867993/article/details/112675776