PG中的file_fdw、postgres_fdw和dblink
Tags： DBLinkfile_fdwPGpostgres_fdwPostgreSQL插件跨库访问
简介
SQL/MED(SQL Management of External Data ）
PostgreSQL可以使用SQL/MED特性功能通过SQL语句访问外部数据源的数据。实现类似Oracle dblink的功能。
数据流向图
支持访问的外部数据源类型
文件(file_fdw)
在 PostgreSQL 数据库中访问数据库主机文件，文件需具备一定的格式，如：CSV和TEXT。它需要file_fdw插件扩展支持。
数据库
关系型数据库
在 PostgreSQL 数据库中访问远程的数据库，例如 PostgreSQL，Oracle ，MySQL，SQL Server 等。
非关系型数据库
MongoDB 、Red is 、Cassandra 等非关系型数据库
大数据
ElasticSearch 、Hadoop 等
file_fdw介绍
简介
file_fdw模块提供外部数据包装器file_fdw， 它能被用来访问服务器的文件系统中的数据文件，或者在服务器上执行程序并读取它们的输出。 数据文件或程序输出必须是能够被COPY FROM读取的格式， 详见COPY。当前只能读取数据文件。
通过file_fdw创建外部表时可指定的参数有：
- filename：指定要被读取的文件。必须是一个绝对路径名。 必须指定filename或program， 但不能 同时指定两个。
- program：指定要执行的命令。该命令的标准输出将被读取， 就像使用COPY FROM PROGRAM一样。 必须指定program或filename，但不能同时指定两个。
- format： 指定数据的格式，和COPY的FORMAT选项相同。
- header： 指定数据是否具有一个头部行，和COPY的HEADER选项相同。
- delimiter： 指定数据的定界符字符，和COPY的DELIMITER选项相同。
- quote： 指定数据的引用字符，和COPY的QUOTE选项相同。
- escape： 指定数据的转义字符，和COPY的ESCAPE选项相同。
- null： 指定数据的空字符串，和COPY的NULL选项相同。
- encoding： 指定数据的编码，和COPY的ENCODING选项相同。
注意虽然COPY允许诸如HEADER的选项不用一个相应的值指定， 但是外部表选项语法要求在所有情况下都出现一个值。要激活通常写入没有值的 COPY选项，你可以传递值 TRUE，因为所有这些选项都是布尔值。
使用这个包装器创建的表的一列可以具有下列选项：
force_not_null这是一个布尔选项。如果为真，它指定该列的值不应该与空字符串匹配（也就是表级别的
null选项）。这和把该列放在COPY的FORCE_NOT_NULL选项中具有相同的效果。force_null这是一个布尔选项。如果为真，它指定匹配空值字符串的列值会被返回为
NULL， 即使该值被引号引用。如果没有这个选项，只有匹配空值字符串的未被引用的值会被返回为NULL。这和在COPY的FORCE_NULL选项中列出该列有同样的效果。
COPY的FORCE_QUOTE选项当前不被file_fdw支持。
这些选项只能为一个外部表及其列指定，而不能在file_fdw外部数据包装器的选项中指定，也不能在使用该包装器的服务器或者用户映射的选项中指定。
出于安全原因，改变表级别的选项要求超级用户特权或 具有默认角色pg_read_server_files（使用文件名）或 默认角色pg_execute_server_program （使用程序）的权限： 只有特定用户能够控制读取哪个文件或者运行哪个程序。 原则上普通用户可以被允许改变其它选项，但是当前还不支持这样做。
当指定program选项时，请记住，选项字符串是通过shell执行的。 如果想传递任何参数到来自不受信任的源的命令， 必须小心去掉或转义任何对shell来说可能有特殊含义的字符。安全起见，最好使用固定的命令字符串， 或者至少避免传递任何用户输入。
对于一个使用file_fdw的外部表，EXPLAIN显示要读取的文件名或要运行的程序。对于文件来说，除非指定COSTS OFF，否则文件尺寸（以字节计）也会被显示。
目前基于 file_fdw 的外部表仅支持只读，不支持 INSERT/UPDATE/DELETE 操作。
file_fdw示例
创建测试表，添加测试数据
1 2 3 4 5 | postgres=# create table tb10(id integer,name character varying,passworld character varying); CREATE TABLE postgres=# postgres=# insert into tb10 select generate_series(1,50),'john',md5(random()::text); INSERT 0 50 |
通过copy拷贝成文件
1 2 | postgres=# copy tb10 to '/tmp/tb10.csv'; COPY 50 |
创建SERVER（外部服务器）
1 2 3 4 5 6 7 8 | postgres=# create server server_file_fdw foreign data wrapper file_fdw; CREATE SERVER postgres=# \des List of foreign servers Name | Owner | Foreign-data wrapper -----------------+----------+---------------------- server_file_fdw | postgres | file_fdw |
注：以 postgres 用户连接虚拟机上的库 mydb, 并创建 SERVER;Server 可以理解为外部服务器，是本地库访问外部数据的桥梁。
创建外部表
1 2 | postgres=# create foreign table foreign_tb10 (id integer,name character varying,password character varying)server server_file_fdw options (filename '/tmp/tb10.csv'); CREATE FOREIGN TABLE |
备注：这里创建外部表，并指定外部 Server 为 server_file_fdw，在测试过程中，发现外部表有很多限制,目前发现了以下：
- default 值不支持；
- 索引不支持；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | postgres=# create index idx on foreign_tb10(id); ERROR: cannot create index on foreign table "foreign_tb10" postgres=# postgres=# \d foreign_tb10 Foreign table "public.foreign_tb10" Column | Type | Modifiers | FDW Options ----------+-------------------+-----------+------------- id | integer | | name | character varying | | password | character varying | | Server: server_file_fdw FDW Options: (filename '/tmp/tb10.csv') postgres=# select * from foreign_tb10 order by id limit 10; id | name | password ----+------+---------------------------------- 1 | john | 00ef7c39d7b955ab1d152d212df8219b 2 | john | 1fa525f371f8283dcd411c5681fdda9d 3 | john | 3f0b3e8a9a272786a4e9d6d3bd1024bf 4 | john | dcb12e120adc3bada87ea1a7998ea7c5 5 | john | 87f5536ba8e875592aa34ba50df15b7d 6 | john | a5c16e9a67999d2a9dbffbe2fce6e89b 7 | john | 2ad65c548d49a9044ef2623a65d943fb 8 | john | 2da48f614feb78573266963a2e8dc746 9 | john | 329b0dff8b5c9967591d725a224113e1 10 | john | 8048a6b38d63e790cf07d79c1328b8cb (10 rows) |
执行计划
1 2 3 4 5 6 7 8 9 10 | postgres=# explain select * from foreign_tb10 order by id limit 10; QUERY PLAN ------------------------------------------------------------------------------ Limit (cost=3.55..3.58 rows=10 width=68) -> Sort (cost=3.55..3.61 rows=21 width=68) Sort Key: id -> Foreign Scan on foreign_tb10 (cost=0.00..3.10 rows=21 width=68) Foreign File: /tmp/tb10.csv Foreign File Size: 2041 b (6 rows) |
可以看到，数据是从外部文件扫描获取，文件的位置，大小也有展示。
file_fdw查看日志
例 F.1. 为 PostgreSQL CSV 日志创建一个外部表
一种file_fdw的用法是把可用的 PostgreSQL 活动日志变成一个表用于查询。 要这样做，首先你必须写日志到一个CSV文件,，这里我们将它称为pglog.csv。 首先，将file_fdw安装为一个扩展：
1 | CREATE EXTENSION file_fdw; |
然后创建一个外部服务器：
1 | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw; |
现在你已经准备好创建外部数据表。使用CREATE FOREIGN TABLE命令，你将需要为该表定义列、CSV 文件名以及格式：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | CREATE FOREIGN TABLE pglog ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text, backend_type text ) SERVER pglog OPTIONS ( filename 'log/pglog.csv', format 'csv' ); |
就是这样了 — 现在你能够直接查询你的日志了。当然，在生产中你会需要定义一些方法来处理日志轮转。
PG13注意事项
因为pg13中在日志中新增加了记录backend type的数据，所有pg13中对应的表需要加上backend type这一列，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | CREATE FOREIGN TABLE pglog ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text, backend_type text ) SERVER pglog OPTIONS ( program 'find $PGDATA/log -type f -name "*.csv" -mtime -1 -exec cat {} \;', format 'csv' ); |
dblink
在PostgreSQL数据库之间进行跨库操作的方式
- dblink
- postgres_fdw
dblink是一个支持从数据库会话中连接到其他PostgreSQL数据库的插件。在其他数据库跨库操作也是采用dblink的方式。
PostgreSQL插件dblink是PostgreSQL安装包自带的；若安装数据库时没有安装dblink插件；可以自行去编译安装。用使用dblink；先要按照dblink扩展；安装完成之后；我们调用dblink函数来实现跨库操作。我们常用的操作是跨库查询。
PostgreSQL使用dblink；存在优势是；即取即用；无须在创建其他对象；
调用dblink_connect函数；创建dblink连接（连接名为hrxxt）；这样我们可以在这个会话中访问hrxxt数据库中下面的对象。
1 2 3 4 5 | hrxxt01=> select dblink_connect('hrxxt', 'host=192.168.1.221 port=6000 user=hrxxt password=li0924 dbname=hrxxt'); dblink_connect ---------------- OK (1 row) |
跨库查询表(dblink)
使用dblink；我们更多是跨库查询操作；由于上面创建dblink_connect连接；所以我们可以拿来使用。
1 2 3 4 5 | hrxxt01=> SELECT * FROM dblink('hrxxt', 'select id, info from public.t1') as hrxxt_t1(id int, info text); id | info ------+------- 1001 | hrxxt (1 row) |
我们也可以直接使用；无需先创建dblink_connect。这是常用的方式。
1 2 3 4 5 | hrxxt01=> SELECT * FROM dblink('host=192.168.1.221 port=6000 user=hrxxt password=li0924 dbname=hrxxt', 'select id, info from public.t1') as hrxxt_t1(id int, info text); id | info ------+------- 1001 | hrxxt (1 row) |
跨库执行ddl，dcl操作(dblink_exec)
若我们跨库操作create、insert、update、delete语句；其实这种行为一般不跨库操作。慎用！下面演示下insert操作
1 2 3 4 5 6 7 8 9 10 11 | hrxxt01=> SELECT dblink_exec('hrxxt', 'insert into public.t1 values (1002,''hello'')'); dblink_exec ------------- INSERT 0 1 (1 row) # OR hrxxt01=> SELECT dblink('hrxxt', format('insert into public.t1 select %L, %L', 1003, 'hrxxt')); dblink ---------------- ("INSERT 0 1") (1 row) |
关闭dblink连接
在PostgreSQL中dblink是会话级别；会话断开即dblink也关闭。当然也可以在会话中手动关闭
1 2 3 4 5 | hrxxt01=> SELECT dblink_disconnect('hrxxt'); dblink_disconnect ------------------- OK (1 row) |
扩展
使用dblink查询；都要带有conn_str；非常不简洁；在会话使用临时表/视图来保存。两种效果不同
到底选择视图/临时表；看你需求；在PostgreSQL中临时表在会话结束后是不会保持的。这样的好处；不使用的话无需去删除对应的临时表
1 2 3 4 5 6 7 8 | hrxxt01=> create temp table hrxxt_t1 as SELECT * FROM dblink('host=192.168.1.221 port=6000 user=hrxxt password=li0924 dbname=hrxxt', 'select id, info from public.t1') as hrxxt_t1(id int, info text); SELECT 2 hrxxt01=> select * from hrxxt_t1; id | info ------+------- 1001 | hrxxt 1002 | hello (2 rows) |
同步远端库表
在开发工作中；测试数据库需要同步某个（新建/旧的）表的数据；使用dblink非常便捷。
1 2 3 4 5 6 7 8 9 10 11 12 13 | hrxxt01=> create table public.t1(id int, info text); CREATE TABLE hrxxt01=> insert into public.t1 SELECT * FROM dblink('host=192.168.1.221 port=6000 user=hrxxt password=li0924 dbname=hrxxt', 'select id, info from public.t1') as hrxxt_t1(id int, info text); INSERT 0 5 hrxxt01=> select * from public.t1; id | info ------+------- 1001 | hrxxt 1002 | hello 1003 | rax 1004 | rax 1005 | hrxxt (5 rows) |
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- db2创建 drop table t1; create table t1(id int,name VARCHAR); insert into t1 VALUEs(1,'lhr'); -- db1操作 SELECT * FROM pg_available_extensions WHERE name = 'dblink'; CREATE EXTENSION dblink; SELECT dblink_connect('dblink_db2', 'dbname=db2 user=gpadmin'); SELECT dblink_connect('host=remotehost port=5432 dbname=postgres user=gpadmin password=secret'); -- 这里的id和name需要匹配起来 SELECT * FROM dblink('dblink_db2', 'SELECT id,name FROM test1') AS test1(id int8,name VARCHAR); -- 也可以直接查询而不需要提前创建 SELECT * FROM dblink('dbname=db2 user=gpadmin', 'SELECT id,name FROM t1') as test1(id int,name VARCHAR); -- 查询 SELECT dblink_get_connections(); |
postgres_fdw
postgres_fdw模块提供了外部数据包装器postgres_fdw，它可以被用来访问存储在外部PostgreSQL服务器中的数据。
使用postgres_fdw的操作步骤如下：
1）使用CREATE EXTENSION来安装postgres_fdw扩展。
2）使用CREATE SERVER创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库。指定除了user和password之外的连接信息作为该服务器对象的选项。
3）使用CREATE USER MAPPING创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项。
4）为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表。外部表的列必须匹配被引用的远程表。但是，如果你在外部表对象的选项中指定了正确的远程名称，你可以使用不同于远程表的表名和/或列名。
现在你只需要从一个外部表SELECT来访问存储在它的底层的远程表中的数据。也可以使用INSERT、UPDATE或DELETE修改远程表。
如果可以的话，通常推荐一个外部表的列被声明为与被引用的远程表列完全相同的数据类型和排序规则。尽管postgres_fdw目前已经能够容忍在需要时执行数据类型转换，但是当类型或排序规则不匹配时可能会发生奇怪的语义异常，因为远程服务器解释WHERE子句时可能会与本地服务器有所不同。
通过下列系统表可以查看数据库外部表信息。
| 系统表 | 简命令操作 | 含义 |
|---|---|---|
| pg_extension | \dx | 插件 |
| pg_foreign_data_wrapper | \dew | 支持外部数据库接口 |
| pg_foreign_server | \des | 外部服务器 |
| pg_user_mappings | \deu | 用户管理 |
| pg_foreign_table | \det | 外部表 |
举例说明postgres_fdw的使用方式：
首先安装该扩展：
1 | CREATE EXTENSION postgres_fdw; |
然后使用CREATE SERVER语句创建一个外部服务器。本例中为连接到主机192.168.0.100上并且监听5432端口的PostgreSQL服务器。在该远程服务器上要连接的数据库名为foreign_db：
1 2 3 | CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.0.100', port '5432', dbname 'foreign_db'); |
然后需要用CREATE USER MAPPING语句定义一个用户映射来标识在远程服务器上使用哪个角色：
1 2 3 | CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password'); |
现在可以使用CREATE FOREIGN TABLE语句创建外部表了。本例中希望访问远程服务器上名为some_schema.some_table的表。它的本地名称是foreign_table：
1 2 3 4 5 6 | CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text ) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table'); |
CREATE FOREIGN TABLE中声明的列数据类型和其他性质必须要匹配实际的远程表。列名也必须匹配，不过也可以为某一些列附上column_name选项以表示它们在远程服务器上对应的列。
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | create extension postgres_fdw; CREATE SERVER server_db2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432', dbname 'db2'); CREATE USER MAPPING FOR gpadmin SERVER server_db2 OPTIONS (user 'lhr', password 'lhr'); CREATE FOREIGN TABLE ft_db2_t1 ( id int, name VARCHAR ) SERVER server_db2 OPTIONS (schema_name 'public', table_name 't1'); SELECT * from ft_db2_t1; select * from pg_foreign_server ; SELECT * from pg_foreign_data_wrapper; SELECT * from pg_user_mappings; SELECT * from pg_foreign_table; -- 批量导入一个模式下的所有表到当前库的一个schem import foreign schema dw_core from server server_db2 into lhr; |
参考
http://postgres.cn/docs/13/file-fdw.html
http://postgres.cn/docs/13/contrib-dblink-function.html
http://postgres.cn/docs/13/postgres-fdw.html
https://blog.csdn.net/weixin_39540651/article/details/106553057
https://www.cnblogs.com/kuang17/p/10833458.html
