PostgreSQL / 系统课时

导出与传输归档数据

导出与传输归档数据

历史数据归档不能只解决“怎么导出”,还要保证导出范围固定、传输文件可校验、目标端能够正确导入。本课比较 COPY、pg_dump 与程序化批量传输,并完成一条可验证的导出、传输、导入链路。

学习目标

  • 根据归档目标选择 COPY、pg_dump 或程序化批量传输
  • 用冻结的时间边界导出历史数据,并校验传输前后的文件完整性
  • 将归档数据导入目标表,并用行数与文件校验结果完成验收

先确定导出边界与工具

归档通常不是整库迁移,而是把满足固定时间边界的历史记录转移到低成本数据库或独立归档库。工具选择主要取决于是否需要按条件筛选行、是否要携带对象定义,以及目标端准备怎样恢复。

方式 适合场景 主要特点
COPY / \copy 按查询条件导出大量行 速度快,可精确筛选数据,但不自动保存表结构
pg_dump 导出完整表、分区或数据库对象 可保存对象定义并配合 pg_restore 恢复,但不适合复杂的逐行业务筛选
程序化批量传输 需要转换、压缩或跨系统写入 可定制重试、格式转换和幂等逻辑,但实现与维护成本更高

如果归档对象已经按时间分区,并准备转移完整旧分区,pg_dump 很合适;如果只归档表中某个时间范围,通常优先使用查询配合 \copy。

用 COPY 导出固定范围

假设业务表为 orders,归档目标是导出 2025 年以前的数据。客户端执行 \copy 时,文件写在运行 psql 的机器上,因此无需数据库服务器拥有目标目录的写权限。

sql
\copy (
    SELECT id, customer_id, status, amount, created_at
    FROM orders
    WHERE created_at < TIMESTAMPTZ '2025-01-01 00:00:00+00'
    ORDER BY id
) TO '<PATH>/orders_before_2025.csv' WITH (FORMAT csv, HEADER true)

预期结果

text
COPY <VALUE>

<VALUE> 是实际导出的行数,<PATH> 是客户端可写目录。应记录这次 COPY 返回的行数,后续与目标端导入行数核对。若数据量很大,还可以把导出流交给压缩工具以降低磁盘和网络开销,但要提前评估额外 CPU 消耗。

为传输文件建立校验值

文件生成后,不应只根据文件大小判断传输是否成功。至少记录字节数和 SHA-256,使传输前后的文件能够确定性比对。

bash
wc -c <PATH>/orders_before_2025.csv
sha256sum <PATH>/orders_before_2025.csv

预期输出

text
<VALUE> <PATH>/orders_before_2025.csv
<CHECKSUM>  <PATH>/orders_before_2025.csv

<VALUE> 与 <CHECKSUM> 会随数据变化。文件传输到归档环境后,对接收文件再次执行相同命令;只有字节数和 SHA-256 都一致,才能确认文件没有被截断或修改。

历史数据从源表导出到归档库的带校验传输流程

导入归档数据库

目标端应提前准备字段类型兼容的归档表。使用 CSV 时,可以通过 \copy 从客户端文件直接导入。第一次实施时,建议先导入暂存表,完成数量和内容验证后再合并到正式归档表,避免错误批次污染已有数据。

sql
\copy archive_orders (
    id,
    customer_id,
    status,
    amount,
    created_at
) FROM '<PATH>/orders_before_2025.csv' WITH (FORMAT csv, HEADER true)

预期结果

text
COPY <VALUE>

这里的 <VALUE> 应与导出阶段记录的行数一致。若不同,应停止后续源端清理,并检查接收文件、目标表约束、字段格式以及是否发生重复导入。对于已有数据的归档表,还应按归档批次或冻结边界再做一次目标端计数,避免只看本次命令返回值。

什么时候改用 pg_dump

当归档单位是完整表或完整旧分区,并希望连同表定义、索引和约束一起保存时,pg_dump 更合适。下面使用自定义格式导出一个冻结的历史分区,再用 pg_restore --list 检查归档文件是否可解析。

bash
pg_dump --format=custom --table=orders_2024 --file=<PATH>/orders_2024.dump <DATABASE>
pg_restore --list <PATH>/orders_2024.dump

预期输出

text
;
; Archive created at <TIME>
;
<VALUE>; <ID> <ID> TABLE public orders_2024 <VALUE>
<VALUE>; <ID> <ID> TABLE DATA public orders_2024 <VALUE>

pg_restore --list 的归档时间、目录编号、对象 ID 和所有者会因环境而异,因此用 <TIME>、<ID>、<VALUE> 表示动态内容。能正常列出归档目录只说明 dump 文件可解析,不能替代行数、业务关键字段或哈希等数据级完整性验证。

归档传输完成的判定条件应同时包括:冻结边界明确、源端导出行数已记录、文件传输前后校验一致、目标端导入行数一致。只有这些证据齐全后,才应进入归档完整性验证和源端历史数据清理阶段。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

导出与传输归档数据

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通