当前位置: gbaiyou->培训 > PostgreSQL技术大讲堂 - 第26讲:PG-FDW部署

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

2023-08-30作者:gbaiyou来源:www.gbaiyou.com

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

  • 11月18日直播!杭州峰会大咖晚宴煮酒论英雄+PG技术大讲堂(34)
  • 11月27日,CUUG新鲜出炉的Oracle DB 19C OCP证书
  • Oracle 19c OCM认证好考吗?CUUG OCM成绩公布
  • 2023年12月,PostgreSQL认证培训红头文件【工信部人才交流中心】
  • 2024年第13届PostgreSQL中国技术大会来啦!
  • OCP认证能不能在家中考试,不去VUE考点考试吗?
  • 恭喜CUUG Guo同学以较高分数通过19c OCM认证考试!
  • Oracle数据库加入AI功能,Database 23c改名为Database 23ai
  • DB-Engines:PostgreSQL is the DBMS of the Year 2023
  • PostgreSQL技术大讲堂 - 第44讲:pg流复制部署
  • PostgreSQL技术大讲堂 - 第45讲:poc-tpcc测试
  • PostgreSQL技术大讲堂 - 第46讲:poc-tpch测试
  • PostgreSQL技术大讲堂 - 第47讲:JMETER工具使用
  • PostgreSQL技术大讲堂 - 第48讲:PG高可用实现keepalived
  • PostgreSQL技术大讲堂 - 第50讲:PG分区表管理
  • 3月30日,工信部人才交流中心PostgreSQL认证考试顺利结束
  • 2024年4月8日,工信人才发布红头文件:PostgreSQL数据库管理人才研修与评测班
  • 恭喜CUUG入选2024年工业和信息化重点领域人才能力评价支撑机构
  • 天津职业技术师范大学《PolarDB开源数据库工作室》授牌仪式顺利完成
  • 温州大学国产开源数据库工作室成功举办PostgreSQL技能培训活动
  • 2024-02-02,恭喜CUUG 刘同学通过Oracle考试获得OCP 19c证书
  • 5月16日,开源驱动教育创新研讨会(青岛站)成功举办-CUUG
  • postgresql技术大讲堂 - 第39讲:数据库完全恢复
  • 温州大学 - 开源国产数据库工作室成立揭牌仪式圆满结束
  • Oracle OCP认证还值得考吗 考OCP证书需要门槛吗
  • 今天(5月6日),CUUG 赵同学收到19c OCM认证考试证书!
  • 5月17日,PolarDB开源数据库沙龙(青岛站)成功举办-CUUG
  • postgresql技术大讲堂 - 第40讲:数据库不完全恢复
  • PostgreSQL技术大讲堂 - 第41讲:表空间备份与恢复
  • PostgreSQL技术大讲堂 - 第42讲:pg_rman部署与使用
  • PostgreSQL技术大讲堂 - 第43讲:流复制原理
  • 1月17日阿里云PolarDB开发者大会PolarDB DevCon
  • 阿里云PolarDB开发者大会圆满结束,CUUG两次获奖
  • 2024-1-12,恭喜CUUG 王同学获得Oracle OCP证书
  • 2024年首张Oracle OCP证书-CUUG胡同学
  • 北京培黎职业学院 - PolarDB开源国产数据库工作室成立揭牌 - CUUG
  • PostgreSQL技术大讲堂 - 第51讲:老陈与德哥聊一聊数据库调优
  • 5月25日,温州大学49名学生参加工信人才PostgreSQL认证考试!
  • 5月30日,PG中级证书来了!工信人才PostgreSQL管理员认证证书!
  • 6月1日,汇华学院12名学生参加工信人才PostgreSQL认证考试!
  • PG技术大讲堂 - 第55讲:通义大模型+向量数据库实现AI的外脑
  • PostgreSQL技术大讲堂 - 第53讲:老陈与德哥开讲PostgreSQL 17新特性
  • PostgreSQL技术大讲堂 - 第54讲:如何在上线前精准评估PG SQL性能
  • 有大奖!第13届PostgreSQL中国技术大会:聚焦云端创新,汇聚智慧共享
  • PolarDB开源社区走进金蝶,开源数据库沙龙成功举办!
  • 阿里云PolarDB再获顶会SIGMOD最佳论文奖
  • 阿里云斩获国际数据库顶会ICDE 2024最佳论文
  • 腾讯云数据库TDSQL荣获深圳市科技进步奖一等奖
  • oracle ocp证书有效期多长时间
  • PostgreSQL技术大讲堂 - 第52讲:与德哥背后的男人们聊如何实现自动性能调优