PG中的file_fdw、postgres_fdw和dblink

0    148    2

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

简介

SQL/MED(SQL Management of External Data )

PostgreSQL可以使用SQL/MED特性功能通过SQL语句访问外部数据源的数据。实现类似Oracle dblink的功能。

数据流向图

PG中的file_fdw、postgres_fdw和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选项)。这和把该列放在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示例

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

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

通过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这一列,如下所示:

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的使用方式:

首先安装该扩展:

然后使用CREATE SERVER语句创建一个外部服务器。本例中为连接到主机192.168.0.100上并且监听5432端口的PostgreSQL服务器。在该远程服务器上要连接的数据库名为foreign_db:

然后需要用CREATE USER MAPPING语句定义一个用户映射来标识在远程服务器上使用哪个角色:

现在可以使用CREATE FOREIGN TABLE语句创建外部表了。本例中希望访问远程服务器上名为some_schema.some_table的表。它的本地名称是foreign_table:

CREATE FOREIGN TABLE中声明的列数据类型和其他性质必须要匹配实际的远程表。列名也必须匹配,不过也可以为某一些列附上column_name选项以表示它们在远程服务器上对应的列。

dblink

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

  • dblink
  • postgres_fdw

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

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

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

调用dblink_connect函数;创建dblink连接(连接名为hrxxt);这样我们可以在这个会话中访问hrxxt数据库中下面的对象。

跨库查询表(dblink)

使用dblink;我们更多是跨库查询操作;由于上面创建dblink_connect连接;所以我们可以拿来使用。

我们也可以直接使用;无需先创建dblink_connect。这是常用的方式。

跨库执行ddl,dcl操作(dblink_exec)

若我们跨库操作create、insert、update、delete语句;其实这种行为一般不跨库操作。慎用!下面演示下insert操作

关闭dblink连接

在PostgreSQL中dblink是会话级别;会话断开即dblink也关闭。当然也可以在会话中手动关闭

扩展

使用dblink查询;都要带有conn_str;非常不简洁;在会话使用临时表/视图来保存。两种效果不同

到底选择视图/临时表;看你需求;在PostgreSQL中临时表在会话结束后是不会保持的。这样的好处;不使用的话无需去删除对应的临时表

同步远端库表

在开发工作中;测试数据库需要同步某个(新建/旧的)表的数据;使用dblink非常便捷。

参考

https://pgfans.cn/a/1735

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

    头像

    小麦苗

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

    您可能还喜欢...

    发表评论

    您的电子邮箱地址不会被公开。

    4 × 5 =

     

    嘿,我是小麦,需要帮助随时找我哦
    • 18509239930
    • 个人微信

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部