PG中的file_fdw、postgres_fdw和dblink

0    232    2

Tags:

👉 本文共约7159个字,系统预计阅读时间或需27分钟。

简介

在PostgreSQL中,file_fdwpostgres_fdwdblink 是三个常用的外部数据访问机制,它们允许数据库与外部数据源或其他数据库进行交互。

以下是对它们的简要介绍:

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选项)。这和把该列放在COPYFORCE_NOT_NULL选项中具有相同的效果。

  • force_null

    这是一个布尔选项。如果为真,它指定匹配空值字符串的列值会被返回为NULL, 即使该值被引号引用。如果没有这个选项,只有匹配空值字符串的未被引用的值会被返回为 NULL。这和在COPYFORCE_NULL 选项中列出该列有同样的效果。

COPYFORCE_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示例

 创建测试表,添加测试数据

通过copy拷贝成文件

创建SERVER(外部服务器)

注:以 postgres 用户连接虚拟机上的库 mydb, 并创建 SERVER;Server 可以理解为外部服务器,是本地库访问外部数据的桥梁。

创建外部表

备注:这里创建外部表,并指定外部 Server 为 server_file_fdw,在测试过程中,发现外部表有很多限制,目前发现了以下:

  • default 值不支持;
  • 索引不支持;

执行计划

可以看到,数据是从外部文件扫描获取,文件的位置,大小也有展示。

file_fdw查看日志

例 F.1. 为 PostgreSQL CSV 日志创建一个外部表

一种file_fdw的用法是把可用的 PostgreSQL 活动日志变成一个表用于查询。 要这样做,首先你必须写日志到一个CSV文件,,这里我们将它称为pglog.csv。 首先,将file_fdw安装为一个扩展:

然后创建一个外部服务器:

现在你已经准备好创建外部数据表。使用CREATE FOREIGN TABLE命令,你将需要为该表定义列、CSV 文件名以及格式:

就是这样了 — 现在你能够直接查询你的日志了。当然,在生产中你会需要定义一些方法来处理日志轮转。

PG13注意事项

因为pg13中在日志中新增加了记录backend type的数据,所有pg13中对应的表需要加上backend type这一列,如下所示:

file_fdw的使用2

file_fdw插件为pg提供了访问外部文件的能力,目前只提供了只读能力。file_fdw已经在contrib中,可以直接CREATE EXTESION安装使用。外部文件必须是符合COPY规则的。来个经典的把pg的输出日志映射成外部表的例子,脚本来自官方文档 (注意官方文档和pgsql库的版本,pgsql的版本不同日志输出略有不同,所以建的外部表字段有一点不一样)1.创建file_fdw插件

2.创建外部服务

3.创建外部表

4.查看日志表直接在库中查看映射的日志表,很方便查到报错信息

dblink

在PostgreSQL数据库之间进行跨库操作的方式

  • dblink
  • postgres_fdw

dblink是一个支持从数据库会话中连接到其他PostgreSQL数据库的插件。在其他数据库跨库操作也是采用dblink的方式。

PostgreSQL插件dblink是PostgreSQL安装包自带的;若安装数据库时没有安装dblink插件;可以自行去编译安装。用使用dblink;先要按照dblink扩展;安装完成之后;我们调用dblink函数来实现跨库操作。我们常用的操作是跨库查询。

PostgreSQL使用dblink;存在优势是;即取即用;无须在创建其他对象;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
验证码:
获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复