Postgresql 中文操作指南

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

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

The file_fdw module provides the foreign-data wrapper file_fdw, which can be used to access data files in the server’s file system, or to execute programs on the server and read their output. The data file or program output must be in a format that can be read by COPY FROM; see COPY for details. Access to data files is currently read-only.

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

A foreign table created using this wrapper can have the following options:

  • filename

    • Specifies the file to be read. Relative paths are relative to the data directory. Either filename or program must be specified, but not both.

  • program

    • Specifies the command to be executed. The standard output of this command will be read as though COPY FROM PROGRAM were used. Either program or filename must be specified, but not both.

  • format

    • Specifies the data format, the same as COPY's FORMAT option.

  • header

    • Specifies whether the data has a header line, the same as COPY's HEADER option.

  • delimiter

    • Specifies the data delimiter character, the same as COPY's DELIMITER option.

  • quote

    • Specifies the data quote character, the same as COPY's QUOTE option.

  • escape

    • Specifies the data escape character, the same as COPY's ESCAPE option.

  • null

    • Specifies the data null string, the same as COPY's NULL option.

  • encoding

    • Specifies the data encoding, the same as COPY's ENCODING option.

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

Note that while COPY allows options such as HEADER to be specified without a corresponding value, the foreign table option syntax requires a value to be present in all cases. To activate COPY options typically written without a value, you can pass the value TRUE, since all such options are Booleans.

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

A column of a foreign table created using this wrapper can have the following options:

  • force_not_null

    • This is a Boolean option. If true, it specifies that values of the column should not be matched against the null string (that is, the table-level null option). This has the same effect as listing the column in COPY's FORCE_NOT_NULL option.

  • force_null

    • This is a Boolean option. If true, it specifies that values of the column which match the null string are returned as NULL even if the value is quoted. Without this option, only unquoted values matching the null string are returned as NULL. This has the same effect as listing the column in COPY's FORCE_NULL option.

COPY 的 FORCE_QUOTE 选项目前不受 file_fdw 支持。

COPY's FORCE_QUOTE option is currently not supported by file_fdw.

这些选项只能为外部表或其列指定，不能在 file_fdw 外部数据包装器选项中指定，也不能在使用该包装器的服务器或用户映射选项中指定。

These options can only be specified for a foreign table or its columns, not in the options of the file_fdw foreign-data wrapper, nor in the options of a server or user mapping using the wrapper.

由于安全原因，更改表级别选项需要成为超级用户或拥有角色 pg_read_server_files（使用文件名）或角色 pg_execute_server_program（使用程序）的特权：只有某些用户才能控制读取哪个文件或运行哪个程序。原则上，可以允许普通用户更改其他选项，但目前不支持该功能。

Changing table-level options requires being a superuser or having the privileges of the role pg_read_server_files (to use a filename) or the role pg_execute_server_program (to use a program), for security reasons: only certain users should be able to control which file is read or which program is run. In principle regular users could be allowed to change the other options, but that’s not supported at present.

指定 program 选项时，请记住该选项字符串由 shell 执行。如果需要将任何来自不可信源的命令参数传递，你必须仔细剥离或转义任何对 shell 来说可能有特殊含义的字符。出于安全原因，最好使用固定的命令字符串，或者至少避免在其中传递任何用户输入。

When specifying the program option, keep in mind that the option string is executed by the shell. If you need to pass any arguments to the command that come from an untrusted source, you must be careful to strip or escape any characters that might have special meaning to the shell. For security reasons, it is best to use a fixed command string, or at least avoid passing any user input in it.

对于使用 file_fdw 的外部表，EXPLAIN 显示要读取的文件或要运行的程序的名称。对于一个文件，除非指定了 COSTS OFF，否则还将显示文件大小（以字节为单位）。

For a foreign table using file_fdw, EXPLAIN shows the name of the file to be read or program to be run. For a file, unless COSTS OFF is specified, the file size (in bytes) is shown as well.

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

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

One of the obvious uses for file_fdw is to make the PostgreSQL activity log available as a table for querying. To do this, first you must be logging to a CSV file, which here we will call pglog.csv. First, install file_fdw as an extension:

CREATE EXTENSION file_fdw;

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

Then create a foreign server:

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

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

Now you are ready to create the foreign data table. Using the CREATE FOREIGN TABLE command, you will need to define the columns for the table, the CSV file name, and its format:

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' );

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

That’s it — now you can query your log directly. In production, of course, you would need to define some way to deal with log rotation.