PostgreSQL 经典故障解决方案

案例一：WAL 日志满导致数据库挂起解决方案

紧急处理步骤

立即释放磁盘空间：

bash

# 查找并删除WAL日志目录中的旧日志文件（谨慎操作）
# 只删除已归档的WAL日志文件
find /path/to/wal/directory -name "00000001*" -type f -mtime +7 -delete

# 清理PostgreSQL日志文件
rm -f /var/lib/pgsql/15/data/log/postgresql-*.log

# 检查磁盘空间
df -h

重启PostgreSQL服务：

bash

# 重启PostgreSQL服务
systemctl restart postgresql-15

# 检查服务状态
systemctl status postgresql-15

验证数据库连接：

bash

# 测试数据库连接
pg_isready -U postgres
psql -U postgres -c "SELECT 1;"

根本解决方案

配置WAL日志自动清理：

sql

-- 设置WAL日志保留时间（单位：分钟）
ALTER SYSTEM SET wal_keep_size = '1GB';

-- 设置归档命令
ALTER SYSTEM SET archive_command = 'cp %p /path/to/archive/%f';

-- 设置检查点配置
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET max_wal_size = '4GB';

-- 重新加载配置
SELECT pg_reload_conf();

优化备份策略：
- 使用pg_basebackup定期进行全量备份
- 配置pg_archivecleanup自动清理已归档的WAL日志
- 考虑使用pg_probackup或Barman等备份工具
配置监控和告警：
- 监控WAL日志目录的磁盘空间
- 配置磁盘空间告警阈值（建议80%）
- 监控归档命令的执行状态

案例二：主从复制延迟导致数据不一致解决方案

紧急处理步骤

检查复制状态：

sql

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

-- 从库查看复制状态
SELECT * FROM pg_stat_wal_receiver;
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

分析延迟原因：

bash

# 检查从库资源使用情况
top
iostat -x 1 5
vmstat 1 5

解决大事务延迟：

sql

-- 查找长时间运行的事务
SELECT pid, usename, datname, query_start, now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state <> 'idle' 
ORDER BY duration DESC;

-- 如果需要，终止长时间运行的事务
SELECT pg_terminate_backend(pid);

根本解决方案

优化主库配置：

sql

-- 主库配置
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET wal_keep_size = '2GB';
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET synchronous_commit = 'remote_write';

-- 从库配置
ALTER SYSTEM SET max_worker_processes = 8;
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET hot_standby = on;
ALTER SYSTEM SET hot_standby_feedback = on;

-- 重新加载配置
SELECT pg_reload_conf();

优化网络连接：
- 确保主从库之间的网络带宽充足
- 减少主从库之间的网络延迟
- 考虑使用专线连接
使用级联复制：
- 对于多个从库，使用级联复制架构
- 减少主库的复制压力
- 提高复制的可靠性
配置复制监控：
- 监控复制延迟
- 配置复制延迟告警阈值（建议>30秒）
- 监控复制进程状态

案例三：索引失效导致查询性能急剧下降解决方案

紧急处理步骤

分析慢查询：

sql

-- 查看当前慢查询
SELECT pid, usename, datname, query_start, now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state <> 'idle' 
ORDER BY duration DESC;

查看查询执行计划：

sql

-- 分析查询执行计划
EXPLAIN ANALYZE SELECT * FROM my_table WHERE condition;

重新收集统计信息：

sql

-- 重新收集表统计信息
ANALYZE VERBOSE my_table;

-- 重新收集数据库统计信息
ANALYZE VERBOSE;

重建索引：

sql

-- 重建单个索引
REINDEX INDEX my_index;

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

-- 重建整个数据库的所有索引
REINDEX DATABASE my_database;

根本解决方案

优化索引设计：
- 分析查询模式，创建合适的索引
- 避免创建过多冗余索引
- 考虑使用部分索引或表达式索引
- 定期审核和清理无用索引

配置自动统计信息收集：

sql

-- 设置自动统计信息收集参数
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_max_workers = 3;
ALTER SYSTEM SET autovacuum_naptime = '1min';
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;

-- 为特定表配置自动统计信息收集
ALTER TABLE my_table SET (autovacuum_analyze_scale_factor = 0.02);

监控索引使用情况：

sql

-- 查看索引使用情况
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes 
ORDER BY idx_scan ASC;

优化查询语句：
- 避免在索引列上使用函数
- 避免使用SELECT *
- 优化WHERE子句
- 合理使用JOIN条件

案例四：死锁导致事务长时间阻塞解决方案

紧急处理步骤

检测死锁：

sql

-- 查看当前锁状态
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_query,
       blocking_activity.query   AS blocking_query
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

终止死锁进程：

sql

-- 终止阻塞进程
SELECT pg_terminate_backend(blocking_pid);

根本解决方案

优化事务设计：
- 尽量缩短事务长度
- 避免在事务中执行耗时操作
- 确保事务以相同顺序访问资源
- 考虑使用乐观锁机制

配置锁超时：

sql

-- 设置锁等待超时（单位：毫秒）
ALTER SYSTEM SET lock_timeout = '30000';

-- 设置事务超时
ALTER SYSTEM SET statement_timeout = '60000';

-- 重新加载配置
SELECT pg_reload_conf();

监控死锁：
- 启用死锁日志
- 监控死锁发生频率
- 分析死锁原因

使用适当的事务隔离级别：

sql

-- 设置事务隔离级别
SET default_transaction_isolation = 'read committed';

案例五：权限配置错误导致数据泄露解决方案

紧急处理步骤

立即撤销不当权限：

sql

-- 撤销用户的不当权限
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM unauthorized_user;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM unauthorized_user;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM unauthorized_user;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM unauthorized_user;

审查所有用户权限：

sql

-- 查看所有用户的权限
SELECT grantee, privilege_type, table_name 
FROM information_schema.role_table_grants 
WHERE grantee NOT IN ('postgres', 'public');

修改默认权限：

sql

-- 修改默认权限
ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM public;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON SEQUENCES FROM public;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM public;

根本解决方案

实施最小权限原则：

sql

-- 创建角色
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password' NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- 授予最小必要权限
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES IN SCHEMA public TO readonly_user;

使用角色管理权限：

sql

-- 创建功能角色
CREATE ROLE db_reader;
CREATE ROLE db_writer;
CREATE ROLE db_admin;

-- 授予角色权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_reader;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_writer;
GRANT ALL PRIVILEGES ON DATABASE mydb TO db_admin;

-- 将角色授予用户
GRANT db_reader TO app_user;
GRANT db_writer TO app_admin;
GRANT db_admin TO postgres;

配置详细的权限审计：

sql

-- 启用审计日志
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';

-- 重新加载配置
SELECT pg_reload_conf();

定期权限审查：
- 建立定期权限审查机制
- 使用脚本自动化权限审查
- 及时撤销不再需要的权限

故障解决方案的最佳实践

1. 建立故障响应团队

明确团队成员的职责和分工
建立24/7故障响应机制
制定详细的故障响应流程

2. 准备故障工具箱

收集常用的故障诊断和修复工具
编写自动化脚本处理常见故障
建立故障知识库

3. 定期演练

定期进行故障演练
测试故障解决方案的有效性
优化故障响应流程

4. 持续改进

分析每次故障的根本原因
更新故障解决方案和文档
改进监控和告警机制

常见问题（FAQ）

Q1：如何快速定位故障根因？

A1：可以采取以下步骤：

查看PostgreSQL错误日志，寻找关键错误信息
检查系统资源使用情况（CPU、内存、磁盘、网络）
使用pg_stat_activity等系统视图查看数据库状态
分析慢查询日志
检查锁状态和事务状态

Q2：如何避免WAL日志满导致的数据库挂起？

A2：可以采取以下措施：

配置合理的WAL日志保留策略
确保归档命令执行成功
监控WAL日志目录的磁盘空间
配置磁盘空间告警
考虑使用自动扩展的存储解决方案

Q3：如何处理主从复制延迟？

A3：可以采取以下措施：

优化主库大事务，拆分为小事务
确保从库有足够的资源
优化主从库之间的网络连接
合理配置复制相关参数
使用级联复制架构

Q4：如何预防索引失效？

A4：可以采取以下措施：

定期更新表的统计信息
避免在索引列上使用函数
监控索引使用情况
定期重建或重新分析索引
优化索引设计

Q5：如何处理死锁？

A5：可以采取以下措施：

确保事务以相同顺序访问资源
尽量缩短事务长度
配置锁超时
使用适当的事务隔离级别
监控和分析死锁日志

Q6：如何加强数据库权限管理？

A6：可以采取以下措施：

遵循最小权限原则
使用角色管理权限
定期审查用户权限
配置详细的权限审计日志
实施双人授权机制

Q7：如何建立有效的故障响应机制？

A7：可以采取以下措施：

建立故障响应团队和流程
配置完善的监控和告警机制
准备故障工具箱
定期进行故障演练
持续改进故障响应流程

Q8：如何从故障中学习？

A8：可以采取以下措施：

记录详细的故障信息和解决方案
分析故障根本原因
更新故障知识库和文档
组织团队经验分享
改进系统和流程，预防类似故障再次发生

PostgreSQL 经典故障解决方案 ​

案例一：WAL 日志满导致数据库挂起解决方案 ​

紧急处理步骤 ​

根本解决方案 ​

案例二：主从复制延迟导致数据不一致解决方案 ​

紧急处理步骤 ​

根本解决方案 ​

案例三：索引失效导致查询性能急剧下降解决方案 ​

紧急处理步骤 ​

根本解决方案 ​

案例四：死锁导致事务长时间阻塞解决方案 ​

紧急处理步骤 ​

根本解决方案 ​

案例五：权限配置错误导致数据泄露解决方案 ​

紧急处理步骤 ​

根本解决方案 ​

故障解决方案的最佳实践 ​

1. 建立故障响应团队 ​

2. 准备故障工具箱 ​

3. 定期演练 ​

4. 持续改进 ​

常见问题（FAQ） ​

Q1：如何快速定位故障根因？ ​

Q2：如何避免WAL日志满导致的数据库挂起？ ​

Q3：如何处理主从复制延迟？ ​

Q4：如何预防索引失效？ ​

Q5：如何处理死锁？ ​

Q6：如何加强数据库权限管理？ ​

Q7：如何建立有效的故障响应机制？ ​

Q8：如何从故障中学习？ ​

PostgreSQL 经典故障解决方案

案例一：WAL 日志满导致数据库挂起解决方案

紧急处理步骤

根本解决方案

案例二：主从复制延迟导致数据不一致解决方案

紧急处理步骤

根本解决方案

案例三：索引失效导致查询性能急剧下降解决方案

紧急处理步骤

根本解决方案

案例四：死锁导致事务长时间阻塞解决方案

紧急处理步骤

根本解决方案

案例五：权限配置错误导致数据泄露解决方案

紧急处理步骤

根本解决方案

故障解决方案的最佳实践

1. 建立故障响应团队

2. 准备故障工具箱

3. 定期演练

4. 持续改进

常见问题（FAQ）

Q1：如何快速定位故障根因？

Q2：如何避免WAL日志满导致的数据库挂起？

Q3：如何处理主从复制延迟？

Q4：如何预防索引失效？

Q5：如何处理死锁？

Q6：如何加强数据库权限管理？

Q7：如何建立有效的故障响应机制？

Q8：如何从故障中学习？