Debian下PostgreSQL磁盘空间如何管理

Debian下PostgreSQL磁盘空间管理

一 快速定位占用来源

• 查看整体磁盘与挂载点：使用df -h确认数据盘、WAL盘、归档盘使用率；接近满时数据库可能出现异常或宕机风险。
• 定位数据库数据目录：在 psql 中执行SHOW data_directory;，常见路径为**/var/lib/postgresql/<版本>/main**。
• 识别大对象与膨胀：
  • 查询表与索引大小：
    • 总大小：SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||‘.’||tablename)) FROM pg_tables ORDER BY pg_total_relation_size(schemaname||‘.’||tablename) DESC;
    • 仅数据/索引：pg_relation_size / pg_indexes_size。
  • 检查 TOAST：宽列会溢出到TOAST表，必要时通过 pg_class 与 pg_index 关联分析。
  • 定位磁盘文件路径：SELECT pg_relation_filepath(‘schema.tbl’);
• 检查WAL与归档：
  • 是否开启归档：SHOW archive_mode;
  • 查看复制槽（滞留会阻止WAL回收）：SELECT slot_name, plugin, slot_type, active, restart_lsn FROM pg_replication_slots;
  • 手工切WAL并验证归档：SELECT pg_switch_wal();
• 检查日志占用：确认 PostgreSQL 日志目录（常见在**/var/log/postgresql/**或通过 log_directory 配置），按需压缩/轮转旧日志。

二 常见空间增长源与对应处理

• WAL 日志堆积：多由归档失败、复制槽未释放或检查点配置不当引起。
  • 处理步骤：
    • 确保归档命令成功（网络、权限、磁盘空间充足），必要时先修复归档链路再清理。
    • 释放或删除无用复制槽：SELECT pg_drop_replication_slot(‘slot_name’);
    • 合理设置检查点参数以平衡恢复与WAL量：例如max_wal_size = 20GB、min_wal_size = 5GB、checkpoint_timeout = 30min、wal_compression = on（需结合负载与恢复目标调优）。
• 表膨胀（更新/删除后未有效回收）：
  • 常规维护：执行VACUUM（并发、回收供表重用，不返回空间给OS）；统计信息更新用ANALYZE。
  • 回收空间给OS：执行VACUUM FULL（需ACCESS EXCLUSIVE锁，耗时且需额外临时空间）。
  • 在线重组表：使用pg_repack，无需排他锁，适合大表在线收缩，释放空间给OS。
• 归档WAL未清理：启用归档后需定期清理过期归档，避免无限增长。
• 日志文件过大：配置合理的日志轮转与保留策略，压缩归档旧日志，避免与数据盘争用。

三 安全释放空间的步骤

• 紧急处置（磁盘将满或已影响写入）：
  • 暂停写入或切到维护窗口；优先清理可快速释放的非数据库文件（如旧备份、临时文件、无关日志）。
  • 若 WAL 盘满：先修复归档或临时移走部分 WAL（确保有备份与恢复路径），再释放空间。
• 清理WAL：
  • 确认archive_mode=on且归档命令稳定；
  • 使用**pg_switch_wal()**触发切换；
  • 用pg_archivecleanup清理已不再需要的WAL段（基于恢复点/时间线或全备标签）。
• 清理复制槽：删除不再使用的复制槽，防止 WAL 无法被回收。
• 回收表空间：
  • 常规 VACUUM + ANALYZE；
  • 需要归还空间给OS时，选择VACUUM FULL（停机窗口）或pg_repack（在线）。
• 清理系统/数据库日志：压缩并归档旧日志，释放**/var/log/postgresql/**等目录空间。

四 长期治理与容量规划

• 存储架构：将数据目录、pg_wal、归档目录分别挂载到不同磁盘/分区，降低单盘风险；新增磁盘后创建挂载点并配置**/etc/fstab自动挂载，权限设为postgres:postgres**。
• 表空间管理：使用CREATE TABLESPACE将数据/索引分布到不同磁盘，缓解单盘瓶颈。
• 监控与告警：对df -h与关键指标（WAL位置、复制槽、表膨胀）设置阈值告警，提前处置。
• 备份与保留策略：定期pg_basebackup，保留策略如“最近1份全备 + 其后所有WAL”，并编写脚本调用pg_archivecleanup清理过期归档，配合crontab每日执行。
• 参数优化：结合业务与恢复目标调整max_wal_size、min_wal_size、checkpoint_timeout、wal_compression等，减少WAL与检查点带来的空间与I/O压力。

五 常用命令清单

• 查看与切换WAL：
  • SHOW data_directory;
  • SHOW archive_mode;
  • SELECT pg_switch_wal();
• 复制槽：
  • SELECT slot_name, plugin, slot_type, active, restart_lsn FROM pg_replication_slots;
  • SELECT pg_drop_replication_slot(‘slot_name’);
• 清理WAL：pg_archivecleanup /pgdata/pgwal 0000000100000032000000A1
• 空间统计：
  • SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||‘.’||tablename)) FROM pg_tables ORDER BY pg_total_relation_size DESC;
  • SELECT pg_relation_filepath(‘schema.tbl’);
• 维护与重组：
  • VACUUM ANALYZE;
  • VACUUM FULL;
  • 创建扩展：CREATE EXTENSION pg_repack;（服务端）
  • 客户端执行：pg_repack -h host -p port -U user -d db;