postgresql技术大讲堂 -凯发app官方网站

postgresql从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对pg基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容，希望对热爱pg、学习pg的同学们有帮助，欢迎持续关注cuug pg技术大讲堂。

第26讲：pg-fdw部署

内容1 : foreign data wrappers(fdw)简介与特性

内容2 : postgres_fdw部署

内容3 : fdw执行原理

内容4 : 本地外部表操作

foreign data wrappers

foreign data wrappers (fdw)

fdw部署

部署fdw(在客户端部署)

1、编译postgres_fdw

cd /soft/postgresql-12.2/contrib/postgres_fdw

make

make install

2、安装postgres_fdw (哪个database上使用，就在哪个database上安装)

create extension postgres_fdw;

3、创建fdw服务器

create server pgdb foreign data wrapper postgres_fdw

options (host 'pg2',port '1922',dbname 'testdb');

host：远程主机名、ip地址

port：远程数据库监听端口

dbname：远程服务器名字

4、授权

grant usage on foreign server pgdb to pg_fdw1;

5、创建用户映射(本地用户与远程用户映射)

create user mapping for pg_fdw1 server pgdb

options (user 'scott', password 'tiger');

user：远程数据库用户

password：用户密码

6、创建fdw表(以pg_fdw1用户创建)

grant usage on foreign server pgdb to scott_pg;

6、创建fdw表(以pg_fdw1用户创建)

create foreign table emp_fdw (

empno int ,

ename varchar(10),

job varchar(9),

mgr int,

hiredate date,

sal float4,

comm float4,

deptno int

) server pgdb options (schema_name 'public', table_name 'emp');

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

table_name：需要访问的表表名

7、创建fdw表(以pg_fdw1用户创建)

create foreign table dept_fdw (

deptno integer,

dname character varying(14),

loc character varying(13)

)server pgdb options (schema_name ‘public', table_name 'dept');;

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

table_name：需要访问的表表名

8、访问fdw表(以pg_fdw1用户访问)

select * from emp_fdw;

select * from dept_fdw;

*访问fdw表的语法与访问本地表一样。

fdw原理

fdw执行流程

fdw执行流程

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中称为deparsing。)

5、sending sql statements and receiving result

fdw执行流程(pg-pg)

在远程服务器端打开日志，可以查看到访问流程：

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

各个版本功能演进

执行dml操作(pg-pg支持dml操作，其它不支持)

postgresql_fdw不会检测死锁

localdb=# -- client a

localdb=# begin;

begin

localdb=# update tbl_local set data = 0 where id = 1;

update 1

localdb=# update tbl_remote set data = 0 where id = 1;

update 1

localdb=# -- client b

localdb=# begin;

begin

localdb=# update tbl_remote set data = 0 where id = 1;

update 1

localdb=# update tbl_local set data = 0 where id = 1;

update 1

多表查询

version 9.6以前版本

localdb=# explain select * from tbl_a as a, tbl_b as b where a.id = b.id and a.id < 200;

query plan

------------------------------------------------------------------------------

merge join (cost=532.31..700.34 rows=10918 width=16)

merge cond: (a.id = b.id)

-> sort (cost=200.59..202.72 rows=853 width=8)

sort key: a.id

-> foreign scan on tbl_a a (cost=100.00..159.06 rows=853 width=8)

-> sort (cost=331.72..338.12 rows=2560 width=8)

sort key: b.id

-> foreign scan on tbl_b b (cost=100.00..186.80 rows=2560 width=8)

(8 rows)

多表查询

(pg-pg)如果使用alter server命令将use_remote_estimate选项设置为on，则计划器将通过执行explain命令向远程服务器查询计划的成本，此时连接操作在远程进行，提高性能。

alter server pgdb options (use_remote_estimate 'on');

\des

localdb=# explain select * from tbl_a as a, tbl_b as b where a.id = b.id and a.id < 200;

query plan

-----------------------------------------------------------

foreign scan (cost=134.35..244.45 rows=80 width=16)

relations: (public.tbl_a a) inner join (public.tbl_b b)

(2 rows)

排序操作

在9.5或更早版本中：

localdb=# explain select * from tbl_a as a where a.id < 200 order by a.id;

query plan

-----------------------------------------------------------------------

sort (cost=200.59..202.72 rows=853 width=8)

sort key: id

-> foreign scan on tbl_a a (cost=100.00..159.06 rows=853 width=8)

(3 rows)

排序操作

在9.6或以后版本中：

localdb=# explain select * from tbl_a as a where a.id < 200 order by a.id;

query plan

-----------------------------------------------------------------

foreign scan on tbl_a a (cost=100.00..167.46 rows=853 width=8)

(1 row)

聚组函数操作

在9.6或更早版本中：

localdb=# explain select avg(data) from tbl_a as a where a.id < 200;

query plan

-----------------------------------------------------------------------

aggregate (cost=168.50..168.51 rows=1 width=4)

-> foreign scan on tbl_a a (cost=100.00..166.06 rows=975 width=4)

(2 rows)

聚组函数操作

在10或以后版本中：

localdb=# explain select avg(data) from tbl_a as a where a.id < 200;

query plan

-----------------------------------------------------

foreign scan (cost=102.44..149.03 rows=1 width=32)

relations: aggregate on (public.tbl_a a)

(2 rows)

本地外部表

本地外部表

1、--添加扩展

create extension file_fdw;

2、--创建server for file

create server pg_file_server foreign data wrapper file_fdw;

3、--创建外部表，与外部文件结构一致

create foreign table emp_file_fdw

(empno int,

ename varchar(10),

job varchar(9),

mgr int,

hiredate date,

sal int,

comm int,

deptno int)

server pg_file_server

options(filename '/home/postgres/emp.csv',format 'csv',header 'true',delimiter ',');

以上就是【postgresql从小白到专家】第26讲 - pg-fdw部署  的内容，欢迎一起探讨交流钉钉交流群：35，82，24，60，往期视频及文档内容联系cuug