跳到主要内容

PostgreSQL 运维

VACUUM 机制​

PostgreSQL 使用 MVCC（多版本并发控制），已删除/更新的行不会立即物理删除，而是标记为"死元组"(dead tuples)。VACUUM 负责回收这些空间。

-- 手动 VACUUM
VACUUM VERBOSE my_table;

-- VACUUM FULL（重写整个表，需要排他锁，慎用）
VACUUM FULL my_table;

-- ANALYZE（更新统计信息，优化查询计划）
VACUUM ANALYZE my_table;
自动 VACUUM 配置（postgresql.conf）
autovacuum = on
autovacuum_vacuum_threshold = 50          # 基础阈值
autovacuum_vacuum_scale_factor = 0.1      # 当死元组 > 10% 行数时触发
autovacuum_vacuum_cost_delay = 2ms        # 控制 IO 影响
autovacuum_max_workers = 3
警告

事务 ID 回卷（Wraparound）：PostgreSQL 使用 32 位事务 ID，约 21 亿个。autovacuum_freeze_max_age 控制冻结阈值。如果 autovacuum 跟不上，会触发强制 VACUUM 导致数据库只读！监控 age(datfrozenxid) 指标。

WAL 管理​

WAL 配置
wal_level = replica              # 支持复制和 PITR
max_wal_size = 2GB               # WAL 总大小上限
min_wal_size = 80MB
archive_mode = on                # 归档模式
archive_command = 'cp %p /archive/%f'
# 检查 WAL 堆积
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / 1024 / 1024 AS wal_mb;

# 检查复制延迟
SELECT client_addr, state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

备份与恢复​

pg_basebackup 物理备份
pg_basebackup -h 10.0.1.10 -U repl -D /backup/base \
    -Fp -Xs -P -R

# -Fp: plain 格式
# -Xs: 流式传输 WAL
# -P: 显示进度
# -R: 自动生成 standby.signal 和复制配置
逻辑备份
# 备份单库
pg_dump -h localhost -U postgres mydb > mydb.sql

# 备份全部
pg_dumpall -h localhost -U postgres > all.sql

# 恢复
psql -h localhost -U postgres mydb < mydb.sql

常用扩展​

扩展说明
pg_stat_statementsSQL 性能统计
pgvector向量搜索
PostGIS地理信息
pg_cron定时任务
pg_repack在线表重组（替代 VACUUM FULL）
-- 启用 pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- 查看 Top 10 慢查询
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

常见面试问题​

Q1: VACUUM 和 VACUUM FULL 的区别？​

答案：

维度VACUUMVACUUM FULL
锁不阻塞读写排他锁，阻塞所有操作
空间标记可复用，不归还 OS重写表，归还 OS 空间
耗时快慢（需要双倍磁盘空间）
使用场景日常维护大量删除后回收空间

生产环境优先用 pg_repack 替代 VACUUM FULL，实现在线表重组。

相关链接​