PGSql 通过postgres_fdw跨库操作远程数据库

postgres_fdw(PostgreSQL Foreign Data Wrappers)，是PostgreSQL 外部数据包装器，类似于 Oracle 的 dblink，DB2 的 Federation，使用其可以将本地数据库与外部数据库建立连接，从而可以像操作本地数据一样来操作远程数据库中的数据。

安装使用

1.安装 postgres_fdw 插件

 在DB管理工具中(比如：navicat)执行一下语句：

create extension postgres_fdw;

执行后出现‘“ok”表示安装成功。

2.创建一个外部服务器

使用 CREATE SERVER 语句创建外部服务器，需要指定远程数据库的主机、端口及数据库名。

create server server_XXX(起一个服务名) 

foreign data wrapper postgres_fdw options(

    host 'XXX.XXX.XXX.XXX',

    port 'XXXX',

    dbname 'XXX'
);

 3.创建用户映射 

使用 CREATE USER MAPPING 语句创建本地用户与远程用户的映射，在 options 中指定远程数据库的用户名和密码。

create user mapping for XXX (本地的数据库用户)

server server_XXX(第2步中创建的服务名) 

options(

        user 'XXX', -- 远程数据库用户名

        password 'XXX' -- 远程数据库密码

);

4.创建外部表

使用 CREATE FOREIGN TABLE 语句创建外部表，在 options 中指定远程数据库的库名和表名。

要注意外部表的字段名称&类型最好和远程表的字段名称&类型保持一致，否则需要使用column_name参数为每一列单独指定远程据库表中的列名。

CREATE FOREIGN TABLE XXX.table_name(起一个表名)(

 "row_id" varchar COLLATE "pg_catalog"."default" NOT NULL,
 "name" varchar COLLATE "pg_catalog"."default" NOT NULL,
 "age" int4,
 ···

) server server_XXX(第2步中创建的服务名)
options  (
    schema_name 'XXX', -- 远程数据库的模式名
    table_name 'XXX' -- 远程数据库的表名
);

到此齐活，执行完以上步骤你会发现你的'XXX'数据库(第4步中创建外部表时指定的库名)里多了一张表，然后就可以像操作本地表一样操作这张外部表(增、删、改、查)，同时对应远程表中的数据会由于你的操作发生变化。

5.扩展

查询安装过的数据库扩展插件：

select * from pg_extension;

清理扩展插件：

drop extension postgres_fdw ;

 清理上面步骤创建的外部表、用户映射、服务：

drop foreign table XXX;

drop user mapping for XXX(第3步中指定的本地的数据库的用户)
server server_XXX; -- 创建的服务名

drop server server_XXX; -- 创建的服务名