PostgesSQL外部数据封装FDW

POSTGRESQL FDW 的名词是 foreign-data wrapper 外部数据包装, 此模块提供的功能与较旧的 dblink 模块的功能有很大重叠。但是 postgres_fdw 提供了更透明且符合标准的语法来访问远程表，并且在许多情况下可以提供更好的性能。

要准备使用 postgres_fdw 进行远程访问

1. 使用 CREATE EXTENSION 安装 postgres_fdw 扩展。

2. 使用 CREATE SERVER 创建外部服务器对象，以表示您要连接的每个远程数据库。将连接信息指定为服务器对象的选项，但 user 和 password 除外。

3. 使用 CREATE USER MAPPING 为您要允许访问每个外部服务器的每个数据库用户创建用户映射。将要使用的远程用户名和密码指定为用户映射的 user 和 password 选项。

4. 使用 CREATE FOREIGN TABLE 或 IMPORT FOREIGN SCHEMA 为您要访问的每个远程表创建外部表。外部表的列必须与引用的远程表匹配。但是，如果将正确的远程名称指定为外部表对象的选项，则可以使用与远程表不同的表和/或列名。

1. FDW外部数据配置（单表）

1.1 远端数据库创建测试表

postgres=# create database testdb13;
CREATE DATABASE
postgres=# \lList of databasesName    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +|          |          |             |             | postgres=CTc/postgrestemplate1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +|          |          |             |             | postgres=CTc/postgrestestdb13  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)
postgres=# create user u13 with password 'u13';
CREATE ROLE
postgres=# \c testdb13 u13
Password for user u13: 
You are now connected to database "testdb13" as user "u13".
testdb13=> 
testdb13=> create table t1 (id int,name varchar);
CREATE TABLE
testdb13=> insert into t1 values(1,'A');
INSERT 0 1
testdb13=> select * from t1;id | name 
----+------1 | A
(1 row)
testdb13=> \dt+List of relationsSchema | Name | Type  | Owner | Persistence | Size  | Description 
--------+------+-------+-------+-------------+-------+-------------public | t1   | table | u13   | permanent   | 16 kB | 

1.2 安装扩展postges_fdw

已安装扩展

postgres=# \dxList of installed extensionsName   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

安装报错，需先在本地软件安装

postgres=# create extension postges_fdw;
ERROR:  extension "postges_fdw" is not available
DETAIL:  Could not open extension control file "/pgsql/app/pg16/share/extension/postges_fdw.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

进入到编译安装目录，编辑安装postgres_fdw

[root@pgdb ~]# cd /pgsql/soft/postgresql-16.1/contrib/postgres_fdw
[root@pgdb postgres_fdw]# ls
connection.c  deparse.c  expected  meson.build  option.o                    postgres_fdw--1.0.sql  postgres_fdw.control  postgres_fdw.o   shippable.c  sql
connection.o  deparse.o  Makefile  option.c     postgres_fdw--1.0--1.1.sql  postgres_fdw.c         postgres_fdw.h        postgres_fdw.so  shippable.o
[root@pgdb postgres_fdw]# make
[root@pgdb postgres_fdw]# make install

安装扩展postgres_fdw

postgres=# create extension postgres_fdw ;
CREATE EXTENSION
postgres=# \dxList of installed extensionsName     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural languagepostgres_fdw | 1.1     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)

1.3 创建外部服务SERVER

创建一个服务器名pg_fdw_server 使用的类型为postgres_fdw 连接的数据库为 dbname 为 testdb13

postgres=# CREATE SERVER pg_fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.100', port '5432', dbname 'testdb13');
CREATE SERVER

查看创建server

postgres=# \desList of foreign serversName      |  Owner   | Foreign-data wrapper 
---------------+----------+----------------------pg_fdw_server | postgres | postgres_fdw
(1 row)
postgres=# \des+List of foreign serversName      |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                      FDW options                       | Description 
---------------+----------+----------------------+-------------------+------+---------+--------------------------------------------------------+-------------pg_fdw_server | postgres | postgres_fdw         |                   |      |         | (host '192.168.1.100', port '5432', dbname 'testdb13') | 
(1 row)

1.4 创建用户映射USER MAPPING

postgres=# CREATE USER MAPPING FOR postgres SERVER pg_fdw_server OPTIONS (user 'u13', password 'u13');
CREATE USER MAPPING
postgres=# \deu+List of user mappingsServer     | User name |          FDW options           
---------------+-----------+--------------------------------pg_fdw_server | u109      | ("user" 'u13', password 'u13')
(1 row)

1.5 创建远程表FOREIGN TABLE

postgres=# CREATE FOREIGN TABLE fdw_t1(id int,name varchar) SERVER pg_fdw_server OPTIONS (schema_name 'public',table_name 't1');
CREATE FOREIGN TABLE
postgres=# \det+List of foreign tablesSchema | Table  |    Server     |               FDW options               | Description 
--------+--------+---------------+-----------------------------------------+-------------public | fdw_t1 | pg_fdw_server | (schema_name 'public', table_name 't1') | postgres=# select * from fdw_t1;id | name 
----+------1 | A
(1 row)

1.6 数据库更新测试

远端数据库insert数据

postgres=# \c testdb13 u13
Password for user u13: 
You are now connected to database "testdb13" as user "u13".
testdb13=> insert into t1 values(2,'B');
INSERT 0 1

本地数据库访问远程表，实时同步过来。

postgres=# select * from fdw_t1;id | name 
----+------1 | A2 | B
(2 rows)

2. FDW外部数据配置（用户）

2.1 远端数据库创建测试表

testdb13=> create table t2 (id int,name varchar);
CREATE TABLE
testdb13=>  create table t3 (id int,name varchar);
CREATE TABLE
testdb13=> \dt+List of relationsSchema | Name | Type  | Owner | Persistence |    Size    | Description 
--------+------+-------+-------+-------------+------------+-------------public | t1   | table | u13   | permanent   | 16 kB      | public | t2   | table | u13   | permanent   | 8192 bytes | public | t3   | table | u13   | permanent   | 8192 bytes | 
(3 rows)

2.2 创建远程schema

postgres=# create schema remote_fdw_schema;
CREATE SCHEMA

2.3 导入远程schema

postgres=# import foreign schema public from server pg_fdw_server into remote_fdw_schema options ( import_default 'true');
IMPORT FOREIGN SCHEMA

2.4 查看远程schema表

postgres=# \det+ remote_fdw_schema.*List of foreign tablesSchema       | Table |    Server     |               FDW options               | Description 
-------------------+-------+---------------+-----------------------------------------+-------------remote_fdw_schema | t1    | pg_fdw_server | (schema_name 'public', table_name 't1') | remote_fdw_schema | t2    | pg_fdw_server | (schema_name 'public', table_name 't2') | remote_fdw_schema | t3    | pg_fdw_server | (schema_name 'public', table_name 't3') | 
(3 rows)
postgres=# select * from remote_fdw_schema.t2;id | name 
----+------
(0 rows)postgres=# select * from remote_fdw_schema.t1;id | name 
----+------1 | A2 | B
(2 rows)postgres=# select * from remote_fdw_schema.t3;id | name 
----+------
(0 rows)

远端数据库更新数据测试

postgres=# \c testdb13 u13
testdb13=> insert into t2 values(22,'BB');
INSERT 0 1
testdb13=> insert into t3 values(33,'CC');
INSERT 0 1

本地数据库查看远程schema表

postgres=# select * from remote_fdw_schema.t2;id | name 
----+------22 | BB
(1 row)
postgres=# select * from remote_fdw_schema.t3;id | name 
----+------33 | CC
(1 row)

3. FDW运维操作

3.1 外部表设置只读操作

postgres=# insert into remote_fdw_schema.t1 values (3,'C');
INSERT 0 1
postgres=# select * from remote_fdw_schema.t1;id | name 
----+------1 | A2 | B3 | C
(3 rows)

修改为只读操作，false改为true读写模式

postgres=# alter foreign table remote_fdw_schema.t1 options (add updatable 'false');
ALTER FOREIGN TABLE
postgres=# insert into remote_fdw_schema.t1 values (4,'D');
ERROR:  foreign table "t1" does not allow inserts

3.2 删除增加外部表列

删除列

postgres=# select * from remote_fdw_schema.t1;id | name 
----+------1 | A2 | B3 | C
(3 rows)
postgres=# alter foreign table remote_fdw_schema.t1 drop column name;
ALTER FOREIGN TABLE
postgres=# select * from remote_fdw_schema.t1;id 
----123
(3 rows)

增加列

postgres=# alter foreign table remote_fdw_schema.t1 add column name varchar;
ALTER FOREIGN TABLE
postgres=# select * from remote_fdw_schema.t1;id | name 
----+------1 | A2 | B3 | C
(3 rows)

3.3 模式导入部分表

• Limit to 指定哪些表导入

postgres=# \det_ remote_fdw_schema.*List of foreign tablesSchema | Table | Server 
--------+-------+--------
(0 rows)
postgres=# import foreign schema public limit to ( t1,t2) from server pg_fdw_server into remote_fdw_schema options ( import_default 'true');
IMPORT FOREIGN SCHEMA
postgres=# \det_ remote_fdw_schema.*List of foreign tablesSchema       | Table |    Server     
-------------------+-------+---------------remote_fdw_schema | t1    | pg_fdw_serverremote_fdw_schema | t2    | pg_fdw_server
(2 rows)

• Except 指定哪些表不要导入

postgres=# \det_ remote_fdw_schema.*List of foreign tablesSchema | Table | Server 
--------+-------+--------
(0 rows)
postgres=# import foreign schema public except ( t1,t3) from server pg_fdw_server into remote_fdw_schema options ( import_default 'true');
IMPORT FOREIGN SCHEMA
postgres=# \det_ remote_fdw_schema.*List of foreign tablesSchema       | Table |    Server     
-------------------+-------+---------------remote_fdw_schema | t2    | pg_fdw_server
(1 row)