Debian上PostgreSQL故障如何排查

Debian上PostgreSQL故障排查清单

一 快速定位与通用检查

• 查看服务状态与版本
  • 执行：sudo systemctl status postgresql 或 sudo systemctl status postgresql@<version>-main
  • 若未运行：sudo systemctl start postgresql，必要时 sudo systemctl restart postgresql
• 查看日志
  • Debian 常见路径：/var/log/postgresql/postgresql-<version>-main.log
  • 也可用系统日志：sudo journalctl -u postgresql -xe 或 tail -f /var/log/syslog
• 确认端口与监听
  • 本地套接字：/var/run/postgresql/.s.PGSQL.5432
  • 网络监听：ss -lntp | grep 5432 或 netstat -lntp | grep 5432
• 防火墙与网络连通
  • UFW：sudo ufw allow 5432/tcp
  • firewalld：sudo firewall-cmd --add-port=5432/tcp --permanent && sudo firewall-cmd --reload
  • 连通性：nc -vz <host> 5432 或 psql -h <host> -p 5432 -U <user> -d <db> -c "SELECT 1"
• 配置文件与权限
  • 主配置：/etc/postgresql/<version>/main/postgresql.conf
  • 客户端认证：/etc/postgresql/<version>/main/pg_hba.conf
  • 数据目录权限：应为 0700，属主 postgres:postgres
• 资源与表空间
  • 资源：free -h、df -h、top/htop
  • 表空间：SELECT * FROM pg_tablespaces;
• 快速连接测试
  • 本地：sudo -u postgres psql -c "SELECT version();"
  • 远程：psql -h <host> -p <port> -U <user> -d <db>

二 常见故障场景与处理

• 服务无法启动
  • 查日志定位 FATAL/ERROR；核对 postgresql.conf 语法与路径
  • 常见原因：无效的 locale（如 lc_messages 设置不存在）；修复：sudo dpkg-reconfigure locales 启用所需 locale，再重启
  • 数据目录权限错误：设为 0700 且属主 postgres:postgres
• 无法连接
  • 服务未起、端口未放行、pg_hba.conf 规则不匹配、监听地址错误（如仅 127.0.0.1）
  • 逐步验证：服务状态 → 端口监听 → 防火墙 → pg_hba.conf → 客户端连接串
• 磁盘空间耗尽
  • 常见元凶：WAL 堆积或复制槽未回收
  • 处理：检查归档是否正常；清理过期 WAL（如 pg_archivecleanup）；核查复制槽 SELECT * FROM pg_replication_slots; 并删除废弃槽 SELECT pg_drop_replication_slot('slot_name');
• 查询变慢
  • 使用 EXPLAIN (ANALYZE, BUFFERS) 找瓶颈；按需创建索引、避免 SELECT *、优化 JOIN/子查询
  • 启用 pg_stat_statements 定位最耗时语句：shared_preload_libraries='pg_stat_statements'（需重启），查询 SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
• 复制与高可用异常
  • 查复制状态：SELECT * FROM pg_stat_replication;，关注 state、sent_lsn、write_lsn、flush_lsn、replay_lsn 的延迟与卡点

三 日志与性能分析工具

• 内置视图
  • pg_stat_activity：当前连接、查询、等待事件
  • pg_stat_database：提交/回滚、事务、读写统计
  • pg_stat_statements：SQL 执行次数、总/平均耗时、行数
• 日志分析
  • 建议启用：在 postgresql.conf 中设置
    • log_destination = 'csvlog'
    • logging_collector = on
    • log_directory = 'pg_log'
    • log_filename = 'postgresql-%Y-%m-%d.log'
    • log_checkpoints = on
    • log_connections = on
    • log_disconnections = on
    • log_lock_waits = on
    • log_min_duration_statement = 1000（记录超过 1 秒的慢查询）
  • 使用 pgBadger 生成 HTML 报告：pgbadger /var/log/postgresql/*.log -o report.html，可配合定时任务做周报/日报

四 应急与恢复建议

• 配置回滚：变更前备份 postgresql.conf、pg_hba.conf 与 pg_ident.conf
• 紧急止血
  • 终止阻塞/异常会话：SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE ...;
  • 只读应急：ALTER SYSTEM SET default_transaction_read_only = on; SELECT pg_reload_conf();
• 一致性检查与修复
  • 物理一致性：pg_checksums -c -D /var/lib/postgresql/<version>/main（若启用校验和）
  • 对象级检查：社区工具如 pg_check 可用于物理一致性检查（使用需谨慎，优先在测试环境验证）
• 备份与恢复
  • 逻辑备份：pg_dump -U <user> -h <host> -p <port> -F c -b -v -f backup.dump <db>
  • 时间点恢复（PITR）：基于基础备份 + WAL 归档；按时间点 recovery_target_time 或 recovery_target_lsn 配置 recovery.conf/postgresql.auto.conf 后启动
• 变更控制
  • 任何参数修改后执行 SELECT pg_reload_conf();（无需重启的配置），或 sudo systemctl restart postgresql（需重启的配置）