PostgreSQL 常见故障模式

核心概念

PostgreSQL常见故障模式是指数据库在运行过程中经常出现的故障类型，主要包括：

连接故障：客户端无法连接到数据库
性能故障：数据库响应缓慢或查询执行时间过长
数据损坏：数据库文件或数据出现损坏
复制故障：主从复制出现问题
资源耗尽：数据库耗尽内存、磁盘空间或连接数
系统崩溃：数据库服务意外停止

连接故障

1. 无法连接到数据库

症状：客户端无法连接到PostgreSQL数据库，报错信息可能包括：

could not connect to server: Connection refused
FATAL: no pg_hba.conf entry for host
FATAL: password authentication failed for user

常见原因：

PostgreSQL服务未启动
监听地址或端口配置错误
防火墙阻止了连接
pg_hba.conf配置不正确
用户名或密码错误

排查方法：

bash

# 检查PostgreSQL服务状态
systemctl status postgresql-14

# 检查监听地址和端口
psql -c "SHOW listen_addresses;"
psql -c "SHOW port;"

# 检查防火墙规则
firewall-cmd --list-ports

# 检查pg_hba.conf配置
cat /var/lib/pgsql/14/data/pg_hba.conf

# 使用telnet测试端口连通性
telnet localhost 5432

# 检查连接日志
cat /var/lib/pgsql/14/data/log/postgresql-*.log | grep -i connect

解决方案：

bash

# 启动PostgreSQL服务
systemctl start postgresql-14

# 修改监听地址（允许所有IP访问）
psql -c "ALTER SYSTEM SET listen_addresses = '*';"

# 添加防火墙规则
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload

# 修改pg_hba.conf，允许特定IP访问
echo "host all all 192.168.1.0/24 md5" >> /var/lib/pgsql/14/data/pg_hba.conf

# 重置用户密码
psql -c "ALTER ROLE postgres WITH PASSWORD 'new_password';"

2. 连接数耗尽

症状：客户端无法连接到数据库，报错信息：

FATAL: sorry, too many clients already

常见原因：

max_connections参数设置过小
应用程序未正确关闭连接
长连接过多
连接池配置不合理

排查方法：

sql

-- 查看当前连接数和最大连接数
SELECT count(*) as current_connections, setting as max_connections 
FROM pg_stat_activity, pg_settings 
WHERE name = 'max_connections' GROUP BY setting;

-- 查看连接来源分布
SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY client_addr;

-- 查看连接状态分布
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- 查看长时间运行的连接
SELECT pid, usename, datname, state, query_start, now() - query_start as duration 
FROM pg_stat_activity 
WHERE now() - query_start > interval '5 minutes' 
ORDER BY duration DESC;

解决方案：

sql

-- 增加最大连接数
ALTER SYSTEM SET max_connections = '200';

-- 增加共享内存大小（需要重启）
ALTER SYSTEM SET shared_buffers = '4GB';

-- 终止空闲连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' 
AND now() - query_start > interval '10 minutes';

性能故障

1. 查询执行缓慢

症状：查询执行时间过长，影响应用程序响应速度

常见原因：

缺少合适的索引
统计信息过时
查询计划不合理
内存配置不足
磁盘I/O瓶颈

排查方法：

sql

-- 查看当前正在执行的查询
SELECT pid, usename, datname, query_start, now() - query_start as duration, query 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY duration DESC 
LIMIT 10;

-- 分析查询计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- 查看表的索引情况
\di+ orders

-- 查看统计信息状态
SELECT relname, last_autovacuum, last_autoanalyze 
FROM pg_stat_all_tables 
WHERE relname = 'orders';

-- 查看缓存命中率
SELECT 
  round(blks_hit * 100.0 / (blks_hit + blks_read), 2) as cache_hit_rate
FROM pg_stat_database 
WHERE datname = current_database();

解决方案：

sql

-- 创建缺失的索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 更新统计信息
ANALYZE orders;

-- 优化查询（添加LIMIT、优化JOIN条件等）
SELECT * FROM orders WHERE customer_id = 123 LIMIT 100;

-- 增加work_mem
ALTER SYSTEM SET work_mem = '64MB';

-- 增加effective_cache_size
ALTER SYSTEM SET effective_cache_size = '8GB';

2. 系统负载过高

症状：服务器CPU、内存或磁盘使用率过高

常见原因：

大量复杂查询同时执行
全表扫描过多
索引膨胀
自动vacuum配置不合理
磁盘I/O性能问题

排查方法：

bash

# 查看系统负载
uptime

# 查看CPU使用情况
top

# 查看内存使用情况
free -h

# 查看磁盘I/O情况
iostat -x 1

# 查看PostgreSQL进程
ps aux | grep postgres

sql

-- 查看当前活跃查询
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- 查看表的大小和索引使用情况
SELECT relname, 
       pg_size_pretty(pg_total_relation_size(relid)) as total_size, 
       idx_scan, seq_scan 
FROM pg_stat_user_tables 
ORDER BY pg_total_relation_size(relid) DESC 
LIMIT 10;

-- 查看自动vacuum状态
SELECT relname, autovacuum_count, last_autovacuum, n_dead_tup 
FROM pg_stat_user_tables 
ORDER BY n_dead_tup DESC 
LIMIT 10;

解决方案：

sql

-- 优化自动vacuum配置
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_max_workers = 4;
ALTER SYSTEM SET autovacuum_naptime = '1min';

-- 手动执行vacuum和analyze
VACUUM ANALYZE orders;

-- 重建膨胀的索引
REINDEX INDEX idx_orders_customer_id;

-- 优化查询，减少全表扫描
SELECT * FROM orders WHERE id > 1000 AND id < 2000;

数据损坏

1. 表数据损坏

症状：查询时出现数据损坏错误，如：

ERROR: invalid page in block 123 of relation base/12345/67890
FATAL: corrupted cluster: see documentation for details

常见原因：

硬件故障（磁盘损坏）
操作系统崩溃
数据库进程异常终止
错误的文件系统操作
病毒或恶意攻击

排查方法：

bash

# 使用pg_checksums检查数据完整性
pg_checksums -c -D /var/lib/pgsql/14/data

# 使用pg_resetwal修复WAL损坏（谨慎使用）
pgsql_resetwal -f /var/lib/pgsql/14/data

# 检查特定表的完整性
psql -c "SELECT count(*) FROM orders;"
psql -c "SELECT * FROM orders LIMIT 100;"

# 查看数据库日志中的错误信息
cat /var/lib/pgsql/14/data/log/postgresql-*.log | grep -i error

解决方案：

bash

# 从备份恢复数据
pg_restore -d mydb /backup/mydb.backup

# 使用pg_dump导出可恢复的数据
pg_dump -O -x -f recoverable_data.sql mydb

# 重建数据库并导入数据
createdb new_db
psql -d new_db -f recoverable_data.sql

# 修复特定表
pg_dump -t orders -f orders.sql mydb
psql -d mydb -c "TRUNCATE TABLE orders;"
psql -d mydb -f orders.sql

2. 索引损坏

症状：查询时出现索引相关错误，如：

ERROR: index "idx_orders_customer_id" contains unexpected zero page at block 456
ERROR: could not read block 789 in file "base/12345/67890": read only 0 of 8192 bytes

常见原因：

索引创建过程中系统崩溃
磁盘I/O错误
内存不足
硬件故障

排查方法：

sql

-- 检查索引是否有效
SELECT indexname, indisvalid FROM pg_indexes JOIN pg_index ON indexname::regclass = indexrelid WHERE tablename = 'orders';

-- 测试索引使用
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

解决方案：

sql

-- 重建损坏的索引
REINDEX INDEX idx_orders_customer_id;

-- 重建表的所有索引
REINDEX TABLE orders;

-- 重建数据库的所有索引
REINDEX DATABASE mydb;

复制故障

1. 主从复制延迟

症状：从库落后于主库，延迟时间不断增加

常见原因：

主库写入负载过高
从库硬件性能不足
网络带宽不足
从库配置不合理
长事务或大事务

排查方法：

sql

-- 在主库查看复制状态
SELECT * FROM pg_stat_replication;

-- 在从库查看复制延迟
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

-- 查看主库WAL生成速率
SELECT 
  extract(epoch FROM (now() - query_start)) AS duration,
  pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_delay_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_delay_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_delay_bytes
FROM pg_stat_replication;

-- 查看大事务
SELECT pid, usename, datname, query_start, now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY duration DESC 
LIMIT 5;

解决方案：

sql

-- 在从库增加资源配置
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- 优化主库大事务，拆分为小事务
-- 原大事务：
BEGIN;
-- 大量INSERT/UPDATE操作
COMMIT;

-- 优化为小事务：
-- 每次处理1000条记录
FOR i IN 1..100 LOOP
  BEGIN;
  -- 处理1000条记录
  COMMIT;
END LOOP;

-- 增加从库数量，分担读取压力
-- 使用pg_basebackup创建新的从库
pg_basebackup -D /var/lib/pgsql/14/data_new -h master_host -U replication -F p -P -v

2. 复制中断

症状：从库与主库断开连接，复制停止

常见原因：

主库或从库服务重启
网络中断
复制槽已满
权限问题
配置错误

排查方法：

bash

# 检查从库复制状态
psql -c "SELECT * FROM pg_stat_wal_receiver;"

# 检查从库recovery.conf或postgresql.auto.conf配置
cat /var/lib/pgsql/14/data/postgresql.auto.conf

# 检查复制槽状态
psql -c "SELECT * FROM pg_replication_slots;"

# 查看复制日志
cat /var/lib/pgsql/14/data/log/postgresql-*.log | grep -i replication

解决方案：

bash

# 重启从库复制
psql -c "SELECT pg_reload_conf();"

# 修复复制槽问题
psql -c "SELECT pg_drop_replication_slot('slot_name');"
psql -c "SELECT pg_create_logical_replication_slot('slot_name', 'pgoutput');"

# 重新配置复制
# 在主库创建复制用户
psql -c "CREATE ROLE replication WITH REPLICATION LOGIN PASSWORD 'replication_password';"

# 在从库配置恢复参数
echo "primary_conninfo = 'host=master_host port=5432 user=replication password=replication_password'" >> /var/lib/pgsql/14/data/postgresql.auto.conf
echo "primary_slot_name = 'slot_name'" >> /var/lib/pgsql/14/data/postgresql.auto.conf

常见问题（FAQ）

Q1：如何监控PostgreSQL故障？

A1：可以使用以下工具和方法监控PostgreSQL故障：

内置监控视图：
- pg_stat_activity：查看连接和查询状态
- pg_stat_database：查看数据库统计信息
- pg_stat_replication：查看复制状态
- pg_stat_bgwriter：查看后台写入器统计信息
第三方监控工具：
- Prometheus + Grafana：全面监控系统和数据库性能
- Zabbix：支持PostgreSQL监控模板
- Nagios/Icinga：监控服务状态和性能指标
- pgAdmin：图形化管理和监控工具
日志监控：
- 配置详细的日志记录
- 使用ELK Stack或Graylog分析日志
- 设置日志告警规则

Q2：如何预防PostgreSQL故障？

A2：可以采取以下措施预防PostgreSQL故障：

定期备份：
- 执行全量备份和增量备份
- 测试备份恢复
- 存储备份到异地
监控和告警：
- 监控系统资源使用情况
- 监控数据库性能指标
- 设置合理的告警阈值
- 定期查看告警日志
定期维护：
- 执行vacuum和analyze
- 检查和修复索引膨胀
- 更新统计信息
- 检查数据完整性
配置优化：
- 根据硬件调整参数
- 优化查询和索引
- 配置合理的连接池
- 启用合适的日志级别
安全措施：
- 定期更新PostgreSQL版本
- 应用安全补丁
- 配置防火墙和访问控制
- 使用SSL加密连接
- 定期更换密码

Q3：如何进行PostgreSQL故障演练？

A3：可以按照以下步骤进行故障演练：

制定演练计划：
- 确定演练目标和范围
- 选择演练故障类型
- 制定恢复步骤和时间限制
- 通知相关人员
准备演练环境：
- 创建测试环境
- 备份生产数据到测试环境
- 配置监控和日志
执行故障演练：
- 模拟故障场景（如服务崩溃、数据损坏、复制中断等）
- 按照恢复计划执行恢复操作
- 记录恢复时间和遇到的问题
评估演练结果：
- 分析恢复过程和时间
- 评估恢复计划的有效性
- 识别改进点
- 更新恢复计划
总结和改进：
- 编写演练报告
- 分享经验和教训
- 改进监控和告警
- 更新文档和流程

通过定期的故障演练，可以提高DBA团队的故障处理能力，确保在实际故障发生时能够快速、有效地恢复数据库服务。

PostgreSQL 常见故障模式 ​

核心概念 ​

连接故障 ​

1. 无法连接到数据库 ​

2. 连接数耗尽 ​

性能故障 ​

1. 查询执行缓慢 ​

2. 系统负载过高 ​

数据损坏 ​

1. 表数据损坏 ​

2. 索引损坏 ​

复制故障 ​

1. 主从复制延迟 ​

2. 复制中断 ​

常见问题（FAQ） ​

Q1：如何监控PostgreSQL故障？ ​

Q2：如何预防PostgreSQL故障？ ​

Q3：如何进行PostgreSQL故障演练？ ​

PostgreSQL 常见故障模式

核心概念

连接故障

1. 无法连接到数据库

2. 连接数耗尽

性能故障

1. 查询执行缓慢

2. 系统负载过高

数据损坏

1. 表数据损坏

2. 索引损坏

复制故障

1. 主从复制延迟

2. 复制中断

常见问题（FAQ）

Q1：如何监控PostgreSQL故障？

Q2：如何预防PostgreSQL故障？

Q3：如何进行PostgreSQL故障演练？