PostgreSQL 逻辑备份工具整理
==============================
一、工具总览对比
----------------
| 工具 | 层级 | 粒度 | 并行 | 格式支持 | 适用场景 |
|---------------|-----------|--------------|------|-------------------|--------------------------|
| pg_dump | 数据库级 | 单库/单表/部分 | 是 | SQL/Custom/Tar/Directory | 日常备份、迁移、恢复测试 |
| pg_dumpall | 集群级 | 全集群 | 否 | 纯 SQL | 全库迁移、集群级备份 |
| COPY | 表级 | 单表 | 否 | CSV/Binary/Text | 数据导入导出、ETL |
| pg_bulkload | 表级 | 单表 | 是 | CSV/Binary | 超大数据量高速加载 |
二、pg_dump 详解
----------------
1. 基本语法
pg_dump [选项] 数据库名 > 备份文件
2. 常用模式
【纯文本 SQL】
pg_dump -U postgres -h localhost dbname > dbname.sql
-- 优点：人类可读，可编辑
-- 缺点：恢复慢，体积大
【自定义格式（推荐）】
pg_dump -U postgres -h localhost -Fc dbname > dbname.dump
-- -Fc: Custom 格式，压缩 + 支持选择性恢复
-- 优点：压缩率高，支持 pg_restore 细粒度恢复
【目录格式（并行备份）】
pg_dump -U postgres -h localhost -Fd dbname -j 4 -f /backup/dbname
-- -Fd: Directory 格式
-- -j 4: 4 个并行作业
-- 优点：并行备份/恢复，速度最快
【Tar 格式】
pg_dump -U postgres -h localhost -Ft dbname > dbname.tar
-- 优点：兼容 tar 工具
-- 缺点：不支持并行恢复
3. 关键参数
| 参数 | 说明 |
|----------------|----------------------------------|
| -a, --data-only | 仅备份数据，不备份结构 |
| -s, --schema-only | 仅备份结构，不备份数据 |
| -n, --schema | 仅备份指定 schema |
| -t, --table | 仅备份指定表 |
| -T, --exclude-table | 排除指定表 |
| --column-inserts | 使用 INSERT 带列名（兼容性好） |
| --inserts | 使用 INSERT 而非 COPY（兼容性好） |
| --no-owner | 不备份对象所有者信息 |
| --no-privileges | 不备份权限（GRANT/REVOKE） |
| --disable-triggers | 恢复时禁用触发器（用于数据恢复） |
4. 备份内容
【包含】
- 表结构（CREATE TABLE）
- 索引（CREATE INDEX）
- 约束（主键、外键、CHECK、UNIQUE）
- 触发器
- 视图、物化视图
- 函数、存储过程
- 序列（SEQUENCE）
- 数据（COPY 或 INSERT 格式）
- 注释（COMMENT ON）
【不包含】
- 数据库用户/角色（用 pg_dumpall -g）
- 表空间（Tablespace）物理位置
- 配置文件（postgresql.conf 等）
- WAL 日志
5. 恢复方式
【Custom 格式恢复】
pg_restore -U postgres -d dbname dbname.dump
pg_restore -U postgres -d dbname -t tablename dbname.dump # 单表恢复
【SQL 格式恢复】
psql -U postgres -d dbname < dbname.sql
【目录格式恢复】
pg_restore -U postgres -d dbname -j 4 /backup/dbname
6. 生产最佳实践
- 优先使用 Custom 或 Directory 格式
- 大库使用 -Fd -j 并行备份
- 定期验证备份：pg_restore --list 检查完整性
- 结合 cron 定时任务自动化
- 备份文件异地存储，加密传输
三、pg_dumpall 详解
-------------------
1. 基本语法
pg_dumpall [选项] > 备份文件
2. 特点
【与 pg_dump 的核心区别】
- pg_dump：单数据库，支持多种格式，支持并行
- pg_dumpall：全集群，仅 SQL 格式，不支持并行
3. 备份内容
【全局对象】
- 角色（CREATE ROLE / ALTER ROLE）
- 表空间（CREATE TABLESPACE）
- 数据库属性（ALTER DATABASE）
- 配置参数（ALTER SYSTEM，PG 9.4+）
【所有数据库】
- 每个数据库的完整内容（等价于逐个 pg_dump）
4. 常用命令
【完整集群备份】
pg_dumpall -U postgres > full_backup.sql
【仅备份全局对象】
pg_dumpall -U postgres -g > globals.sql
【仅备份角色】
pg_dumpall -U postgres -r > roles.sql
【仅备份表空间】
pg_dumpall -U postgres -t > tablespaces.sql
5. 恢复方式
【全集群恢复】
psql -U postgres < full_backup.sql
-- 注意：需在 template1 或新集群执行
【分步恢复】
psql -U postgres -f globals.sql # 先恢复角色和表空间
createdb -U postgres newdb # 创建数据库
pg_restore -U postgres -d newdb db.dump # 再恢复单库数据
6. 生产注意事项
- 全集群备份耗时长，建议在低峰期执行
- 备份期间会持有短锁，大库可能影响性能
- 角色密码以 MD5/SCRAM 哈希存储，恢复后密码有效
- 不备份物理文件，仅逻辑对象
四、COPY 详解
-------------
1. 基本语法
【导出】
COPY table_name TO '/path/to/file.csv' WITH (FORMAT CSV, HEADER);
COPY table_name TO STDOUT WITH (FORMAT BINARY);
【导入】
COPY table_name FROM '/path/to/file.csv' WITH (FORMAT CSV, HEADER);
COPY table_name FROM STDIN WITH (FORMAT BINARY);
2. 格式支持
| 格式 | 说明 | 适用场景 |
|--------|----------------------------------|----------------------|
| TEXT | 默认，制表符分隔，\N 表示 NULL | 简单导出，人类可读 |
| CSV | 逗号分隔，支持引号转义 | 与 Excel/其他系统交互 |
| BINARY | 二进制格式，速度最快，体积最小 | 同构系统间高速传输 |
3. 关键选项
| 选项 | 说明 |
|------------------|----------------------------------|
| HEADER | 包含列名标题行 |
| DELIMITER | 自定义分隔符（默认逗号） |
| QUOTE | 引号字符（默认双引号） |
| ESCAPE | 转义字符 |
| NULL | NULL 表示方式（默认空字符串） |
| ENCODING | 文件编码 |
| FORCE_QUOTE | 强制引号列 |
| FORCE_NOT_NULL | 指定列不视为 NULL |
4. 示例
【CSV 导出（含标题）】
COPY (SELECT * FROM users WHERE created_at > '2024-01-01')
TO '/tmp/users_2024.csv' WITH (FORMAT CSV, HEADER, ENCODING 'UTF8');
【CSV 导入（跳过标题）】
COPY users FROM '/tmp/users.csv' WITH (FORMAT CSV, HEADER);
【Binary 导出】
COPY large_table TO '/tmp/large_table.bin' WITH (FORMAT BINARY);
【使用 psql 的 \copy（客户端文件）】
\copy users TO '/local/path/users.csv' CSV HEADER
-- \copy 通过 STDIN/STDOUT 传输，文件在客户端
5. 与 SQL COPY 的区别
| 特性 | SQL COPY (服务端) | \copy (客户端) |
|------------|-------------------------------|-----------------------------|
| 文件位置 | 服务端文件系统 | 客户端文件系统 |
| 权限要求 | 超级用户或 pg_read_server_files | 普通用户即可 |
| 性能 | 直接文件 IO，更快 | 通过网络传输，稍慢 |
| 安全性 | 可访问服务端任意文件 | 仅客户端文件，更安全 |
6. 生产注意事项
- 大表 COPY 会触发 WAL 大量写入，注意磁盘 IO
- COPY 导入时自动禁用索引，导入后重建（比逐行更新索引快）
- 外键约束在 COPY 后检查，大量数据导入前可临时禁用
- 使用 BINARY 格式时，源和目标必须是相同架构（大端/小端、版本兼容）
五、pg_bulkload 详解
--------------------
1. 简介
pg_bulkload 是 PostgreSQL 的高速批量加载工具，
绕过共享缓冲池和 WAL，直接写入数据文件，速度极快。
2. 安装
【源码编译】
git clone https://github.com/ossc-db/pg_bulkload.git
cd pg_bulkload && make && make install
【创建扩展】
CREATE EXTENSION pg_bulkload;
3. 核心原理
┌─────────────────────────────────────────────────────────────┐
│ 标准 COPY/INSERT 流程： │
│ 客户端 → 服务端解析 → 共享缓冲池 → WAL 写入 → 数据文件 │
│ │
│ pg_bulkload 流程： │
│ 客户端 → 直接解析 → 数据文件（绕过缓冲池和 WAL） │
│ │
│ 恢复机制：通过 pg_bulkload 的日志文件（.log）进行恢复 │
└─────────────────────────────────────────────────────────────┘
4. 使用方法
【控制文件方式（推荐）】
创建 load.ctl：
TYPE = CSV
INPUT = /data/large_table.csv
OUTPUT = public.large_table
MULTI_PROCESS = YES
PARSE_BADFILE = /data/parse_bad.log
DUPLICATE_BADFILE = /data/dup_bad.log
执行：
pg_bulkload load.ctl -d dbname -U postgres
【命令行方式】
pg_bulkload -i /data/input.csv -O public.target_table -l /data/bulkload.log -d dbname
5. 关键参数
| 参数 | 说明 |
|------------------|----------------------------------|
| TYPE | 输入格式（CSV/TEXT/FIXED/BINARY/DB）|
| INPUT | 输入文件路径 |
| OUTPUT | 目标表 |
| MULTI_PROCESS | 多进程并行加载（YES/NO） |
| WRITER | 写入模式（DIRECT/ BUFFERED） |
| COMMIT | 每批次提交行数 |
| PARSE_BADFILE | 解析错误日志文件 |
| DUPLICATE_BADFILE| 主键重复错误日志文件 |
| LOGFILE | 操作日志文件 |
| LOAD | 加载方法（REPLACE/APPEND/TRUNCATE）|
6. 写入模式
| 模式 | 说明 | 风险 |
|----------|----------------------------------|-------------------------|
| DIRECT | 直接写数据文件，绕过缓冲池和 WAL | 崩溃后需用日志恢复，非事务安全 |
| BUFFERED | 使用共享缓冲池，生成 WAL | 事务安全，但速度较慢 |
7. 与 COPY 性能对比
| 场景 | COPY | pg_bulkload (DIRECT) | 提升倍数 |
|------------------|---------|---------------------|---------|
| 10万行小表 | 2s | 1s | 2x |
| 1000万行大表 | 120s | 15s | 8x |
| 1亿行超大数据 | 1800s | 120s | 15x |
| 含索引大表 | 300s | 30s（延迟索引重建） | 10x |
8. 生产注意事项
- DIRECT 模式非事务安全，加载期间崩溃可能导致数据不一致
- 加载前建议删除索引，加载后重建（pg_bulkload 可自动处理）
- 外键约束需在加载前禁用，加载后启用
- 需要超级用户权限或 pg_bulkload 角色
- 日志文件需妥善保存，用于崩溃恢复
- 不触发触发器，加载后需手动补全触发器逻辑
六、四工具选型决策树
--------------------
需要备份/加载？
│
┌─────────────┴─────────────┐
│ │
备份（导出） 恢复（导入）
│ │
┌──────┴──────┐ ┌─────┴─────┐
│ │ │ │
单库/部分 全集群 中小数据量 超大数据量
│ │ │ │
pg_dump pg_dumpall COPY pg_bulkload
(-Fc/-Fd) (-g 全局对象) (\copy) (DIRECT模式)
七、生产环境备份策略建议
------------------------
1. 日常备份
- 中小型库：pg_dump -Fc 每晚全量备份
- 大型库：pg_dump -Fd -j 4 每周全量 + 每日增量（WAL 归档）
2. 集群迁移
- 源端：pg_dumpall -g > globals.sql（全局对象）
- 源端：pg_dump -Fd -j 4 逐个库备份
- 目标端：先恢复 globals.sql，再 pg_restore 各库
3. 数据迁移/ETL
- 同构系统：COPY BINARY 或 pg_bulkload
- 异构系统：COPY CSV 或 pg_dump --inserts
- 超大数据：pg_bulkload DIRECT 模式 + 禁用索引/外键
4. 备份验证
- 定期 pg_restore --list 检查备份完整性
- 测试环境定期恢复演练
- 监控备份文件大小和生成时间，异常告警
八、关键配置参数
----------------
| 参数名 | 说明 | 影响工具 |
|------------------------|------------------------------|----------------|
| max_wal_size | WAL 量上限，影响检查点频率 | COPY/pg_bulkload |
| checkpoint_timeout | 检查点间隔 | COPY/pg_bulkload |
| maintenance_work_mem | 维护操作内存（创建索引等） | pg_restore |
| work_mem | 排序/哈希操作内存 | pg_dump 排序 |
| shared_buffers | 共享缓冲池大小 | COPY 导入性能 |
| wal_buffers | WAL 缓冲区 | COPY 写入性能 |
| max_parallel_workers | 最大并行工作进程 | pg_dump -Fd -j |
九、常见问题与解决方案
----------------------
| 问题 | 原因 | 解决方案 |
|----------------------|------------------------------|-----------------------------|
| pg_dump 内存不足 | 大表排序或复杂视图 | 使用 -Fd 格式，降低 work_mem |
| pg_restore 慢 | 大量索引重建 | 先 -s 恢复结构，再 -a 恢复数据 |
| COPY 权限拒绝 | 非超级用户访问服务端文件 | 使用 \copy（客户端文件） |
| pg_bulkload 崩溃后数据损坏 | DIRECT 模式无 WAL 保护 | 使用日志恢复或改用 BUFFERED |
| 备份文件过大 | 未压缩或包含大对象 | 使用 -Fc 压缩，排除大对象 |
| 外键约束导入失败 | 数据顺序与外键依赖冲突 | 使用 --disable-triggers 或调整顺序 |
