适用场景

PostgreSQL 在政务与行业系统里越来越多。它的运维重点和 MySQL 不同：WAL 归档决定能不能做时间点恢复，VACUUM 决定性能会不会随写入退化，连接数模型决定高峰会不会被打爆。

配置步骤（性能与膨胀治理）

-- 1) 慢查询（需 pg_stat_statements）
SELECT query, calls, round(total_exec_time/calls) AS avg_ms, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- 2) 连接与等待
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle' LIMIT 10;

-- 3) 锁等待
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query
FROM pg_stat_activity blocked JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- 4) 表膨胀与死元组
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- 5) 长事务（会阻断 vacuum）
SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY duration DESC LIMIT 5;

关键参数与建议

  • 部署：数据目录与 WAL 目录分离，参数按内存设置（shared_buffers、work_mem、effective_cache_size）
  • WAL 归档：开启 archive_mode，归档目录独立并定期清理；没有归档就只能恢复到全备点
  • 备份：pg_basebackup 做基础备份 + WAL 归档 = 时间点恢复；逻辑备份用 pg_dump 兜底
  • 恢复验证：定期用基础备份 + WAL 恢复到指定时间点，验证数据一致
  • 连接管理：连接数按 内存/每连接开销 估算，配合 PgBouncer 连接池
  • 膨胀治理：autovacuum 参数按表调，长事务与准备事务要及时发现
  • 监控：连接数、慢查询、锁等待、复制延迟、表膨胀、WAL 生成速率
  • 权限：应用账号按 schema/表授权，禁止用 postgres 超级用户连业务

容易踩的坑

  • 长事务一直挂着，autovacuum 清不掉死元组，表和索引持续膨胀
  • 连接数打到上限，新连接直接失败
  • 索引过多，写入放大
  • work_mem 太小，排序落盘
  • 从不看 pg_stat_statements，慢查询靠用户投诉发现
  • 手动 VACUUM FULL 在业务高峰执行，锁表导致业务中断

验证与巡检

su - postgres -c "psql -c 'select count(*) from pg_stat_activity;'"
# 连接水位 < 70%

su - postgres -c "psql -c \"select relname, n_dead_tup from pg_stat_user_tables order by n_dead_tup desc limit 5;\""
# 死元组趋势与 autovacuum 执行记录
  • 巡检：无 >1 小时长事务、死元组受控、无锁等待堆积、慢查询有治理记录

小结

PG 运维验收：能说出备份点与 WAL 归档位置、能实测恢复到指定时间点、慢查询与膨胀有治理记录。

> 说明：文中命令为通用写法，不同型号/版本可能略有差异，落地前请对照设备实际版本的官方文档；带外管理与安全设备变更建议先在测试设备验证。