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（需重启的配置）

最新问答