file_fdw 文件外部数据包装器

有些数据根本不在数据库里——它们在服务器的磁盘上，是一堆 CSV、TSV 或者制表符分隔的日志文件。想分析它们，传统做法是写脚本导入，或者用 awk/grep 硬扛。file_fdw 提供了第三种可能：把文件直接挂成一张只读的外部表，然后用标准 SQL 查询它。

file_fdw 的强大之处在于它复用了 COPY FROM 的完整解析机制。这意味着 COPY 支持的所有格式（text、csv）、所有细节控制（分隔符、引号、转义、NULL 表示、编码）在这里都能用。而且，PostgreSQL 服务器本身就在用这套机制做一件事——解析 CSV 格式的日志。官方文档甚至直接给出了一个完整示例：把 csvlog 日志文件挂成外部表来做日志分析。

本节讲清 file_fdw 的全部选项、权限要求，以及最经典的"SQL 分析数据库日志"实战。

前置知识

阅读本节前，建议先了解：postgres_fdw 外部数据包装器

基础概念

file_fdw 是什么

file_fdw 是一个只读的外部数据包装器，把 PostgreSQL 服务器上的文本文件映射为外部表。三个关键词需要强调：

只读：不能 INSERT/UPDATE/DELETE，也不能 COPY ... TO 到它。文件本身由外部程序（应用、日志系统）负责生成和维护。
文本文件：通过 COPY FROM 的解析器读取，支持 text 与 csv 两种格式，不支持二进制格式。
服务器上的文件：路径是服务器进程视角的路径，不是客户端视角。这一点是新手最大的认知陷阱。

与 COPY 的关系

file_fdw 内部就是调用 COPY FROM 的解析流程。理解这点后，选项体系就顺理成章了：

text

              COPY FROM 'file.csv' WITH (FORMAT csv, HEADER true, ...)
                    │
                    │  同一套解析器、同一套选项
                    ▼
              file_fdw 外部表
                    │
                    │  在 SQL 里像表一样查询
                    ▼
              SELECT ... FROM file_table WHERE ...

所以：COPY FROM 支持的表级选项，file_fdw 基本都支持。反过来说，COPY ... TO 方向的选项（比如 FORCE_QUOTE）在 file_fdw 里没有意义，因为它是只读的。

五层模型在 file_fdw 下的简化

FDW 通用模型是五层，但 file_fdw 用起来更简单：

层	对象	在 file_fdw 中的意义
1	`FOREIGN DATA WRAPPER`	固定就是 `file_fdw`，需 `CREATE EXTENSION`
2	`SERVER`	一个占位对象，通常不带任何选项
3	`USER MAPPING`	不需要，因为访问文件靠的是本地文件系统权限
4	`FOREIGN TABLE`	关键层：在这里指定 `filename` 或 `program` 及格式选项
5	查询	只读：仅 `SELECT`

注意第 3 层：file_fdw 没有凭据概念，所以不能（也无需）创建用户映射。CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw; 这样就够了，不需要 OPTIONS。

权限：谁能读文件

这是 file_fdw 最需要注意的部分。读取文件需要以下三者之一：

身份	说明
超级用户	无限制
`pg_read_server_files` 角色的成员	使用 `filename` 选项时
`pg_execute_server_program` 角色的成员	使用 `program` 选项时

普通用户即使被授予了外部表的 SELECT 权限，也无法访问服务器文件系统——这是刻意的安全设计，防止普通用户读取任意文件（例如 /etc/passwd）。

text

授权路径（二选一）
┌────────────────────────────────────────────────┐
│  GRANT pg_read_server_files TO analyst;        │  ← 读文件
│  GRANT pg_execute_server_program TO analyst;   │  ← 执行程序
└────────────────────────────────────────────────┘

注意：这两个角色权限很大——pg_read_server_files 能读取服务器上任意可读文件，pg_execute_server_program 能以 PostgreSQL 进程的权限执行任意命令。只授予确实需要的账号。

相对路径相对哪里

filename 若使用相对路径，是相对于数据目录（$PGDATA），不是相对于当前工作目录，也不是相对于 SQL 文件的位置。

text

/var/lib/postgresql/18/data/          ← 数据目录（PGDATA）
├── postgresql.conf
├── log/
│   └── postgresql.csv                 ← filename 'log/postgresql.csv' 有效
└── base/

实践建议：永远用绝对路径。相对路径的解析基准会随环境变化，是难以排查的隐患。

file_fdw 能做什么、不能做什么

能力	支持
`SELECT` 查询	支持
`WHERE` 过滤（本地执行）	支持
`JOIN` 文件表与普通表	支持
聚集、排序、窗口函数	支持（本地执行）
`INSERT` / `UPDATE` / `DELETE`	不支持（只读）
`COPY ... TO` 外部表	不支持
在外部表上建索引	不支持
直接查询 `.gz` 压缩文件	不支持（需借助 `program` 调用外部解压）
二进制 `COPY` 格式	不支持

注意"本地执行"这个标注：file_fdw 没有下推能力——它没有远端可以下推。整张文件都会被解析、过滤都发生在本地。这意味着对大文件做复杂查询时，每次查询都要重新读一遍整个文件。

语法与用法

完整创建流程

sql

-- 1. 安装扩展（通常是数据库级）
CREATE EXTENSION file_fdw;

-- 2. 创建 server 占位对象（不需要任何选项）
CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw;

-- 3. 直接创建外部表，所有关键配置都在这里
CREATE FOREIGN TABLE pg_csvlog (
    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,
    error_severity   text,
    sql_state_code   text,
    message          text
)
SERVER log_server
OPTIONS (
    filename '/var/lib/postgresql/18/data/log/postgresql.csv',
    format   'csv',
    header   'true'
);

注意：第 3 步上没有 USER MAPPING。这是 file_fdw 与 postgres_fdw 在流程上最直观的差异。

用 program 读取命令输出

除了 filename，file_fdw 还提供 program 选项，等价于 COPY FROM PROGRAM——执行一个 Shell 命令并解析其标准输出：

sql

CREATE FOREIGN TABLE compressed_log (
    log_time    timestamp(3) with time zone,
    message     text
)
SERVER log_server
OPTIONS (
    program 'gunzip -c /var/log/postgresql/postgresql.csv.gz',
    format  'csv'
);

有了 program，压缩文件、需要实时生成的输出、需要权限切换读取的文件，都可以用同一条通路解决：

sql

-- 读取需要管道处理的内容
CREATE FOREIGN TABLE filtered_errors (
    log_time timestamp(3) with time zone,
    message  text
)
SERVER log_server
OPTIONS (
    program 'grep ERROR /var/log/app/app.log',
    format  'text',
    delimiter E'\t'
);

注意：program 需要 pg_execute_server_program 角色权限，风险高于 filename。命令以 PostgreSQL 服务进程的身份执行（通常是 postgres 操作系统用户）。

修改选项

sql

-- 切换到另一个日志文件
ALTER FOREIGN TABLE pg_csvlog
    OPTIONS (SET filename '/var/lib/postgresql/18/data/log/postgresql-2026-09-01.csv');

-- 增加列级选项
ALTER FOREIGN TABLE pg_csvlog
    ALTER COLUMN user_name OPTIONS (ADD force_not_null 'true');

-- 移除选项
ALTER FOREIGN TABLE pg_csvlog OPTIONS (DROP header);

日志轮转（log rotation）后文件名会变，此时需要 ALTER FOREIGN TABLE ... OPTIONS (SET filename ...)。可以用 current_logfiles 文件（记录了当前活跃日志文件路径）来编写自动化脚本更新。

查询

sql

-- 按错误级别统计
SELECT error_severity, count(*)
FROM pg_csvlog
GROUP BY error_severity
ORDER BY count(*) DESC;

-- 查找慢查询记录
SELECT log_time, user_name, message
FROM pg_csvlog
WHERE message LIKE '%duration:%'
ORDER BY log_time DESC
LIMIT 20;

详细说明

表级选项：filename

指定要读取的文件路径。

sql

CREATE FOREIGN TABLE t (a text)
SERVER log_server
OPTIONS (filename '/var/log/app/data.csv', format 'csv');

绝对路径最稳妥；相对路径相对于 $PGDATA。
文件必须在服务器进程可读（注意是运行 PostgreSQL 的操作系统用户，通常是 postgres）。
文件内容在每次查询时重新读取，不缓存。
若文件不存在或不可读，查询时报 could not open file ... for reading: Permission denied 之类的错误。

表级选项：program

执行外部命令并读取其标准输出。

sql

CREATE FOREIGN TABLE t (a text)
SERVER log_server
OPTIONS (program '/bin/cat /var/log/app/data.csv', format 'csv');

需要 pg_execute_server_program 权限。因为存在命令注入与权限提升风险，不要把 program 的取值暴露给不受信任的用户。

表级选项：format

sql

OPTIONS (format 'csv')

可选值为 text、csv，默认 text。默认的分隔符也随格式而不同：

format	默认分隔符	默认 NULL 表示	引号处理
`text`	制表符 `\t`	`\N`	反斜杠转义
`csv`	逗号 `,`	空字符串（未加引号时）	双引号包围

这也是为什么 CSV 日志要明确写 format 'csv'——否则会按制表符解析，整行会挤进第一列。

sql

OPTIONS (header 'true')

仅对 format 'csv' 有效，表示跳过文件的第一行（通常作为标题行）：

sql

-- CSV 文件内容：
-- id,name,amount
-- 1,alice,100
-- 2,bob,200

CREATE FOREIGN TABLE t (id int, name text, amount numeric)
SERVER log_server
OPTIONS (filename '/tmp/data.csv', format 'csv', header 'true');
-- 查询只返回 2 行（alice / bob）

若 header 未设置或为 false，id,name,amount 这一行也会被当成数据，通常导致类型转换错误。

表级选项：delimiter

sql

OPTIONS (delimiter '|')

单字符分隔符。可以是 E'\t' 这种转义写法。常见用途：读取竖线分隔的导出文件。

表级选项：quote

sql

OPTIONS (format 'csv', quote '"')

CSV 格式下用于包围字段的字符，默认双引号。若数据源使用其他引号（较少见），可自定义。

表级选项：escape

sql

OPTIONS (format 'csv', escape '"')

指定与 quote 配合的转义字符。默认与 quote 相同（CSV 标准的"双写引号"转义）。

表级选项：null

sql

OPTIONS (format 'csv', null 'NULL')

指定什么字符串代表 NULL。

sql

-- text 格式默认以 \N 表示 NULL
-- csv 格式默认以未加引号的空字符串表示 NULL
-- 可以改成自定义字符串：
OPTIONS (format 'csv', null 'NULL')

注意：CSV 格式下，加引号的空串 "" 与不加引号的空串含义不同——前者是"空字符串"，后者才是 NULL。这个区别在数据清洗时经常是 bug 来源。

表级选项：default

sql

OPTIONS (format 'csv', default '0')

为文件中缺失的字段指定默认值。注意是"字段缺失"，不是"字段为空"。在解析那些末尾缺少列的行时有用。

表级选项：encoding

sql

OPTIONS (format 'csv', encoding 'GBK')

指定文件的字符编码。若文件编码与数据库编码不同，COPY 机制会做转换。常见场景：读取 Windows 导出的 GBK 编码 CSV。

sql

-- 数据库是 UTF8，文件是 GBK
CREATE FOREIGN TABLE legacy_export (id int, name text)
SERVER log_server
OPTIONS (
    filename '/data/legacy_export.csv',
    format   'csv',
    header   'true',
    encoding 'GBK'
);

表级选项：on_error

sql

OPTIONS (on_error 'ignore')

控制遇到错误行时的行为：

取值	行为
`stop`（默认）	遇到错误即中止查询并报错
`ignore`	跳过错误行，继续

这对脏数据文件非常实用。例如某行数字字段里混了一个 N/A：

sql

CREATE FOREIGN TABLE messy_data (id int, amount numeric)
SERVER log_server
OPTIONS (filename '/data/messy.csv', format 'csv', header 'true', on_error 'ignore');

SELECT count(*) FROM messy_data;  -- 跳过无法解析的行后返回计数

表级选项：reject_limit

sql

OPTIONS (on_error 'ignore', reject_limit '100')

配合 on_error 'ignore' 使用，限制最多跳过多少行。超过上限则整条语句失败。这是防止"静默跳过大量数据"的安全阀。

sql

-- 最多容忍 50 行坏数据，超过就报错
OPTIONS (format 'csv', on_error 'ignore', reject_limit '50')

表级选项：log_verbosity

sql

OPTIONS (format 'csv', on_error 'ignore', log_verbosity 'verbose')

控制被跳过的错误行记录到服务器日志的详细程度：

取值	说明
`silent`	不记录
`default`	记录基本信息
`verbose`	记录完整行内容与错误详情

排查"到底哪一行有问题"时，设为 verbose 会非常有用。

列级选项：force_not_null

sql

ALTER FOREIGN TABLE pg_csvlog
    ALTER COLUMN user_name OPTIONS (ADD force_not_null 'true');

声明"这一列即使是空串也不算 NULL"。在 CSV 格式下，不加引号的空字段默认被视为 NULL；force_not_null 可以改变这个行为，让空串保持为空字符串。

这与 COPY 的 FORCE_NOT_NULL 语义完全一致。

列级选项：force_null

sql

ALTER FOREIGN TABLE pg_csvlog
    ALTER COLUMN message OPTIONS (ADD force_null 'true');

与 force_not_null 相反：声明"这一列即使是加了引号的空串，也当成 NULL"。用于处理那些"把 NULL 写成 """的导出文件。

不支持 FORCE_QUOTE

sql

-- 错误！file_fdw 只读，FORCE_QUOTE 是输出方向（COPY TO）的选项，无意义
ALTER FOREIGN TABLE pg_csvlog OPTIONS (ADD force_quote 'true');

FORCE_QUOTE 控制输出时哪些列必须加引号，属于写入方向，file_fdw 不支持。

选项只能放在哪里

file_fdw 的选项位置限制比 postgres_fdw 更严格：

sql

-- 错误！file_fdw 的 FDW 级不能带选项
CREATE FOREIGN DATA WRAPPER file_fdw OPTIONS (...);   -- 不通过扩展安装时才会遇到

-- 错误！server 级不能带这些选项
ALTER SERVER log_server OPTIONS (ADD filename '/tmp/a.csv');

-- 错误！user mapping 不是 file_fdw 的用法
CREATE USER MAPPING FOR CURRENT_USER SERVER log_server OPTIONS (user 'x');

所有 filename/program/格式类选项只能建在外部表或列上。

EXPLAIN 输出中的文件信息

file_fdw 在 EXPLAIN 时会显示文件名（或程序名）以及文件字节大小：

sql

EXPLAIN SELECT * FROM pg_csvlog;

text

                         QUERY PLAN
-------------------------------------------------------------
 Foreign Scan on pg_csvlog  (cost=0.00..10.00 rows=1000 width=64)
   File: /var/lib/postgresql/18/data/log/postgresql.csv
   File Size: 24812340 bytes
(3 rows)

注意 File Size 这一行——它让你一眼看出"这次扫描要读 24MB"。如果加了 COSTS OFF，这些信息不会显示：

sql

EXPLAIN (COSTS OFF) SELECT * FROM pg_csvlog;   -- 不显示 File / File Size

实战示例

示例一：用 SQL 分析 PostgreSQL 的 CSV 日志

这是 file_fdw 最经典的用法，官方文档也以此为例。前置条件是服务器开启 CSV 日志：

ini

# postgresql.conf
logging_collector = on
log_destination = 'csvlog'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.csv'

创建完整的外部表（列定义严格对应 csvlog 格式的字段顺序）：

sql

CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE pg_csvlog (
    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 log_server
OPTIONS (
    filename '/var/lib/postgresql/18/data/log/postgresql.csv',
    format   'csv',
    header   'true'
);

然后就可以用纯 SQL 做以前需要日志平台才能做的分析：

sql

-- 1) 各类错误级别的数量分布
SELECT error_severity, count(*) AS n
FROM pg_csvlog
GROUP BY error_severity
ORDER BY n DESC;

text

 error_severity |   n
----------------+--------
 LOG            | 128432
 ERROR          |   1204
 WARNING        |    318
 FATAL          |      9
(4 rows)

sql

-- 2) 按 SQLSTATE 归类错误，定位高频问题
SELECT sql_state_code, count(*) AS n, min(message) AS sample
FROM pg_csvlog
WHERE error_severity = 'ERROR'
GROUP BY sql_state_code
ORDER BY n DESC
LIMIT 10;

text

 sql_state_code |  n   |                          sample
----------------+------+----------------------------------------------------------
 23505          |  512 | duplicate key value violates unique constraint "users_email_key"
 23503          |  187 | insert or update on table "orders" violates foreign key constraint
 40001          |   96 | could not serialize access due to concurrent update
 57014          |   43 | canceling statement due to statement timeout
 42P01          |   21 | relation "tmp_report" does not exist
(5 rows)

sql

-- 3) 找出连接数最多的来源 IP
SELECT split_part(connection_from, ':', 1) AS client_ip, count(*) AS conns
FROM pg_csvlog
WHERE connection_from IS NOT NULL
GROUP BY client_ip
ORDER BY conns DESC
LIMIT 5;

text

   client_ip    | conns
----------------+-------
 10.0.0.21      | 42311
 10.0.0.22      | 39204
 172.16.5.9     |  8123
(3 rows)

sql

-- 4) 统计报错最多的数据库
SELECT database_name, count(*) AS errors
FROM pg_csvlog
WHERE error_severity IN ('ERROR', 'FATAL', 'PANIC')
GROUP BY database_name
ORDER BY errors DESC;

text

 database_name | errors
---------------+--------
 app_prod      |    842
 report        |    331
 batch         |     40
(3 rows)

这种方式的价值在于：你可以把日志与业务表 JOIN 起来。例如"哪些租户的请求最容易触发唯一键冲突"——把 pg_csvlog 与租户表关联即可，这是纯文本日志工具做不到的。

示例二：读取应用导出的 CSV 做数据校验与装载

场景：运维每天从外部系统拿到一个 users_export.csv，需要先校验数据质量，再决定是否入库。

sql

CREATE SERVER staging_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE staging_users (
    id       bigint,
    email    text,
    nickname text,
    amount   numeric(12,2)
)
SERVER staging_server
OPTIONS (
    filename '/data/incoming/users_export.csv',
    format   'csv',
    header   'true',
    on_error 'ignore',
    reject_limit '100',
    log_verbosity 'verbose'
);

先做质量体检：

sql

-- 总行数
SELECT count(*) AS total FROM staging_users;

-- 邮箱格式明显异常的行
SELECT id, email
FROM staging_users
WHERE email !~ '^[^@]+@[^@]+\.[^@]+$'
LIMIT 20;

-- 金额为负或为空
SELECT count(*) FILTER (WHERE amount IS NULL) AS null_amount,
       count(*) FILTER (WHERE amount < 0)    AS negative_amount
FROM staging_users;

-- 与已有本地表比对，找出新增与冲突
SELECT u.email
FROM staging_users u
LEFT JOIN users_local l ON l.email = u.email
WHERE l.id IS NULL
LIMIT 50;

确认无误后再装载：

sql

INSERT INTO users_local (id, email, nickname, amount)
SELECT id, email, nickname, amount
FROM staging_users
WHERE email ~ '^[^@]+@[^@]+\.[^@]+$'
  AND amount >= 0
ON CONFLICT (email) DO NOTHING;

text

INSERT 0 1842

注意：file_fdw 本身是只读的，这里的 INSERT 目标是本地真实表，读的是外部表。这个组合模式（外部表做暂存与校验 + 本地表做落地）在生产中非常常见。

注意事项

file_fdw 是只读的

sql

INSERT INTO pg_csvlog (message) VALUES ('fake');  -- 错误！只读
UPDATE pg_csvlog SET message = 'x';               -- 错误！只读
DELETE FROM pg_csvlog;                            -- 错误！只读
COPY pg_csvlog TO '/tmp/out.csv';                 -- 错误！不支持

普通用户需要显式角色权限

sql

-- 只给表的 SELECT 是不够的
GRANT SELECT ON pg_csvlog TO analyst;
-- 还要：
GRANT pg_read_server_files TO analyst;

否则会报 permission denied for ... (must be superuser or have privileges of pg_read_server_files) 之类的错误。

相对路径基准是数据目录，不是当前目录

sql

-- 不推荐：路径含义随环境变化
OPTIONS (filename 'log/postgresql.csv');

-- 推荐：绝对路径
OPTIONS (filename '/var/lib/postgresql/18/data/log/postgresql.csv');

CSV 文件必须显式声明 format

sql

-- 错误！未声明 format，默认按 text 用制表符解析，整行会挤进第一列
CREATE FOREIGN TABLE bad (id int, name text)
SERVER log_server
OPTIONS (filename '/data/a.csv');

header 忘记设置会把标题行当数据

sql

-- 不推荐：缺少 header 'true'，标题行会导致类型转换错误
CREATE FOREIGN TABLE t (id int, name text)
SERVER log_server
OPTIONS (filename '/data/a.csv', format 'csv');
-- 报错：invalid input syntax for type integer: "id"

列定义必须与文件字段数一致

sql

-- 错误！文件有 4 列，外部表只定义 3 列
CREATE FOREIGN TABLE t (id int, name text, amount numeric)
SERVER log_server
OPTIONS (filename '/data/users.csv', format 'csv', header 'true');
-- 报错：extra data after last expected column

program 需要更高权限且风险更大

sql

-- 不推荐：把 program 暴露给不可信用户
GRANT pg_execute_server_program TO public;   -- 危险！

文件轮转后路径会失效

日志按天轮转后，原 filename 指向的文件可能已被移走或改名：

sql

-- 轮转后查询报错
SELECT count(*) FROM pg_csvlog;
-- ERROR: could not open file "...": No such file or directory

需要 ALTER FOREIGN TABLE ... OPTIONS (SET filename ...) 或用 glob 类方案（file_fdw 本身不支持通配符，需要外部工具配合）。

每次查询都重新读整个文件

sql

-- 不推荐：大文件上重复做这类查询
SELECT count(*) FROM huge_file_table;   -- 每次都完整读一遍

对大文件反复分析，考虑先导入成真实表。

不要在外部表上建索引

sql

CREATE INDEX ON pg_csvlog (log_time);  -- 错误！外部表不支持索引

没有下推，所有过滤都在本地

sql

-- file_fdw 没有远端可下推，WHERE 条件仍在本地逐行判断
SELECT * FROM pg_csvlog WHERE error_severity = 'FATAL';

这仍然要读整个文件。想加速，只能先落表再建索引。

文件被占用或写一半时会读到不完整数据

日志文件正在被写入时读取，可能拿到半行。建议对已完成轮转的文件做分析。

最佳实践

始终使用绝对路径

sql

OPTIONS (filename '/var/lib/postgresql/18/data/log/postgresql.csv')

CSV 一律显式写全格式三件套

sql

OPTIONS (filename '/data/a.csv', format 'csv', header 'true')

把外部表集中放在专用 schema

sql

CREATE SCHEMA IF NOT EXISTS file_staging;
CREATE FOREIGN TABLE file_staging.users_export (...) SERVER staging_server OPTIONS (...);

为文件分析创建专用角色，只授必需权限

sql

CREATE ROLE log_analyst LOGIN PASSWORD 'xxx';
GRANT pg_read_server_files TO log_analyst;
GRANT USAGE ON FOREIGN SERVER log_server TO log_analyst;
GRANT SELECT ON pg_csvlog TO log_analyst;

优先 filename 而非 program

program 权限更高、风险更大。只有确实需要解压、过滤、跨权限读取时才用它：

sql

-- 优先
OPTIONS (filename '/var/log/app/data.csv', format 'csv');
-- 不得已才用
OPTIONS (program 'gunzip -c /var/log/app/data.csv.gz', format 'csv');

脏数据文件打开容错选项并设置上限

sql

OPTIONS (
    format 'csv',
    header 'true',
    on_error 'ignore',
    reject_limit '100',
    log_verbosity 'verbose'
)

用 log_verbosity verbose 排查解析问题

sql

OPTIONS (format 'csv', on_error 'ignore', reject_limit '10', log_verbosity 'verbose')
-- 然后去服务器日志里查看被跳过的行

用 EXPLAIN 确认文件路径与大小符合预期

sql

EXPLAIN SELECT * FROM pg_csvlog;
-- 检查输出中的 File / File Size 是否为预期文件

对反复分析的日志，先落到真实表并建索引

sql

CREATE TABLE log_archive (LIKE pg_csvlog);
INSERT INTO log_archive SELECT * FROM pg_csvlog;
CREATE INDEX ON log_archive (log_time);
CREATE INDEX ON log_archive (error_severity);

用列级 force_not_null / force_null 精确控制 NULL 语义

sql

ALTER FOREIGN TABLE staging_users
    ALTER COLUMN nickname OPTIONS (ADD force_not_null 'true');

明确字符编码，避免中文乱码

sql

OPTIONS (filename '/data/gbk_export.csv', format 'csv', header 'true', encoding 'GBK')

为轮转日志写一个更新路径的例行任务

bash

# 每日轮转后，用 current_logfiles 中的路径更新外部表
psql -c "ALTER FOREIGN TABLE pg_csvlog OPTIONS (SET filename '/path/to/current.csv');"

给外部表加注释说明文件来源与更新频率

sql

COMMENT ON FOREIGN TABLE pg_csvlog IS
    '数据库 csvlog 日志；每日 00:00 轮转，由运维脚本更新 filename';

控制对 log_server 的授权范围

sql

-- 只授予真正需要的角色
GRANT USAGE ON FOREIGN SERVER log_server TO log_analyst;
-- 不要 GRANT ... TO PUBLIC

继续学习：其他第三方 FDW

file_fdw 文件外部数据包装器 ​

基础概念 ​

file_fdw 是什么 ​

与 COPY 的关系 ​

五层模型在 file_fdw 下的简化 ​

权限：谁能读文件 ​

相对路径相对哪里 ​

file_fdw 能做什么、不能做什么 ​

语法与用法 ​

完整创建流程 ​

用 program 读取命令输出 ​

修改选项 ​

查询 ​

详细说明 ​

表级选项：filename ​

表级选项：program ​

表级选项：format ​

表级选项：header ​

表级选项：delimiter ​

表级选项：quote ​

表级选项：escape ​

表级选项：null ​

表级选项：default ​

file_fdw 文件外部数据包装器

基础概念

file_fdw 是什么

与 COPY 的关系

五层模型在 file_fdw 下的简化

权限：谁能读文件

相对路径相对哪里

file_fdw 能做什么、不能做什么

语法与用法

完整创建流程

用 program 读取命令输出

修改选项

查询

详细说明

表级选项：filename

表级选项：program

表级选项：format

表级选项：header

表级选项：delimiter

表级选项：quote

表级选项：escape

表级选项：null

表级选项：default