PostgreSQL笔记17：逻辑备份工具链与性能优化实践

纲要

• pg_dump — 单数据库逻辑备份工具
  • 输出格式：plain（-Fp）、custom（-Fc）、directory（-Fd）、tar（-Ft）
  • 核心选项：-a（--data-only）、-s（--schema-only）、-t（--table）、-T（--exclude-table）、-n（--schema）、-j（--jobs）、--lock-wait-timeout、--snapshot
  • 管道传输与跨版本迁移
• pg_dumpall — 实例级逻辑备份工具
  • 全局对象备份：roles、tablespaces、配置参数权限
  • -g（--globals-only）选项
• pg_restore — 归档格式恢复工具
  • 并行恢复（-j）
  • 选择性恢复（-t、-n、-P）
• COPY与\copy — 高性能数据导入导出
  • 服务端COPY与客户端\copy的区别
  • PostgreSQL 17 ON_ERROR选项（stop / ignore）
• 并行备份与加速策略
  • directory格式（-Fd）是唯一支持并行转储的格式
  • 单表大表的加速方案：pg_export_snapshot() + 多事务并行COPY
• 第三方工具生态
  • pgcopydb — 全量迁移与克隆
  • pg_dumpbinary / pg_restorebinary — 二进制格式转储

pg_dump：单数据库逻辑备份

pg_dump是PostgreSQL官方自带的单数据库逻辑备份工具，即使在数据库并发读写期间也能创建一致性备份，且不会阻塞其他用户的访问。pg_dump仅转储单个数据库；如需备份整个集群或集群范围内的全局对象（如角色和表空间），应使用pg_dumpall。

输出格式

pg_dump支持四种输出格式，通过-F或--format选项指定：

格式	选项	特点	适用场景
plain（纯文本）	-Fp	生成SQL脚本，可用psql执行，可手动编辑	跨版本迁移、简单备份
custom（自定义）	-Fc	压缩存储，支持pg_restore选择性恢复和并行恢复	生产环境推荐
directory（目录）	-Fd	每个表生成独立文件，唯一支持并行转储的格式	大数据库并行备份
tar	-Ft	tar归档格式，支持pg_restore	兼容性需求

custom和directory格式是最灵活的两种输出格式，支持对归档项目的选择和重新排序，支持并行恢复，且默认启用压缩。

常用选项

以下为pg_dump的核心选项：

选项	简写	说明
--data-only	-a	仅转储数据，不转储结构定义
--schema-only	-s	仅转储结构定义，不转储数据
--table=TABLE	-t	仅转储指定表（可多次指定）
--exclude-table=TABLE	-T	排除指定表
--schema=SCHEMA	-n	仅转储指定模式
--jobs=N	-j	并行转储的工作进程数（仅directory格式有效）
--lock-wait-timeout=TIME	—	获取表锁的超时时间
--snapshot=SNAPSHOT	—	使用指定的同步快照
--file=FILENAME	-f	指定输出文件
--clean	-c	在创建对象前先DROP（仅纯文本格式有效）

基础示例

创建测试表并插入数据：

CREATE TABLE t1 (
    id INT,
    info TEXT
);

INSERT INTO t1 SELECT generate_series(1, 10), 'data_' || generate_series(1, 10);

仅导出数据（-a） ：

pg_dump -a -t t1 -f t1_data_only.sql postgres

仅导出结构（-s） ：

pg_dump -s -t t1 -f t1_schema_only.sql postgres

导出为纯文本格式：

pg_dump -Fp -t t1 -f t1_dump.sql postgres

查看导出的SQL脚本内容：

head -20 t1_dump.sql

输出示例：

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';

CREATE TABLE public.t1 (
    id integer,
    info text
);

COPY public.t1 (id, info) FROM stdin;
1	data_1
2	data_2
...

管道传输：无落地备份与恢复

pg_dump默认输出到标准输出，可借助Linux管道特性实现不落地的数据库迁移：

# 创建目标数据库
createdb mydb

# 通过管道直接将备份数据灌入目标库
pg_dump -Fp postgres | psql -d mydb

此种方式适用于小数据量的快速迁移场景。

pg_dumpall：实例级逻辑备份

pg_dumpall用于将整个PostgreSQL集群的所有数据库转储到一个脚本文件中。其实现方式是依次为集群中的每个数据库调用pg_dump。

与pg_dump的核心区别在于：pg_dumpall还会转储所有数据库通用的全局对象（global objects），包括数据库角色（roles）、表空间（tablespaces）以及配置参数的权限授予（privilege grants for configuration parameters）。pg_dump不保存这些对象。

全局对象备份

使用-g或--globals-only选项仅转储全局对象（角色和表空间），不转储任何数据库：

pg_dumpall -g > globals.sql

查看输出内容：

cat globals.sql

输出示例：

--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

--
-- Roles
--

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

CREATE ROLE app_user;
ALTER ROLE app_user WITH NOLOGIN;

注意事项

• pg_dumpall需要多次连接PostgreSQL服务器（每个数据库一次），使用密码认证时每次都会提示输入密码，建议配置~/.pgpass文件。
• 执行完整的pg_dumpall备份通常需要数据库超级用户权限。

pg_restore：归档格式恢复工具

pg_restore用于从pg_dump以非纯文本格式（custom、directory、tar）创建的归档文件中恢复PostgreSQL数据库。它支持两种运行模式：

1. 直接恢复模式：指定目标数据库名称，直接将归档内容恢复到该数据库
2. 脚本生成模式：不指定目标数据库，生成包含重建数据库所需SQL命令的脚本文件

并行恢复

pg_restore支持通过-j或--jobs选项并发执行恢复过程中最耗时的步骤——即加载数据、创建索引和创建约束：

# 并行恢复，使用4个并发工作进程
pg_restore -d target_db -j 4 -Fd ./backup_dir

选择性恢复

pg_restore支持仅恢复指定的表、模式或函数：

# 仅恢复特定表
pg_restore -d target_db -t t1 -Fc backup.dump

# 仅恢复特定模式
pg_restore -d target_db -n public -Fc backup.dump

COPY与\copy：高性能数据导入导出

COPY是PostgreSQL中在表和文件之间批量移动数据的高性能命令。

COPY vs \copy

特性	COPY（服务端）	\copy（客户端，psql元命令）
执行位置	PostgreSQL服务器	psql客户端
文件路径	服务器文件系统路径	客户端文件系统路径
权限要求	PostgreSQL服务进程用户需有文件读写权限	客户端用户需有文件读写权限
适用场景	服务端本地文件操作	远程客户端文件操作

基础用法

导出数据：

-- 导出整个表
COPY t1 TO '/tmp/t1.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

-- 导出查询结果
COPY (SELECT * FROM t1 WHERE id < 5) TO '/tmp/t1_filtered.csv' WITH (FORMAT CSV, HEADER true);

# 查看导出结果
cat /tmp/t1.csv

导入数据：

-- 从CSV文件导入
COPY t1 FROM '/tmp/t1.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

psql中使用\copy ：

-- 导出到客户端本地文件
\copy t1 TO 't1.csv' WITH (FORMAT CSV, HEADER true);

-- 从客户端本地文件导入
\copy t1 FROM 't1.csv' WITH (FORMAT CSV, HEADER true);

性能优势

COPY命令针对批量数据加载进行了高度优化。根据PostgreSQL官方文档，使用COPY加载大量行几乎总是比使用INSERT快，即使使用了PREPARE并将多个插入分批到单个事务中。COPY在与之前的CREATE TABLE或TRUNCATE命令在同一事务中使用时速度最快。

性能提升主要源于：

• 减少网络往返延迟
• 避免重复的SQL解析开销
• 减少事务和上下文切换开销
• 内部针对批量操作的缓冲区优化

PostgreSQL 17 增强：ON_ERROR 选项

在PostgreSQL 17之前，COPY FROM在导入过程中如果遇到任何数据转换错误（如类型不兼容、异常字符等），整个事务会回滚，已导入的数据全部不可见，变为死元组需要VACUUM清理。这对于大规模数据导入场景极为不利。

PostgreSQL 17引入了ON_ERROR选项，允许指定错误处理策略：

• ON_ERROR stop（默认）：遇到错误时失败并回滚
• ON_ERROR ignore：跳过错误行，继续处理后续数据

-- PostgreSQL 17+：忽略类型转换错误，继续导入
COPY t1 FROM '/data/import.csv' 
WITH (FORMAT CSV, HEADER true, ON_ERROR ignore);

当设置为ignore时，COPY会跳过无法转换的行并在NOTICE消息中报告被跳过的行数。此选项仅适用于COPY FROM且FORMAT为text或csv时。

并行备份与性能优化

pg_dump 并行转储

pg_dump的-j或--jobs选项支持并行转储，但仅适用于directory格式（-Fd） 。这是因为directory格式是唯一允许多个进程同时写入数据的输出格式。

# 使用4个并行工作进程进行备份
pg_dump -Fd -j 4 -f ./my_backup postgres

并行转储的本质是表级并行：每个工作进程负责转储一个或多个表。如果数据库中只有一个大表（如200GB），即使设置-j 10，该表的转储仍然由单个进程完成，无法实现表内并行。

单表大表的加速方案：pg_export_snapshot

针对单个大表的加速备份，可利用PostgreSQL的快照导出功能。

核心原理：

1. 在REPEATABLE READ隔离级别下开启一个事务
2. 使用pg_export_snapshot()导出当前事务的快照ID
3. 多个并发事务使用相同的快照ID，确保看到一致的数据视图
4. 每个事务使用COPY导出表的不同数据分片

-- 会话1：导出快照
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
-- 返回快照ID，如 '00000017-0000002F-1'

-- 会话2、3、...：使用相同快照，并行导出不同分片
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000017-0000002F-1';
COPY (SELECT * FROM large_table WHERE id BETWEEN 1 AND 1000000) TO '/data/part1.csv' WITH (FORMAT CSV);
COMMIT;

pg_dump本身也支持--snapshot选项，可指定使用已导出的快照。结合--exclude-table排除已通过COPY并行导出的表，可实现混合加速策略。

pgcopydb：全量迁移工具

pgcopydb是一个将整个PostgreSQL数据库从源实例复制到目标实例的工具。它本质上利用了pg_dump的能力，同时实现了：

• 以目录格式并行转储
• 并行恢复
• 克隆（clone）和分叉（fork）功能
• 变更回放（change replay）

# 全量克隆数据库
pgcopydb clone --source "postgresql://user@source:5432/db" \
               --target "postgresql://user@target:5432/db"

pg_dumpbinary：二进制格式转储

pg_dump在处理某些特殊数据类型时存在限制：

• bytea类型数据通过escape/hex编码后的总大小超过1GB时无法导出
• 某些自定义类型或包含\0字节的数据可能被截断

pg_dumpbinary将PostgreSQL数据库以二进制格式转储，需使用配套的pg_restorebinary恢复。对于常规场景，仍应优先使用官方自带的pg_dump/pg_restore。

API 速览

pg_dump 命令行接口

方法签名：

pg_dump [connection-option...] [option...] [dbname]

核心参数：

参数	类型	说明
-f, --file=FILENAME	string	输出文件路径
`-F, --format=p	c	d
-j, --jobs=N	integer	并行工作进程数（仅directory格式）
-a, --data-only	flag	仅转储数据
-s, --schema-only	flag	仅转储结构
-t, --table=TABLE	string	指定要转储的表（可重复）
-T, --exclude-table=TABLE	string	排除指定表（可重复）
-n, --schema=SCHEMA	string	指定要转储的模式（可重复）
-c, --clean	flag	恢复前先DROP对象（仅纯文本格式）
--lock-wait-timeout=TIME	string	获取锁的超时时间
--snapshot=SNAPSHOT	string	使用指定的同步快照
-v, --verbose	flag	输出详细信息

返回值：命令成功时返回0，失败时返回非0值。

代码示例：

# 使用custom格式备份，启用压缩
pg_dump -Fc -f mydb.dump mydb

# 使用directory格式并行备份（4个进程）
pg_dump -Fd -j 4 -f ./backup_dir mydb

# 仅备份指定表的结构
pg_dump -s -t orders -t customers -f schema.sql mydb

# 带超时选项的备份
pg_dump --lock-wait-timeout=10s -f mydb.sql mydb

pg_dumpall 命令行接口

方法签名：

pg_dumpall [connection-option...] [option...]

核心参数：

参数	类型	说明
-f, --file=FILENAME	string	输出文件路径
-g, --globals-only	flag	仅转储全局对象（角色和表空间）
-a, --data-only	flag	仅转储数据
-c, --clean	flag	恢复前先DROP所有对象
-O, --no-owner	flag	不输出对象所有者设置
-r, --roles-only	flag	仅转储角色

代码示例：

# 完整备份整个集群
pg_dumpall -f cluster_full.sql

# 仅备份全局对象
pg_dumpall -g -f globals_only.sql

# 备份时不包含所有者信息（便于非超级用户恢复）
pg_dumpall -O -f cluster_no_owner.sql

pg_restore 命令行接口

方法签名：

pg_restore [connection-option...] [option...] [filename]

核心参数：

参数	类型	说明
-d, --dbname=DBNAME	string	目标数据库名称
-f, --file=FILENAME	string	输出脚本文件
-j, --jobs=N	integer	并行恢复的工作进程数
-a, --data-only	flag	仅恢复数据
-c, --clean	flag	恢复前先DROP对象
-C, --create	flag	先创建目标数据库
-t, --table=TABLE	string	仅恢复指定表
-n, --schema=SCHEMA	string	仅恢复指定模式
-P, --function=NAME	string	仅恢复指定函数
-l, --list	flag	列出归档内容（TOC）

代码示例：

# 直接恢复到数据库
pg_restore -d target_db -Fc mydb.dump

# 并行恢复（4个进程）
pg_restore -d target_db -j 4 -Fd ./backup_dir

# 先清理再恢复
pg_restore -d target_db -c -Fc mydb.dump

# 仅列出归档内容（TOC）
pg_restore -l -Fd ./backup_dir

COPY SQL 命令

方法签名：

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]

COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERE condition ]

核心选项：

选项	类型	说明
FORMAT	text/csv/binary	数据格式
DELIMITER	character	字段分隔符
NULL	string	NULL值的表示
HEADER	boolean	CSV是否包含表头
QUOTE	character	CSV引用字符
ESCAPE	character	CSV转义字符
ENCODING	string	文件编码
ON_ERROR	stop/ignore	错误处理策略（PostgreSQL 17+）

代码示例：

-- 导出为CSV（含表头）
COPY t1 TO '/tmp/t1.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

-- 导出查询结果
COPY (SELECT id, info FROM t1 WHERE id <= 5) TO '/tmp/t1_filtered.csv' WITH (FORMAT CSV);

-- 从CSV导入（PostgreSQL 17+ 支持ON_ERROR）
COPY t1 FROM '/tmp/t1.csv' WITH (FORMAT CSV, HEADER true, ON_ERROR ignore);

-- 使用psql \copy（客户端路径）
\copy t1 TO 't1.csv' WITH (FORMAT CSV, HEADER true);
\copy t1 FROM 't1.csv' WITH (FORMAT CSV, HEADER true);

Demo 完整示例

本Demo演示如何使用pg_dump和pg_restore完成一个完整的数据库备份与恢复流程，涵盖custom格式备份、并行恢复以及COPY数据导出。

环境准备

# 创建测试数据库
createdb demo_db

# 连接到数据库并创建测试表
psql -d demo_db <<EOF
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    amount NUMERIC(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (customer_name, amount)
SELECT 'Customer_' || generate_series(1, 10000),
       round((random() * 1000)::numeric, 2)
FROM generate_series(1, 10000);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_name TEXT,
    quantity INT,
    price NUMERIC(10,2)
);

INSERT INTO order_items (order_id, product_name, quantity, price)
SELECT (random() * 10000)::INT + 1,
       'Product_' || generate_series(1, 50000),
       (random() * 10)::INT + 1,
       round((random() * 100)::numeric, 2)
FROM generate_series(1, 50000);

-- 确认数据量
SELECT 'orders' AS table_name, COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;
EOF

备份

# 使用custom格式备份（默认压缩，支持并行恢复）
pg_dump -Fc -f demo_db.dump demo_db

# 查看备份文件大小
ls -lh demo_db.dump

# 使用directory格式并行备份（4个进程）
pg_dump -Fd -j 4 -f ./demo_backup_dir demo_db

# 查看目录结构
tree ./demo_backup_dir

./demo_backup_dir
├── 44814.dat.gz
├── 44815.dat.gz
├── 44816.dat.gz
├── 44817.dat.gz
├── 44818.dat.gz
├── toc.dat
└── ...

恢复

# 创建目标数据库
createdb demo_db_restored

# 从custom格式恢复
pg_restore -d demo_db_restored -Fc demo_db.dump

# 验证恢复结果
psql -d demo_db_restored -c "SELECT 'orders' AS table_name, COUNT(*) FROM orders UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;"

# 并行恢复（从directory格式，4个进程）
pg_restore -d demo_db_restored -j 4 -Fd ./demo_backup_dir

使用COPY导出数据

# 导出orders表为CSV
psql -d demo_db -c "\copy orders TO 'orders.csv' WITH (FORMAT CSV, HEADER true)"

# 导出order_items表（仅部分列）
psql -d demo_db -c "\copy (SELECT id, order_id, product_name, quantity FROM order_items) TO 'order_items_subset.csv' WITH (FORMAT CSV, HEADER true)"

# 查看导出文件行数
wc -l orders.csv order_items_subset.csv

运行说明

1. 环境要求：PostgreSQL 14+（ON_ERROR特性需要PostgreSQL 17+）
2. 执行顺序：环境准备 → 备份 → 恢复 → COPY导出
3. 权限要求：执行pg_dump和pg_restore需要数据库连接权限；COPY到文件需要PostgreSQL服务进程对目标目录有写入权限

技术点总结

技术点	说明
pg_dump -Fc	custom格式，压缩存储，支持pg_restore选择性恢复和并行恢复
pg_dump -Fd -j	directory格式 + 并行转储，适合大数据库
pg_restore -j	并行恢复，充分利用多核CPU
pg_restore -l	列出归档TOC，可用于选择性恢复前的预览
\copy	psql客户端命令，数据导出到客户端本地文件
COPY WITH (FORMAT CSV, HEADER true)	CSV格式导出，含表头，便于与其他工具交互

项目难点与解决方案

核心难点

1. 单表大表的并行备份：pg_dump的并行能力仅限于表级，单表无法通过-j实现并行加速
2. 导入过程中的数据质量问题：COPY FROM在PostgreSQL 17之前遇到任何错误即回滚全部事务，大规模导入时风险极高
3. 特殊数据类型的导出限制：pg_dump无法处理超过1GB的bytea类型数据以及包含\0字节的自定义类型
4. 一致性快照的跨进程共享：多进程并行备份时需要确保所有进程看到同一时间点的数据

解决方案

难点	解决方案	适用版本
单表大表备份慢	使用pg_export_snapshot()导出快照，多事务并行COPY导出不同数据分片	PostgreSQL 9.2+
COPY导入错误回滚	PostgreSQL 17+ 使用ON_ERROR ignore跳过错误行	PostgreSQL 17+
特殊数据类型导出	使用pg_dumpbinary/pg_restorebinary进行二进制格式转储	第三方工具
多进程一致性	pg_dump --snapshot或手动SET TRANSACTION SNAPSHOT	PostgreSQL 9.2+

广度

本文覆盖了PostgreSQL逻辑备份的完整工具链：pg_dump（单库）、pg_dumpall（实例级）、pg_restore（恢复）、COPY/\copy（高性能导入导出），以及第三方扩展工具pgcopydb和pg_dumpbinary。

深度

深入剖析了各工具的核心参数、格式差异、性能特性，重点讲解了：

• directory格式是唯一支持并行转储的格式
• 单表大表通过pg_export_snapshot()实现伪并行备份的原理
• PostgreSQL 17 ON_ERROR选项的设计动机和使用方式
• COPY相比INSERT的性能优势来源

复杂度

涉及的技术复杂度包括：

• 多格式输出（plain/custom/directory/tar）的选型权衡
• 并行备份与恢复的参数调优（-j）
• 快照导出与跨事务一致性保证
• 服务端COPY与客户端\copy的权限和路径差异

官方文档

• pg_dump
• pg_dumpall
• pg_restore
• COPY
• pg_export_snapshot
• PostgreSQL 17 Release Notes

参考链接

• pgcopydb GitHub Repository
• pg_dumpbinary GitHub Repository
• Stack Overflow: COPY vs INSERT performance
• PostgreSQL Wiki: Backup and Restore

总结

本文系统梳理了PostgreSQL逻辑备份的完整工具链。pg_dump作为单数据库逻辑备份的核心工具，支持plain、custom、directory和tar四种输出格式，其中custom和directory格式支持pg_restore的选择性恢复与并行恢复，directory格式是唯一支持并行转储的格式。pg_dumpall扩展了pg_dump的能力，可备份整个集群及全局对象（角色、表空间、配置参数权限）。

COPY命令是PostgreSQL官方提供的高性能批量数据传输工具，在批量加载场景下性能显著优于INSERT；PostgreSQL 17引入的ON_ERROR选项解决了大规模导入时错误即回滚的痛点。对于单表极大的场景，可借助pg_export_snapshot()导出快照并结合多事务并行COPY实现加速；对于特殊数据类型（如超大bytea），可选用pg_dumpbinary进行二进制格式转储。