PostgreSQL 14.6运行一段时间后进入恢复模式问题求助

问题场景 #

Ubuntu 20系统运行PostgreSQL 14.6，搭配Ruby on Rails与pgbouncer。应用需至少250个连接池，后台任务有时会以300+线程持续运行数小时甚至一天。在此期间，数据库偶尔会进入恢复模式并终止所有连接，1-2秒后恢复正常，但会导致后台服务中断。

系统与已做配置 #

• 系统内存：48GB
• 已调整max_connections，当前shared_buffers=8192MB，kernel.shmmax=1342177280，并通过postgresqltuner优化过配置
• Rails后台任务以100+并发线程运行时，触发数据库进入恢复模式的问题

相关配置文件 #

pgbouncer.ini

# pgbouncer.ini
[databases]
app_production = host=localhost dbname=app_production port=5432

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 16432
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = appuser
max_client_conn = 1000
default_pool_size = 1000

database.yml

# database.yml
production:
      adapter: postgresql
      host: 127.0.0.1
      database: app_production
      username: appuser
      password: defaultpassword
      encoding: unicode
      pool: 1000
      port: 16432

PostgreSQL 配置（postgresqltuner优化后）

# OS Type: linux
# DB Type: web
# Total Memory (RAM): 48 GB
# CPUs num: 12
# Connections num: 1000
# Data Storage: hdd

max_connections = 1000
shared_buffers = 12GB
effective_cache_size = 36GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 3145kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 12
max_parallel_workers_per_gather = 4
max_parallel_workers = 12

解决建议 #

1. 定位恢复模式触发原因 #

数据库进入恢复模式通常和WAL日志耗尽、checkpoint压力过大、磁盘IO瓶颈或主从切换（若有集群）有关，先从日志排查：

• 查看PostgreSQL主日志（默认路径/var/log/postgresql/postgresql-14-main.log），搜索recovery关键词，定位具体触发原因（比如WAL写满、磁盘IO超时）
• 检查pgbouncer日志（pgbouncer.log），确认连接断开前后的错误信息，判断是主动终止还是被动断开

2. 重构PgBouncer连接池配置 #

当前default_pool_size=1000直接匹配PostgreSQL的max_connections，完全没用到PgBouncer的连接复用能力：

• 降低default_pool_size至合理值：PostgreSQL单实例最佳并发连接数通常为CPU核心数*2 + 有效磁盘数，你有12核CPU，建议设为30-50
• 保留max_client_conn=1000，让PgBouncer负责排队超出连接池的请求，避免PostgreSQL被过多连接打满
• Rails的database.yml中pool值不要超过PgBouncer的max_client_conn，建议设为200-300，让Rails内部做连接排队

3. 调整PostgreSQL内存与IO配置 #

• 修复kernel.shmmax: 当前值1.28GB远小于shared_buffers=12GB，需调整为至少12GB（12884901888），否则PostgreSQL无法分配足够共享内存，可能触发崩溃恢复
• 优化checkpoint: 当前max_wal_size=4GB对高并发任务偏小，建议调至8GB-16GB，保持checkpoint_completion_target=0.9缓解IO压力
• 调整work_mem: 当前3145kB过小，高并发下排序、哈希操作会用到临时磁盘表加剧IO压力。建议设为64MB（注意：需先降低连接数，比如连接池设为50的话，总内存占用50*64MB=3200MB，在48GB内存下完全可控）
• 磁盘IO优化: HDD存储IO性能是瓶颈，若可行将WAL日志目录迁移到SSD，或把effective_io_concurrency调至4（HDD合理值）

4. 控制Rails后台任务并发 #

• 不要直接跑300+线程的后台任务，用Sidekiq等队列工具限制并发数至100以内，结合PgBouncer连接池，避免瞬间打满数据库
• 拆分长时间运行的任务，比如将300线程任务拆分为多批次，每批50个，降低数据库持续压力

5. 监控关键指标 #

• 监控PostgreSQL的pg_stat_activity，查看连接数、等待事件（比如wait_event_type='IO'的占比）
• 用iostat监控磁盘IO利用率，若WAL写入时IO使用率超90%，说明磁盘IO是瓶颈
• 监控系统内存，确保有足够空闲内存，避免OOM导致PostgreSQL崩溃

内容的提问来源于stack exchange，提问作者Taha ali