数据备份与恢复最佳实践–PG版

为了在大量数据导入期间避免产生过多 WAL（Write-Ahead Logging）日志（从而提升性能并减少归档/复制压力），可以临时降低 WAL 记录级别，但需注意前提条件:

1,要防止加载时增量 WAL 日志记录，请禁用存档和流复制;

方法是：

-- 将 wal_level 设置为 minimal，  
-- 将 archive_mode 设置为 off，
-- 将 max_wal_senders 设置为 0。
show  wal_level ;
show archive_mode;
show max_wal_senders;

每个参数的作用:

⚠️ 重要前提和限制

**
必须在加载前修改配置并重启 PostgreSQL
wal_level 是重启级参数，修改后必须重启数据库才能生效。
仅适用于一次性批量加载场景
适用于初始化数据库、ETL 导入、测试数据填充等场景。
…加载完成后必须恢复原配置并做基础备份…
恢复 wal_level = replica（或 logical）、archive_mode = on、max_wal_senders > 0。
立即执行一次新的 base backup（基础备份），因为 wal_level = minimal 期间产生的 WAL 无法用于恢复或复制。之前的备份 + WAL 链在此期间已断裂。
不能用于已有复制槽或需要时间点恢复（PITR）的系统
否则会导致复制中断或无法恢复到加载期间的任意时间点。

具体操作步骤:

-- 1. 修改 postgresql.conf
wal_level = minimal
archive_mode = off
max_wal_senders = 0

-- 2. 重启 PostgreSQL
pg_ctl restart

-- 3. 执行大批量数据加载（COPY、INSERT 等）

-- 4. 加载完成后，改回配置
wal_level = replica          -- 或 logical
archive_mode = on
max_wal_senders = 10         -- 按需设置

-- 5. 再次重启（wal_level 需重启）
pg_ctl restart

-- 6. 立即创建新的基础备份
pg_basebackup -D /backup/new_base -Ft -z -P

💡 替代方案（无需重启）

如果不能重启数据库，可考虑以下折中方法（但无法完全关闭 WAL）：

使用 COPY 而非逐条 INSERT（WAL 更紧凑）。
在事务中执行批量插入（减少事务日志开销）。
临时将表设为 UNLOGGED（⚠️ 数据不持久，崩溃会丢失）

CREATE UNLOGGED TABLE temp_load (...);
-- 加载数据
INSERT INTO real_table SELECT * FROM temp_load;
DROP TABLE temp_load;

UNLOGGED 表不写 WAL，但仅适用于可重建的数据。 

流程:
改配置 → 重启 → 加载 → 恢复配置 → 重启 → 新建基础备份”

基础理论:

✅ 一、pg_dump 已自动处理的部分（无需手动干预）

当执行 完整数据库转储（pg_dump -Fc 或 pg_dump –schema-only + –data-only 组合）时，pg_dump 默认已做到：

先创建表结构（不含索引、外键、触发器）
用 COPY 批量加载数据（高效、WAL 友好）
最后统一创建索引、外键、约束、触发器等
这本身就规避了：

插入时维护索引的开销
外键约束的逐行检查
触发器的重复执行
📌 所以，对于完整转储，你无需手动删除索引/外键——pg_dump 已为你安排好最优顺序。

✅ 二、你需要手动执行的优化措施（还原时）

增大内存和 WAL 相关参数
在 postgresql.conf 中临时调整（还原期间生效）：

maintenance_work_mem = 2GB # 默认通常 64MB~1GB；建议设为物理内存的 10%~25%（但 ≤ 4GB）
max_wal_size = 4GB # 允许 WAL 暂时增长，避免频繁检查点（默认 1GB）

✅ 作用：加速 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY。

⚠️ 注意：shared_buffers 和 work_mem 对 COPY 本身影响较小，重点调 maintenance_work_mem。

profile
Qwen3-Max
你引用的内容是对 PostgreSQL 使用 pg_dump/pg_restore 进行高效数据恢复（还原）的最佳实践总结，非常准确。下面我将这段内容系统化梳理、补充说明并给出实操建议，以便你在生产环境中安全、高效地还原大规模数据库。

✅ 一、pg_dump 已自动处理的部分（无需手动干预）
当执行 完整数据库转储（pg_dump -Fc 或 pg_dump –schema-only + –data-only 组合）时，pg_dump 默认已做到：

先创建表结构（不含索引、外键、触发器）
用 COPY 批量加载数据（高效、WAL 友好）
最后统一创建索引、外键、约束、触发器等
这本身就规避了：

插入时维护索引的开销
外键约束的逐行检查
触发器的重复执行
📌 所以，对于完整转储，你无需手动删除索引/外键——pg_dump 已为你安排好最优顺序。

✅ 二、你需要手动执行的优化措施（还原时）

增大内存和 WAL 相关参数
在 postgresql.conf 中临时调整（还原期间生效）：

ini

1
2
maintenance_work_mem = 2GB # 默认通常 64MB~1GB；建议设为物理内存的 10%~25%（但 ≤ 4GB）
max_wal_size = 4GB # 允许 WAL 暂时增长，避免频繁检查点（默认 1GB）
✅ 作用：加速 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY。

⚠️ 注意：shared_buffers 和 work_mem 对 COPY 本身影响较小，重点调 maintenance_work_mem。

临时禁用 WAL 归档和流复制（大幅提速）
前提：你接受“还原完成后必须做一次全新基础备份”。

还原前，修改 postgresql.conf：

wal_level = minimal
archive_mode = off
max_wal_senders = 0

→ 重启 PostgreSQL（因为 wal_level 是重启参数）

→ 执行 psql -f dump.sql 或 pg_restore -d db dump.tar

→ 还原完成后，改回原配置：

wal_level = replica # 或 logical
archive_mode = on
max_wal_senders = 10 # 按需

profile
Qwen3-Max
你引用的内容是对 PostgreSQL 使用 pg_dump/pg_restore 进行高效数据恢复（还原）的最佳实践总结，非常准确。下面我将这段内容系统化梳理、补充说明并给出实操建议，以便你在生产环境中安全、高效地还原大规模数据库。

✅ 一、pg_dump 已自动处理的部分（无需手动干预）
当执行 完整数据库转储（pg_dump -Fc 或 pg_dump –schema-only + –data-only 组合）时，pg_dump 默认已做到：

先创建表结构（不含索引、外键、触发器）
用 COPY 批量加载数据（高效、WAL 友好）
最后统一创建索引、外键、约束、触发器等
这本身就规避了：

插入时维护索引的开销
外键约束的逐行检查
触发器的重复执行
📌 所以，对于完整转储，你无需手动删除索引/外键——pg_dump 已为你安排好最优顺序。

✅ 二、你需要手动执行的优化措施（还原时）

增大内存和 WAL 相关参数
在 postgresql.conf 中临时调整（还原期间生效）：

ini

1
2
maintenance_work_mem = 2GB # 默认通常 64MB~1GB；建议设为物理内存的 10%~25%（但 ≤ 4GB）
max_wal_size = 4GB # 允许 WAL 暂时增长，避免频繁检查点（默认 1GB）
✅ 作用：加速 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY。

⚠️ 注意：shared_buffers 和 work_mem 对 COPY 本身影响较小，重点调 maintenance_work_mem。

临时禁用 WAL 归档和流复制（大幅提速）
前提：你接受“还原完成后必须做一次全新基础备份”。

操作步骤：

ini

1
2
3
4

还原前，修改 postgresql.conf：

wal_level = minimal
archive_mode = off
max_wal_senders = 0
→ 重启 PostgreSQL（因为 wal_level 是重启参数）

→ 执行 psql -f dump.sql 或 pg_restore -d db dump.tar

→ 还原完成后，改回原配置：

ini

1
2
3
wal_level = replica # 或 logical
archive_mode = on
max_wal_senders = 10 # 按需
→ 再次重启

→ 立即执行新基础备份（如 pg_basebackup）

💡 效果：COPY 和 CREATE INDEX 几乎不写 WAL，速度提升显著（尤其对大表）。

使用并行还原（强烈推荐）
对于 自定义格式转储（pg_dump -Fc）：
pg_restore -j 4 -d mydb dump_file.dump

-j 4：使用 4 个并发作业（建议设为 CPU 核数的 50%~100%）
可并行：数据加载 + 索引创建 + 约束创建（如果无依赖）
📌 注意：纯 SQL 脚本（pg_dump 默认文本格式）不支持 -j，必须用 -Fc 格式。

对于文本转储（.sql）：
无法并行，但可用 psql 加速：
psql -v ON_ERROR_STOP=1 -f dump.sql mydb

psql -1 -f dump.sql mydb # -1 即 –single-transaction
pg_restore –single-transaction -d mydb dump.dump

✅ 优点：避免中间状态；要么全成功，要么全回滚。
❌ 缺点：任何错误（如唯一冲突）会导致数小时工作全部回滚。
🚫 不适用于大数据库（事务日志过大，可能耗尽磁盘）
🔎 建议：仅用于小库或已知数据干净的场景。

⚠️ 若同时设 wal_level = minimal，单事务模式无意义（因为 minimal 下大事务仍会写 WAL，且不能跳过）。

还原后务必执行 analyze

ANALYZE;
– 或针对大表：
ANALYZE VERBOSE large_table;

作用：更新表统计信息，使查询优化器生成合理计划。
否则，新数据可能导致执行计划极差（如全表扫描代替索引）。
✅ 三、仅数据转储（–data-only）的特殊处理
如果你只导出了数据（pg_dump –data-only）：

❌ 不会删除/重建索引和外键
❌ 不会禁用触发器
你需要手动做：
还原前（可选但推荐）：

– 删除外键（避免加载时检查）
ALTER TABLE child DROP CONSTRAINT fk_name;
– 删除索引（加速 COPY）
DROP INDEX idx_name;

psql -f data_only_dump.sql mydb

– 重建索引（用 maintenance_work_mem 加速）
CREATE INDEX idx_name ON table (col);
– 重建外键
ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (…) REFERENCES parent(…);
– ANALYZE
ANALYZE;

📌 此时 max_wal_size 调整意义不大，因为主要开销在索引重建，而 maintenance_work_mem 才是关键。

show wal_level;
wal_level = minimal
archive_mode = off
show archive_mode;
max_wal_senders = 0
show max_wal_senders;

立即执行一次新的基础备份（base backup），作为新 WAL 链起点
✅ 这样就不会有“损坏”风险，且后续可安全启用复制。

数据恢复:
pg_restore 恢复 .sql 二进制文本
pg_restore -U postgres -d postgres -v “c:dump.sql”

psql -U postgres -d postgres -f “c:dump.sql”

SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots;

SELECT * FROM pg_replication_slots;

一、什么是“WAL 链”？为什么需要“新起点”？
PostgreSQL 使用 WAL（Write-Ahead Logging） 实现：

崩溃恢复（Crash Recovery）
时间点恢复（PITR, Point-In-Time Recovery）
流复制（Streaming Replication）
WAL 链 = 一个基础备份 + 从备份结束时刻开始的所有连续 WAL 文件

✅ 只有 连续、完整的 WAL 链，才能保证恢复到任意时间点。

二、什么情况下 WAL 链会被“断裂”？（需新基础备份）

场景 说明
✅ 临时设置
wal_level = minimal 加载数据 此期间 不生成完整 WAL（缺少归档/复制所需信息），WAL 链断裂
✅ 从
pg_dump/
pg_restore 恢复逻辑备份 逻辑备份不包含物理 WAL 信息，无法延续原 WAL 链
✅ 主库发生时间线切换（Timeline Switch） 如从备库提升为主库
✅ 手动清空了
pg_wal 目录 极端操作，直接丢弃 WAL

📌 在这些场景后，即使重新启用 archive_mode = on，也无法从旧基础备份恢复到新数据状态。

pg_basebackup -h localhost -U postgres -D d:PgDataBackup -Ft -z -P -v
pg_basebackup: 开始基础备份，等待检查点完成
pg_basebackup: 已完成检查点
pg_basebackup: 预写日志起始于时间点: 0/F000028, 基于时间轴1
pg_basebackup: 启动后台 WAL 接收进程
pg_basebackup: 已创建临时复制槽”pg_basebackup_20336″
70829/70829 kB (100%), 1/1 表空间
pg_basebackup: 预写日志结束点: 0/F000158
pg_basebackup: 等待后台进程结束流操作…
pg_basebackup: 同步数据到磁盘…
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: 基础备份已完成

show fsync;