PostgreSQL 中的 pg_dump 和 pg_restore 备份恢复

bash pg_dump [连接选项] [备份选项] 数据库名 > 输出文件

最常用的连接选项：

- `-U` 或 `--username`：数据库用户名
- `-h` 或 `--host`：服务器地址（默认本地套接字）
- `-p` 或 `--port`：端口号（默认 5432）
- `-d` 或 `--dbname`：数据库名

#### 指定备份格式

使用 `-F` 或 `--format` 参数选择输出格式：

| 格式 | 参数值 | 说明 |
|------|--------|------|
| 纯文本 | `p` 或 `plain` | 默认格式，生成 SQL 脚本，只能用 `psql` 恢复。 |
| 自定义 | `c` 或 `custom` | 最灵活的格式，压缩存储，支持 `pg_restore` 选择性恢复。推荐使用。 |
| 目录 | `d` 或 `directory` | 生成一个包含多个文件的目录，支持并行备份和恢复。 |
| tar | `t` 或 `tar` | 生成 tar 归档，可用于恢复，但不支持并行且不能压缩。 |

#### 备份整个数据库

最常用的命令是生成自定义格式的压缩备份：

```bash
pg_dump -U postgres -h localhost -F c -b -v -f /backups/mydb_$(date +%Y%m%d).custom mydb

• -F c：指定自定义格式。
• -b：包含大型对象。
• -v：详细模式，显示进度。
• -f：指定输出文件，而不是重定向。

如果希望生成纯文本 SQL 文件，直接省略 -F 即可：

pg_dump -U postgres -h localhost -f /backups/mydb.sql mydb

备份特定对象

只备份特定表：

pg_dump -U postgres -t 'public.users' -t 'public.orders' -F c -f tables_backup.custom mydb

-t 参数可以多次使用，支持通配符 *（需用引号包含）。

只备份特定模式（Schema）：

pg_dump -U postgres -n 'sales' -n 'inventory' -F c -f schemas_backup.custom mydb

排除某些表：

pg_dump -U postgres -T 'public.temp_logs' -F c -f exclude_table.custom mydb

远程备份与管道传输

备份远程数据库并直接压缩（避免本地磁盘写满）：

pg_dump -h remote-server -U myuser -F c mydb | gzip > mydb.custom.gz

恢复时解压并恢复：

gunzip -c mydb.custom.gz | pg_restore -U postgres -d newdb

使用 pg_restore 恢复备份

pg_restore 仅用于恢复 pg_dump 生成的非纯文本格式（custom、directory、tar）。如果备份是 .sql 文件，请直接使用 psql。

基本恢复命令

恢复一个完整的数据库：

pg_restore -U postgres -h localhost -d target_database -v /backups/mydb.custom

当 target_database 不存在时，通常需要先创建一个空库再恢复：

createdb -U postgres newdb
pg_restore -U postgres -d newdb /backups/mydb.custom

选择性恢复

pg_restore 的强大之处在于你无需恢复整个备份。

仅恢复数据（忽略表结构）：

pg_restore -U postgres -d mydb --data-only -v backup.custom

仅恢复结构（模式定义）：

pg_restore -U postgres -d mydb --schema-only -v backup.custom

恢复指定的表：

pg_restore -U postgres -d mydb -t 'users' -t 'orders' backup.custom

恢复到不同的表空间或所有者： 可以通过选项 --no-owner 和 --role 调整权限，避免权限冲突。

并行恢复提升速度

使用 -j 参数指定并行任务数，对大数据量恢复效果显著（仅目录格式或自定义格式支持）：

pg_restore -U postgres -d mydb -j 4 backup.custom

恢复目录格式备份

目录格式备份是一个文件夹，恢复时指向该文件夹即可：

pg_dump -F d -j 4 -f /backups/mydb_dir mydb   # 并行备份
pg_restore -U postgres -d mydb -j 4 /backups/mydb_dir

使用列表文件精细控制

先生成备份中所有内容的列表：

pg_restore -l backup.custom > list.txt

编辑 list.txt，在每行开头添加 ; 注释掉不需要的对象，或者调整顺序。然后按照编辑后的列表恢复：

pg_restore -U postgres -d mydb -L list.txt backup.custom

恢复纯文本 SQL 备份

对于 pg_dump 生成的 .sql 文件，不需要 pg_restore，直接使用 psql 执行脚本即可：

psql -U postgres -d target_db -f /backups/mydb.sql

如果目标库不存在，先创建：

psql -U postgres -c "CREATE DATABASE newdb;"
psql -U postgres -d newdb -f mydb.sql

高级备份策略与技巧

1. 确保数据一致且不影响生产

pg_dump 默认使用可重复读事务，能保证备份期间的数据一致性，但会持有锁和事务。对于超大数据库，可以考虑：

• 使用 --no-lock 选项跳过表锁（前提是数据变化不大）。
• 将 pg_dump 与快照复制结合，或在从库上进行备份。

2. 排除不需要的对象

# 不备份注释、表空间、所有者等信息，方便迁移到不同环境
pg_dump --no-owner --no-acl --no-comments ...

3. 压缩与分割

对于纯文本备份，可以在备份后使用 gzip 压缩；对于自定义格式，本身已经压缩。如果备份文件巨大，可以使用 split 命令分割：

pg_dump -F c mydb | split -b 1G - mydb_custom_part_

恢复时合并：

cat mydb_custom_part_* | pg_restore -d newdb