PostgreSQL（二十五）PG_FDW的使用

目录

一、FDW的简介与特性

二、pg_fdw的部署与使用

1、编译postgres_fdw

2、添加postgres_fdw

3、创建FDW服务器

​4、授权并创建用户映射

5、客户端创建FDW测试表

6、访问外部表

​7、可能出现的问题

三、FDW的执行原理

1、PG-PG访问过程描述

2、PG-PG访问过程查看

3、PG-PG访问过程说明

四、各版本功能演进

1、排序操作

2、聚组操作

3、多表查询

五、本地外部表的访问实验

1、准备csv文件

2、编译、添加扩展file_fdw

3、创建SERVER ON FILE

4、创建外部表

一、FDW的简介与特性

        FDW：Foreign Data Wrappers，在同构和异构数据库之间进行数据的访问。

        PostgreSQL FDW 是一种外部访问接口。通过这个接口，可以让PG访问存储在外部的数据，这些数据可以是外部的pg数据库，也可以oracle、mysql等数据库，甚至可以是文件。

     目前支持的数据库有：pg-pg（pg_fdw），pg-mysql（mysql_fdw），pg-oracle（oracle_fdw）等。

        支持的外部文件有：csv、josn、pg_dump、xml等。

二、pg_fdw的部署与使用

pg_fdw：PostgreSQL Foreign Data Wrappers，PG-PG时需要使用到的插件。

本实验中，wqdb1为本地客户端的库，wqdb2为远程客户端的库。

pg_fdw要在本地客户端部署与使用。

1、编译postgres_fdw

cd /data/package/pg/postgresql-14.8/contrib/postgres_fdw
make
make install

2、添加postgres_fdw

哪个database上使用，就在哪个database上安装。本实验在wqdb1上安装。

必须使用pg超级用户执行。

安装完成后可以查看下安装的信息。

su - postgres
psql
\c wqdb
CREATE EXTENSION postgres_fdw;
SELECT * FROM pg_extension;

3、创建FDW服务器

在本地客户端创建：

        host：远程主机名、ip地址；

        port：远程数据库监听端口；

        dbname：远程访问的数据库的名字。

CREATE SERVER wq_fdw FOREIGN DATA WRAPPER postgres_fdw
OPTIONS(host '$ip',port '$port',dbname 'wqdb2');
\des+

 4、授权并创建用户映射

在本地客户端执行映射语句，将本地用户与远程用户相映射：

        user：远程访问的数据库所属的用户（\d-拥有者）；

        password：远程访问的数据库所属用户的密码。

CREATE USER fdw_u1 PASSWORD 'fdw_u1';
GRANT USAGE ON FOREIGN SERVER wq_fdw TO fdw_u1;
CREATE USER MAPPING FOR fdw_u1 SERVER wq_fdw OPTIONS(user 'postgres',password '$passwd');
\deu+

5、客户端创建FDW测试表

（前情提要：已经在远程客户端创建了一个名叫emp的表，并插入了相关数据。）

在本地客户端，以刚刚映射到的用户pg_fdw1登录并创建fdw测试表；

客户端创建的表结构要和外部表（远程emp表）的结构相一致（\d emp查看）；

schema_name：public，特定schema用户创建的表，则写该schema名字；

table_name：需要访问的表的表名。

psql -U fdw_u1 -d wqdb1；CREATE FOREIGN TABLE emp_fdw(
empno int,
ename varchar(10),
job varchar(10),
mgr int,
hiredate date,
sal float4,
comm float4,
deptno int)
SERVER wq_fdw OPTIONS(schema_name 'public',table_name 'emp');

6、访问外部表

在本地通过访问创建的FDW表来访问外部表

SELECT * FROM emp_fdw;

 7、可能出现的问题

确保前面的创建步骤都没有问题，但是最后一步查询的时候，报错：

wqdb1=> select * from emp_fdw;
2024-08-06 19:20:36.112 CST [8923] ERROR:  could not connect to server "wq_fdw"
2024-08-06 19:20:36.112 CST [8923] DETAIL:  connection to server at "XXX", port XXX failed: 拒绝连接Is the server running on that host and accepting TCP/IP connections?
2024-08-06 19:20:36.112 CST [8923] STATEMENT:  select * from emp_fdw;
ERROR:  could not connect to server "wq_fdw"
DETAIL:  connection to server at "XXX", port XXX failed: 拒绝连接Is the server running on that host and accepting TCP/IP connections?

        可以测试一下，在本地服务器端执行psql -h $ip -p $port命令，能否登录到远程服务器端的pg中。

        如果失败了，可能是远程服务器的实例链接访问控制，限制了本地服务器的访问。可以根据《PostgreSQL（八）》 ，配置本地客户端允许访问即可。

三、FDW的执行原理

1、PG-PG访问过程描述

pg访问外部表时执行的过程，和本地没什么区别，具体流程如下：

（1）Creating a Query Tree：

        创建查询树（访问 pg_catalog.pg_class和pg_catalog.pg_foreign_table）

（2）Connecting to the Remote Server：连接远程的服务器(使用libpq库)

（3）Creating a Plan Tree Using EXPLAIN Commands：

        创建计划树（Optional）(访问pg_catalog.pg_user_mapping和pg_catalog.pg_foreign_server)

（4）Deparesing：

        重建文本（postgres_fdw从通过解析和分析创建的查询树中重新创建一个纯文本文件，在 Postgresql中称为 deparesing。)

（5）Sending SQL Statements and Receiving Result：发送SQL语句并接受结果

2、PG-PG访问过程查看

在远程PG服务器端配置以下参数打开日志，可以查看到访问流程：

//进到参数文件
vim $PGDATA/postgresql.conf//配置日志参数
log_destination='csvlog'
logging_collector=on
log_directory='pg_log'
log_filename='postgresql-%y-% m-%d'
log_truncate_on_rotation=off
log_rotation_age=1d
log_rotation_size=0
log_error_verbosity=verbose
log_statement=all//配置完成后重启数据库
pg_ctl stop
pg_ctl start//进入日志目录，查看日志
cd $PGDATA/pg_log
tail -f postgresql-24-\ m-06.csv

然后在本地服务端执行select 命令，可以在远程端看见日志更新，日志中记录了远程访问的过程。

3、PG-PG访问过程说明

四、各版本功能演进

1、排序操作

        当前实验的pg版本为pg14。在实验版本上，当查询语句包含排序操作时，排序不在本地执行，而是在远程客户端执行，然后只返回结果给本地客户端。这样可以极大地减少网络间的数据传输。

2、聚组操作

        在实验版本上，当查询语句包含聚组操作时，聚组操作不在本地执行，而是在远程客户端执行，然后只返回结果给本地客户端。

        有条件的可以研究一下，通过对比9.6或更早版本中执行、在9.6或以后版本中执行，可以观察到，在查询性能上有非常明显的提升

3、多表查询

        在默认情况下，执行select语句访问远程客户端的表时，如果进行多表查询，pg会将多表查询的操作放到本地服务端来执行。而这种情况下，会导致消耗更多的性能。

        但是可以通过用ALTER SERVER命令将use_remote_estimate选项设置为on的方式，将连接操作在远程执行，以提高性能。

//用postgres用户执行
ALTER SERVER wq_fdw OPTIONS(use_remote_estimate 'on');//用fdw_u1执行
EXPLAIN SELECT e.*,d.* FROM emp_fdw e,fdw_dept d WHERE d.deptno=e.deptno;

五、本地外部表的访问实验

PG也支持访问本地外部表，本实验使用csv文件为例。

1、准备csv文件

在实验数据库wqdb2中导出所需的csv文件

\copy emp TO '/data/pg14/data/pg_tblspc/emp.csv' WITH CSV HEADER;

2、编译、添加扩展file_fdw

cd /data/package/pg/postgresql-14.8/contrib/file_fdw
make
make install
psql
\c wqdb1
CREATE EXTENSION file_fdw;
SELECT * FROM pg_extension;

3、创建SERVER ON FILE

CREATE SERVER pg_file_server foreign data wrapper file_fdw;
\des+

4、创建外部表

外部表的表结构需要与外部文件中表的表结构一致

CREATE FOREIGN TABLE emp_file_fdw(
empno int,
ename varchar(10),
job varchar(10),
mgr int,
hiredate date,
sal float4,
comm float4,
deptno int)
SERVER pg_file_server OPTIONS(filename '/data/pg14/data/pg_tblspc/emp.csv',format 'csv',header 'true',delimiter ',');SELECT * FROM emp_file_fdw;