Nominatim每日更新时Postgres数据库磁盘占用增长过快求助

一、pg_wal目录堆积处理 #

pg_wal里的预写日志（WAL）文件绝对不能手动删除，PostgreSQL会自动回收，堆积通常是配置或事务问题导致：

• 检查归档配置：如果不需要基于时间点的恢复（PITR），直接关闭归档：

  archive_mode = off
  

  若之前开启了归档但archive_command执行失败，修复命令或关闭归档，否则WAL会持续堆积。
• 调整WAL保留量：无流复制从库时，最小化保留的WAL数量：

  wal_keep_size = 64MB  # 对应4个16MB的WAL文件，旧版本用wal_keep_segments = 4
  

• 优化检查点触发：减少检查点间隔，让旧WAL更快被回收：

  max_wal_size = 1GB
  min_wal_size = 128MB
  checkpoint_timeout = 30min
  checkpoint_completion_target = 0.9
  

• 清理长事务：长事务会阻止WAL回收，执行以下查询找出并终止：

  SELECT pid, now() - xact_start AS duration, query 
  FROM pg_stat_activity 
  WHERE state = 'idle in transaction';
  

  终止用SELECT pg_terminate_backend(<pid>);（替换为对应进程ID）

二、base/16390目录增长处理 #

16390是数据库的OID，先查对应库：

SELECT oid, datname FROM pg_database WHERE oid = 16390;

这个目录的1GB文件是数据库数据文件，增长分两种情况：

1. 真实数据增量（OSM每日更新） #

如果是OSM diff导入带来的正常数据增长，只能通过：

• 确认更新脚本是否保留了不必要的历史版本，若不需要，修改脚本只保留最新数据。
• 定期清理不需要的OSM图层或历史数据。

2. 数据膨胀（死元组/空闲空间） #

OSM更新会产生大量死元组，若autovacuum清理不及时，会导致数据文件膨胀：

• 检查表膨胀情况：

  SELECT 
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    n_live_tup, n_dead_tup,
    round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) AS dead_tuple_pct
  FROM pg_stat_user_tables 
  JOIN pg_class ON pg_stat_user_tables.relid = pg_class.relid;
  

• 优化autovacuum配置，加快死元组清理：

  autovacuum = on
  autovacuum_vacuum_scale_factor = 0.01  # 表大小变化1%就触发vacuum
  autovacuum_analyze_scale_factor = 0.01
  autovacuum_vacuum_threshold = 10000     # 死元组超过1万也触发
  autovacuum_analyze_threshold = 5000
  

  针对OSM大表，还可以单独设置：

  ALTER TABLE <osm_large_table> SET (autovacuum_vacuum_scale_factor = 0.005);
  

• 收缩已膨胀的表：
  • 低峰期执行VACUUM FULL <table_name>;（会锁表，需暂停更新任务）
  • 无锁方案：安装pg_repack扩展后执行pg_repack -d <dbname> -t <table_name>

三、安全删除的文件范围 #

• 禁止手动删除：pg_wal、base下的任何数据文件，否则直接损坏数据库。
• 可安全清理：
  • 日志目录（默认pg_log或log_directory指定路径）下的旧日志文件，可手动删除或配置自动轮转。
  • pg_stat_tmp目录下的临时统计文件（PostgreSQL会自动管理，手动删不影响）。

四、最小化备份与日志的配置调整 #

1. 关闭不必要的备份功能 #

archive_mode = off          # 关闭WAL归档（无PITR需求时）
wal_level = minimal         # 最小WAL级别，只记录崩溃恢复所需内容

2. 缩减日志输出 #

log_min_messages = warning  # 只记录警告及以上级别日志
log_statement = none        # 不记录执行的SQL语句
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d      # 每天轮转日志
log_rotation_size = 100MB  # 文件超过100MB也轮转
log_truncate_on_rotation = on  # 同一天的日志覆盖旧文件

五、监控与验证 #

• 查看WAL生成速率：

  SELECT 
    wal_records, 
    pg_size_pretty(wal_bytes) AS wal_size,
    pg_size_pretty(wal_bytes / EXTRACT(EPOCH FROM now() - stat_reset)) AS wal_per_second
  FROM pg_stat_wal;
  

• 监控数据库大小变化：

  SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
  

• 检查autovacuum执行记录：

  SELECT tablename, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;
  

内容的提问来源于stack exchange，提问作者João Valente