本文主要是介绍炸裂,PG的FDW又进化了!,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
📣📣📣
哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】!😜😜😜
中国DBA联盟(ACDU)成员,目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发,
备份恢复,安装迁移,性能优化、故障应急处理等。
📣📣📣
文章目录
- 📣 1.什么是FDW?
- 📣 2.FDW工作流
- 📣 3.file_fdw
- ✨ 3.1 安装插件
- ✨ 3.2 生成file_fdw服务器
- ✨ 3.3 创建外部表
- 📣 4.postgres_fdw
- ✨ 4.1 创建安装插件
- ✨ 4.2 权限配置
- ✨ 4.3 创建外部服务
- ✨ 4.4 创建映射用户
- 4.5 创建外部表
- ✨ 4.5 其它特性
- 📣 5.总结
FDW是外部数据包装器,基于PG有这个功能,如果想要数据融合,很容易在PG上实现,只要开发对应的FDW功能即可.
📣 1.什么是FDW?
FDW (foreign-data wrapper,外部数据包装器),PostgreSQL FDW 是一种外部访问接口,它可以被用来访问存储在外部的数据,这些数据可以是外部的pg数据库,也可以oracle、mysql等数据库,甚至可以是文件。
Specific SQL Database Wrappers
基本上每种数据库、数据类型都有各自的fdw插件,访问oracle库有oracle_fdw,
访问mysql库有mysql_fdw等等。fdw插件可以直接安装或者下载安装
可以让我们在PostgreSQL 中使用SQL查询极为丰富的外部数据:
主流关系型数据库:Oracle、MySQL、SQL Server等
NoSQL数据库:ClickHouse、MongoDB、Redis、Neo4j等
外部文件:csv、josn、pg_dump、xml
Web文件:S3、Twitter、Facebook…
📣 2.FDW工作流
1.词法分析器为输入的SQL语句创建查询树
2.SQL的执行器通过网络连接到源端的数据库服务器
3.如果启用了use_remote_estimate选项,执行器会执行explain命令评估执行计划
4.执行器根据最优的执行计划,重新创建SQL语句
📣 3.file_fdw
file_fdw插件为PG数据库提供了访问外部数据的能力,比如我们常见的文件csv,log等,该插件是内置在PG源码的contrib中。
✨ 3.1 安装插件
cd /pgccc/soft/postgresql-15.6/contrib
–编译安装
[root@centos79 contrib]# cd file_fdw
[root@centos79 file_fdw]# make install
–再次确认插件
[root@centos79 ~]# cd /pgccc/pgsql-15/share/extension
–安装插件
postgres=# CREATE EXTENSION file_fdw;
✨ 3.2 生成file_fdw服务器
–先生成一个file_fdw服务器。
CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;
✨ 3.3 创建外部表
-- 创建一个名为"example"的新表
CREATE TABLE example (id SERIAL PRIMARY KEY,name VARCHAR(100),age INT
);-- 插入数据到"example"表中
INSERT INTO example (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 35);-- 查询"example"表中的所有数据
SELECT * FROM example;--导出到文件
\copy example to '/home/postgres/example.csv' with csv header delimiter ','--创建一个外部数据表
CREATE FOREIGN TABLE csvTable (
id SERIAL,
name VARCHAR(100),
age INT
) SERVER file_fdw_server
OPTIONS (format 'csv', header 'true', filename '/home/postgres/example.csv', delimiter ',', null'');csv文件放在了根目录下面。
csv里面有4列,从左往右命令。还可以设置有没有列名选择
header 'true' 有列名,csv的第一行不作为表的数据 header 'false' 没有列名,csv的第一行也作为表的数据
–再次查询,完全和一般的表类似
postgres=# select * from csvTable;
📣 4.postgres_fdw
通过 postgres_fdw访问远程PostgreSQL数据库表。
步骤如下:
在源端(本地库)创建 postgres_fdw 插件
创建 foreign server 外部服务(即:指连接外部数据源的连接信息)
创建映射用户(映射用户指定了访问外部表的本地用户和远程用户信息)
创建外部表(外部表的表定义建议和远端表结构一致)
✨ 4.1 创建安装插件
本地库做如下的操作:
–编译安装
[postgres@centos79 ~]$ cd /pgccc/soft/postgresql-15.6/contrib
[postgres@centos79 contrib]$ cd postgres_fdw
[postgres@centos79 postgres_fdw]$ make install
–再次确认插件
[root@centos79 ~]# cd /pgccc/pgsql-15/share/extension
–postgres 超级用户登录 PostgreSQL
[postgres@centos79 ~]$ psql
postgres=# CREATE EXTENSION postgres_fdw;
postgres=# \dx
✨ 4.2 权限配置
本地库做如下的操作:
若使用超级用户使用postgres_fdw可以跳过
普通用户使用postgres_fdw需要单独授权
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO pgtest;
✨ 4.3 创建外部服务
本地库做如下的操作:
外部服务定义了远端PostgreSQL数据库的IP、端口、数据库连接信息
CREATE SERVER fdwpg2 FOREIGN DATA WRAPPER
postgres_fdw OPTIONS (host ‘pg2host’, port ‘5432’, dbname ‘devdb’);
✨ 4.4 创建映射用户
本地库做如下的操作:
映射用户指定了连接源端 PostgreSQL 数据库的用户名和密码信息
CREATE USER MAPPING FOR pgtest
SERVER fdwpg2 OPTIONS (user ‘pg2user’, password ‘pg2user’);
FOR:配置的用户为本地的数据库用户
OPTIONS :配置的是远端PostgreSQL数据库的用户和密码
4.5 创建外部表
--远端数据库创建测试表
CREATE TABLE pg2_fdw (id int4 ,info text) ;
INSERT INTO pg2_fdw (id , info ) VALUES (1, 'a'),(2, 'b');---创建外部表(本地库)
CREATE FOREIGN TABLE ft_fdw (
id int4,
info text
) SERVER fdwpg2 OPTIONS (schema_name 'pg2user', table_name 'pg2_fdw');通过外部表访问远端数据表
注意:远端数据库pg_hba.conf文件需要允许本地库访问策略
select * from pg2_fdw;
✨ 4.5 其它特性
支持可写特性
从PostgreSQL 9.3 版本开始支持postgres_fdw 外部表可写的条件
创建映射用户时配置的远端数据库用户需要对远端表具有写权限
PostgreSQL 必须是 9.3及以上版本。
聚合函数下推
PostgreSQL 10 版本在 postgres_fdw 扩展模块中新增了一个可以将聚合、
关联操作下推到远端 PostgreSQL 数据库进行的特性。
它大幅度减少了从远程库传送到本地库的数据量,
提升了 postgres_fdw 外部表上聚合查询的性能。
📣 5.总结
FDW的其它插件支持外部表方式访问mysql,oracle等数据库表数据,PG的FDW可以根据业务开发各种FDW把数据进行融合,比如数据迁移、异构DBMS之间的联动,是一个做数据整个的非常棒的功能
这篇关于炸裂,PG的FDW又进化了!的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!