目录
一、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 install2、添加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;
扫一扫
1307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
