Postgresql 中文操作指南

F.16. file_fdw — access data files in the server’s file system #

file_fdw 模块提供外部数据封装 file_fdw ,该封装可用于访问服务器文件系统中的数据文件,或在服务器上执行程序并读取其输出。数据文件或程序输出必须采用 COPY FROM 可读取的格式;有关详细信息,请参见 COPY 。目前,对数据文件的访问仅限于只读。

使用此包装器创建的外键表可以有以下选项:

  • filename

    • 指定要读取的文件。相对路径相对于数据目录。必须指定 filenameprogram,但不能同时指定。

  • program

    • 指定要执行的命令。此命令的标准输出将被读取,就像使用了 COPY FROM PROGRAM。必须指定 programfilename,但不能同时指定。

  • format

    • 指定数据格式,与 COPYFORMAT 选项相同。

  • header

    • 指定数据是否具有表头行,与 COPYHEADER 选项相同。

  • delimiter

    • 指定数据分隔符字符,与 COPYDELIMITER 选项相同。

  • quote

    • 指定数据引用字符,与 COPYQUOTE 选项相同。

  • escape

    • 指定数据转义字符,与 COPYESCAPE 选项相同。

  • null

    • 指定数据空字符串,与 COPYNULL 选项相同。

  • encoding

    • 指定数据编码,与 COPYENCODING 选项相同。

请注意,虽然 COPY 允许诸如 HEADER 之类的选项在没有相应值的情况下指定,但在所有情况下,外部表选项语法都要求存在一个值。要激活通常在没有值的情况下编写的 COPY 选项,可以传递值 TRUE,因为所有此类选项都是布尔值。

使用此包装器创建的外部表的一列可以具有以下选项:

  • force_not_null

    • 这是一个布尔选项。如果为 true,它指定不应根据空字符串(即,表级别 null 选项)匹配该列的值。这与在 COPYFORCE_NOT_NULL 选项中列出该列的效果相同。

  • force_null

    • 这是一个布尔选项。如果为 true,它指定即使该值使用引号,也会将与空字符串匹配的列值作为 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,否则还将显示文件大小(以字节为单位)。

Example F.1. Create a Foreign Table for PostgreSQL CSV Logs

file_fdw 的一个显而易见的用途是将 PostgreSQL 活动日志作为一张表提供给查询。要做到这一点,首先你必须是 logging to a CSV file,,在这里我们将它称为 pglog.csv。首先,将 file_fdw 作为扩展安装:

CREATE EXTENSION file_fdw;

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

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

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

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,
  leader_pid integer,
  query_id bigint
) SERVER pglog
OPTIONS ( filename 'log/pglog.csv', format 'csv' );

就是这样 — 现在你可以直接查询日志。当然,在生产中,你需要定义处理日志轮换的方法。