合 PG中的file_fdw、postgres_fdw和dblink
Tags： PGPostgreSQLDBLinkpostgres_fdw跨库访问file_fdw
简介
在PostgreSQL中，file_fdw、postgres_fdw 和 dblink 是三个常用的外部数据访问机制，它们允许数据库与外部数据源或其他数据库进行交互。
以下是对它们的简要介绍：
1. file_fdw（File Foreign Data Wrapper）
- 功能:
file_fdw是一个外部数据包装器（Foreign Data Wrapper），用于访问文件系统中的外部文件（如CSV文件）。通过file_fdw，可以将外部文件中的数据映射为PostgreSQL表，从而在SQL查询中直接访问这些数据。 - 典型用途: 主要用于将文件系统中的平面文件（通常是CSV或文本文件）作为PostgreSQL表进行访问和查询。这对于需要加载外部数据集或进行ETL（Extract, Transform, Load）操作非常有用。
- 限制: 只能访问本地文件系统中的文件，且文件内容需要是结构化的文本（如CSV格式）。
2. postgres_fdw（PostgreSQL Foreign Data Wrapper）
- 功能:
postgres_fdw也是一个外部数据包装器，专门用于访问其他PostgreSQL数据库。通过postgres_fdw，可以在当前PostgreSQL实例中创建外部表，映射到远程PostgreSQL数据库中的表，并在本地执行查询、插入、更新、删除等操作。 - 典型用途: 用于在多个PostgreSQL数据库实例之间进行数据共享或联邦查询。适用于需要从不同PostgreSQL数据库中聚合数据或执行跨数据库操作的场景。
- 优势: 支持分布式查询优化，可以通过在本地执行查询计划部分来减少远程数据传输。
3. dblink
- 功能:
dblink是一个扩展，允许在一个PostgreSQL数据库中通过SQL语句访问其他PostgreSQL数据库。与postgres_fdw不同，dblink提供了更灵活的方式来执行跨数据库的SQL命令，但它不像postgres_fdw那样将远程表映射为外部表。 - 典型用途: 常用于在不同PostgreSQL数据库之间执行即席查询或复杂的跨数据库操作。适合需要在单个会话中从远程数据库获取数据的情况。
- 限制:
dblink的操作往往是基于字符串拼接的，需要手动构造SQL语句，并且不如postgres_fdw那样直观地支持复杂查询的优化。
总结
file_fdw: 用于将文件系统中的文件作为表访问。postgres_fdw: 用于跨PostgreSQL实例访问远程表，支持高级查询优化。dblink: 用于手动执行跨PostgreSQL实例的SQL命令，适合即席查询和复杂操作。
这些工具各有其适用场景，可以根据实际需求选择最合适的工具进行数据访问和操作。
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' ); |
file_fdw的使用2
file_fdw插件为pg提供了访问外部文件的能力，目前只提供了只读能力。file_fdw已经在contrib中，可以直接CREATE EXTESION安装使用。外部文件必须是符合COPY规则的。来个经典的把pg的输出日志映射成外部表的例子，脚本来自官方文档 (注意官方文档和pgsql库的版本，pgsql的版本不同日志输出略有不同，所以建的外部表字段有一点不一样）1.创建file_fdw插件
1 | CREATE EXTENSION file_fdw; |
2.创建外部服务
1 | CREATE SERVER fileserver FOREIGN DATA WRAPPER file_fdw; |
3.创建外部表
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 | 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 ) SERVER fileserver OPTIONS ( filename 'pg_log/postgresql-07-06.csv', format'csv' ); |
4.查看日志表直接在库中查看映射的日志表，很方便查到报错信息
1 2 3 4 5 6 | =# select user_name,database_name,process_id,error_severity,message from pglog where error_severity<>'LOG'; user_name | database_name | process_id | error_severity | message -----------+---------------+------------+----------------+----------------------------------------------- appuser1 | db1 | 102349 | ERROR | value too long for type character varying(20) appuser1 | db1 | 55378 | ERROR | value too long for type character varying(20) appuser2 | db2 | 219377 | ERROR | relation "dual" does not exist |
dblink
在PostgreSQL数据库之间进行跨库操作的方式
- dblink
- postgres_fdw
dblink是一个支持从数据库会话中连接到其他PostgreSQL数据库的插件。在其他数据库跨库操作也是采用dblink的方式。
PostgreSQL插件dblink是PostgreSQL安装包自带的；若安装数据库时没有安装dblink插件；可以自行去编译安装。用使用dblink；先要按照dblink扩展；安装完成之后；我们调用dblink函数来实现跨库操作。我们常用的操作是跨库查询。
PostgreSQL使用dblink；存在优势是；即取即用；无须在创建其他对象；
