PostgreSQL技术大讲堂 - 第26讲：PG-FDW部署

时间：2023-08-17 来源：

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