从 MySQL 迁到 PostgreSQL 之后，备份和调优又踩了几个坑

上次写了从 MySQL 迁到 PostgreSQL 的迁移过程，以为迁完就轻松了。结果上线跑了两个月，备份策略和参数调优又是一堆问题。这篇文章记一下我在这两个方向踩的坑。

pg_dump 的格式选错了，恢复时差点出事

迁完 PostgreSQL 之后，备份方案第一反应就是用 pg_dump，跟 MySQL 的 mysqldump 差不多嘛。直接跑：

pg_dump -U postgres -d mydb > backup.sql

跑完一看，200MB 的库导出来一个纯文本 SQL 文件，好像也没什么问题。但等到真要恢复的时候，800MB 的库，psql < backup.sql 跑了将近 40 分钟。要是这是生产环境故障恢复，黄花菜都凉了。

后来才知道 pg_dump 有三种输出格式，差别很大：

# 纯文本格式（默认）— 只能用 psql 恢复，不能并行
pg_dump -U postgres -d mydb > backup.sql

# custom 格式 — 支持并行恢复、选择性恢复
pg_dump -U postgres -d mydb -F c -f backup.dump

# directory 格式 — 支持并行备份和恢复，适合大库
pg_dump -U postgres -d mydb -F d -f backup_dir/

关键是 custom 和 directory 格式可以用 pg_restore -j 并行恢复。换成 directory 格式之后，同样的库恢复时间从 40 分钟降到 8 分钟左右：

pg_restore -U postgres -d mydb -j 4 backup_dir/

-j 4 表示 4 个并发线程同时恢复，对多 CPU 的服务器效果很明显。但有个前提，恢复的目标表必须有足够的锁，并发恢复时如果有其他进程在写同一个表，会直接卡住。

还有一个坑，pg_dump 默认不备份角色和表空间。换服务器恢复的时候，先得把角色单独导出来：

pg_dumpall -U postgres --roles-only > roles.sql

不然恢复的时候一堆 role does not exist 报错，看着吓人但其实角色没导。我第一次恢复到新机器时被这个坑了一下。

pg_basebackup + WAL 归档：物理备份的核心

pg_dump 是逻辑备份，适合小库或者部分表恢复。但如果是几 TB 的库，或者需要做时间点恢复（PITR），就得用物理备份了。

PostgreSQL 的物理备份思路跟 MySQL 的 binlog + xtrabackup 类似，但配置起来细节更多。核心是两步：pg_basebackup 做全量基线，WAL 归档做增量。

首先在 postgresql.conf 里开启 WAL 归档：

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /data/wal_archive/%f && cp %p /data/wal_archive/%f'

这个 archive_command 有个坑，test ! -f 这个检查不能省。如果归档目录里已经存在同名 WAL 文件，PostgreSQL 会认为归档失败，然后不断重试，把 WAL 堆积在 pg_wal/ 目录里，磁盘迟早被撑爆。我有一回就是这样，pg_wal 目录涨到 50GB，查了半天才发现是归档路径写错了。

全量备份用 pg_basebackup：

pg_basebackup -U postgres -D /data/backup/base -Ft -z -P

-Ft 表示 tar 格式，-z 压缩，-P 显示进度。恢复的时候，把 base 解压到数据目录，然后配一个 recovery.signal 文件，PostgreSQL 启动时会自动进入恢复模式，读取归档的 WAL 重放。

时间点恢复需要额外配 recovery_target_time：

# postgresql.conf 或 recovery.conf
restore_command = 'cp /data/wal_archive/%f %p'
recovery_target_time = '2026-07-05 14:30:00'

配好之后启动 PostgreSQL，它会重放 WAL 到指定时间点然后停下来。这时候数据库是只读的，确认数据没问题后执行 pg_wal_replay_resume() 就能恢复正常读写。

我踩的一个坑是 recovery_target_time 设的时间点刚好在某个大事务中间，恢复直接报错说事务不完整。后来看文档才知道可以加 recovery_target_action = 'pause'，让它停在目标时间点之前最近的合法位置，而不是直接报错退出。

shared_buffers 不是越大越好

PostgreSQL 的内存参数里，shared_buffers 是被讨论最多的一个。网上很多文章说设到物理内存的 25%，我一开始也这么干了，32GB 内存的服务器设了 8GB。

实际跑下来发现有个问题。PostgreSQL 跟 MySQL 不一样，它严重依赖操作系统的 page cache。shared_buffers 设太大，留给 OS cache 的就少了，反而影响读取性能。因为 PostgreSQL 的数据读取路径是先查 shared_buffers，没命中就去 OS page cache 找，再没有才从磁盘读。OS cache 这一层非常关键。

后来我把 shared_buffers 从 8GB 降到 6GB，某些查询反而快了。当然这个值不是绝对的，跟具体工作负载有关。我的经验是，如果主要跑 OLTP（小查询多），25% 可以；如果有大量全表扫描，降到 15%-20%，把更多内存留给 OS cache。

监控 shared_buffers 的命中率：

SELECT 
  sum(blks_hit) AS hit,
  sum(blks_read) AS read,
  sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) AS hit_ratio
FROM pg_stat_database;

命中率低于 99% 就要考虑是不是 shared_buffers 太小，或者查询缺少索引导致大量磁盘读。

work_mem 的隐藏陷阱

work_mem 控制单个查询操作（排序、哈希）能用的内存。默认值只有 4MB，对稍微复杂点的排序查询根本不够用，溢到磁盘后性能直接掉一个量级。

但这个参数不能随便调大。work_mem 是按连接 × 每个操作算的，不是全局共享的。一个查询里有 3 个排序操作，就可能同时用 3 倍 work_mem。如果有 100 个并发连接，每个连接几个排序操作，内存消耗是乘数关系。

我一开始把 work_mem 设到 256MB，想着排序快一点。结果并发一起来，直接 OOM。32GB 内存的服务器，100 个连接，理论上最坏情况要 100 × 256MB × 3 = 75GB，远超物理内存。

后来改成全局设 16MB，对特定需要的会话单独调：

-- 全局设保守值
ALTER SYSTEM SET work_mem = '16MB';

-- 对大查询会话单独调
SET work_mem = '128MB';
SELECT ... -- 复杂排序查询
RESET work_mem;

这样既不会因为默认值太小导致日常查询慢，也不会因为全局调太大导致 OOM。

autovacuum 没你想的那么省心

PostgreSQL 的 MVCC 实现跟 MySQL 不同，更新和删除不会立即回收空间，而是产生"死元组"，需要 VACUUM 来清理。autovacuum 默认是开启的，但默认参数对写密集的场景太保守了。

autovacuum_vacuum_scale_factor 默认 0.2，意思是表的 20% 数据变更后才触发 vacuum。对于 1000 万行的大表，要等 200 万行变更才清理，这期间死元组堆积，查询性能下降，而且膨胀后的表占用更多磁盘空间。

我的做法是针对大表单独调：

-- 对写频繁的大表，降低触发阈值
ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE my_table SET (autovacuum_analyze_scale_factor = 0.02);

-- 对特别活跃的表，还可以设绝对值触发
ALTER TABLE my_table SET (autovacuum_vacuum_threshold = 1000);

另外，autovacuum_max_workers 默认只有 3 个。如果库里有几十张表都需要清理，3 个 worker 根本忙不过来。但也不能调太大，每个 worker 都会消耗 CPU 和 IO。我设成了 6，配合 autovacuum_naptime = 30s，让检查频率快一些。

怎么看 autovacuum 有没有跟上？查 pg_stat_user_tables：

SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

如果 n_dead_tup 持续很高，last_autovacuum 时间很早，说明 autovacuum 没跟上，需要调参数或者手动 VACUUM。

checkpoint 的 IO 风暴

最后一个坑是 checkpoint。PostgreSQL 的 checkpoint 把内存中的脏页刷到磁盘，默认配置下可能产生明显的 IO 突刺。

checkpoint_timeout 默认 5 分钟，max_wal_size 默认 1GB。如果写入量大，1GB 的 WAL 很快就写满了，触发 checkpoint 的频率就高。而每次 checkpoint 都会把所有脏页刷盘，IO 飙升，查询延迟跟着抖动。

看 checkpoint 日志可以确认问题：

log_checkpoints = on

开启后日志里会打印每次 checkpoint 的统计信息，包括写了多少 buffer、花了多长时间。如果看到 checkpoint 频率很高，或者每次刷的 buffer 很多，就该调了。

我的调整方案：

checkpoint_timeout = 15min
max_wal_size = 4GB
checkpoint_completion_target = 0.9

checkpoint_completion_target = 0.9 是关键参数，意思是把刷脏页的动作分散到 checkpoint 间隔的 90% 时间内完成，而不是集中到最后一下猛刷。这个改完之后，IO 突刺明显平缓了。

不过 max_wal_size 调大也有代价，崩溃恢复时要重放的 WAL 更多，恢复时间变长。所以这个值不是越大越好，要在 IO 平滑和恢复时间之间找平衡。我 4GB 是在 32GB 内存、SSD 磁盘的环境下设的，机械硬盘的话建议小一点。

我现在用的备份方案

现在跑的备份策略大概是这样：每天用 pg_dump -F d -j 4 做 directory 格式的并行逻辑备份，压缩后传到异地。WAL 归档实时跑，本地和异地各一份。每周用 pg_basebackup 做一次物理全量基线，配合 WAL 归档支持 PITR。

还有一件事是恢复演练，每月一次，拿备份到测试环境恢复验证。这个一开始觉得没必要，直到有一次真需要恢复才发现备份文件损坏了，pg_dump 跑到一半磁盘满了，生成了一个不完整的 dump 文件，但脚本没检查退出码就标记为备份成功。从那以后恢复演练成了必做项。