Debian 上定位与修复 PostgreSQL 故障的实用流程
一 快速定位路径
sudo systemctl status postgresqlsudo journalctl -u postgresql -xe、tail -f /var/log/syslog、dmesg/var/log/postgresql/,常用命令:tail -f /var/log/postgresql/postgresql-<version>-main.log/etc/postgresql/<version>/main/postgresql.conf/etc/postgresql/<version>/main/pg_hba.confSELECT * FROM pg_stat_activity;EXPLAIN SELECT ...;pg_stat_statements 后查询 pg_stat_statements;top/htop/vmstatSELECT * FROM pg_tablespaces;pg_dump / pg_restore二 常见故障与处理要点
sudo systemctl start postgresqlpostgresql.conf 设置 listen_addresses = '*'pg_hba.conf 增加条目,例如 host all all 0.0.0.0/0 md5sudo ufw allow 5432/tcppsql -h <host> -p 5432 -U <user> -d <db>EXPLAIN (ANALYZE, BUFFERS) SELECT ...;CREATE INDEX ...;;ANALYZE;shared_preload_libraries='pg_stat_statements',查询 pg_stat_statements 找 Top SQLSELECT * FROM pg_replication_slots;SELECT pg_drop_replication_slot('slot_name');pg_archivecleanup /pgdata/pgwal <WAL文件名>max_wal_size=20GB、min_wal_size=5GB、checkpoint_timeout=30min、wal_compression=onSELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE state = 'active';SELECT pg_terminate_backend(<pid>);ROLLBACK 或 COMMIT 清理会话状态openssl s_client -connect <host>:<port>psql -h <host> -p <port> -U <user> -d <db> --ssltop/htop/vmstat,结合 pg_stat_activity 判断是否为连接风暴或单条查询占用过高三 关键配置与维护建议
postgresql.conf 加入:shared_preload_libraries = 'pg_stat_statements'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;listen_addresses 与 pg_hba.conf 网段/方法,变更后执行 sudo systemctl restart postgresqlmax_wal_size、checkpoint_timeout,开启 wal_compression 减少 WAL 体积VACUUM 与 ANALYZE,保持统计信息与空间回收pg_dump -U <user> -h <host> -p <port> -F c -b -v -f backup.dump <db>四 高效工具与命令清单
tail -f /var/log/postgresql/postgresql-<version>-main.logpgBadger 生成 HTML 报告(慢查询、错误、调用统计)psql、pg_toppg_replication_slots、pg_archivecleanup