凌晨两点被电话叫醒数据库连接数飙满业务侧疯狂报错主库CPU 100%慢查询像雪崩一样滚下来——这种场景做过PostgreSQL运维的人多少都经历过。事后复盘你会发现绝大多数“事故”都不是突然发生的而是几个月甚至半年的小隐患累积出来的死元组没来得及清理、索引冗余拖慢了写入、备份脚本半年没验证过恢复、版本老到官方EOL之后还在硬扛。数据库和人一样不体检、不锻炼、不控制饮食迟早进急诊室。这篇东西我不打算讲安装教程也不讲具体SQL优化技巧那些网上随手一搜就是一大把。我想聊的是PostgreSQL日常运维里真正绕不开的十项维护实践覆盖监控、VACUUM、索引、备份恢复、版本升级、连接管理这几个高频出事的环节。适合正在接手PostgreSQL实例的DBA、后端开发兼运维的“全栈杂工”、以及那些数据库还没出问题但心里隐隐发虚的团队。每一条都是我在生产环境里实际踩过坑、验证过、最后沉淀下来的做法有命令、有参数、有原理也有踩坑记录。1. 日常巡检三板斧监控、日志与告警维护数据库的第一步不是调优而是先建立能看见问题的眼睛。很多PostgreSQL实例出事的前48小时其实在监控指标和日志里已经有明显信号了只是没人看或者看了不知道意味着什么。1.1 这几个指标必须在监控面板上不是所有指标都值得天天盯优先级最高的是下面这四个连接数使用率max_connections默认100很多团队直接改成2000甚至5000但没意识到PostgreSQL的每一个连接背后都是一个独立的进程内存开销是实打实的。连接数打满意味着应用侧所有新请求全部排队这是数据库“假死”的最常见原因。建议监控连接使用率超过80%就要预警。慢查询数量与耗时打开log_min_duration_statement把超过阈值比如500ms的SQL全部记录下来。慢查询不是定时炸弹而是已经埋好的雷一旦并发上来每条慢查询都会变成压垮CPU的最后一根稻草。锁等待pg_locks和pg_stat_activity里wait_event字段如果长期出现lock相关等待说明业务侧存在锁竞争。锁等待是数据库“看起来没死但什么都不动”的典型元凶。磁盘空间与IO延迟磁盘满会让PostgreSQL直接进入只读或崩溃状态这没什么好商量的。WAL日志、临时文件、慢查询排序文件都可能瞬间吃掉几个GB。IO延迟飙高则说明底层存储已经在硬扛了迟早出问题。1.2 日志配置别用默认值PostgreSQL默认日志配置在排查问题时基本等于没有。我见过太多实例跑了一年log_statement还是nonelog_min_duration_statement还是-1出事的时候连个现场记录都找不到。推荐至少这样配置# postgresql.conf logging_collector on log_directory log log_filename postgresql-%Y-%m-%d.log log_min_duration_statement 500 log_checkpoints on log_connections on log_disconnections on log_lock_waits on deadlock_timeout 1slog_lock_waits配合deadlock_timeout是我特别推荐的组合——一旦有会话等锁超过1秒日志就会记录下来锁竞争问题从此有迹可循。log_checkpoints也很重要如果发现checkpoint过于频繁说明max_wal_size设置太小WAL频繁刷盘IO压力陡增。1.3 告警别设成“事后通知”告警的价值在于提前干预而不是事故播报。我常用的阈值是连接使用率80%、CPU持续5分钟超过80%、磁盘剩余空间低于20%、慢查询数量15分钟内超过50条、复制延迟超过30秒。达到这些阈值就该触发告警而不是等到业务已经报障了才由客服转告你。注意监控工具不是越重越好。中小团队用pg_stat_statements加Prometheus Grafana就足够覆盖绝大多数场景不必一上来就整套商业化监控全家桶。先把基础指标看住比什么都强。2. 躲不开的VACUUM与MVCCPostgreSQL和MySQL最大的区别之一就是MVCC的实现方式。PostgreSQL更新一条数据并不会直接改原行而是插入新版本、标记旧版本为“死元组”。这些死元组不清除表就会膨胀查询性能断崖式下跌。这就是为什么VACUUM在PostgreSQL维护里地位极高。2.1 先搞清楚为什么会有死元组假设一张用户表有100万行业务每秒更新100行。每次更新都会产生100个新行版本和100个死元组。如果没有任何清理机制一个星期后这张表里就有6000多万个死元组。查询时PostgreSQL需要跳过这些死元组索引扫描效率直线下降表占用的磁盘空间也不断膨胀。VACUUM就是回收这些死元组空间、更新统计信息的机制。Autovacuum默认是开启的但它只是“保底”生产环境下必须主动监控和调优不能完全指望默认参数。2.2 autovacuum参数怎么调才靠谱先看一组我从生产环境里总结的常用调参方案# postgresql.conf autovacuum on autovacuum_max_workers 4 autovacuum_naptime 30s autovacuum_vacuum_threshold 5000 autovacuum_vacuum_scale_factor 0.1 autovacuum_vacuum_cost_delay 10ms autovacuum_vacuum_cost_limit 1000scale_factor默认是0.2也就是表20%的内容是死元组时才触发autovacuum。对中小表来说这个比例太大了比如一张10万行的表要积累2万死元组才触发清理性能影响已经很明显。调到0.1配合threshold5000意思是死元组超过5000 表行数*0.1时触发清理。autovacuum_vacuum_cost_limit控制VACUUM的IO消耗上限默认是200太小容易导致VACUUM“永远清不完”。我会在业务低谷期给到1000高峰期用默认值也可以。每个表可以单独设置这些参数不必全局一刀切ALTER TABLE orders SET (autovacuum_vacuum_scale_factor 0.05, autovacuum_vacuum_threshold 1000); ALTER TABLE audit_log SET (autovacuum_enabled false);2.3 膨胀检测与处理手段最直观的膨胀检测SQL长这样SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100 * n_dead_tup / nullif(n_live_tup n_dead_tup, 0), 2) AS dead_pct FROM pg_stat_user_tables WHERE n_live_tup 10000 ORDER BY dead_pct DESC LIMIT 20;dead_pct超过30%就需要立刻手动VACUUM超过50%且表很大考虑VACUUM FULL或者用pg_repack重建表。VACUUM FULL会锁表生产环境要挑维护窗口执行。pg_repack是更好的选择它可以在不长时间锁表的情况下重建表消除膨胀代价是需要额外的磁盘空间和一点配置成本。我在一个每天千万级写入的流水表上用过pg_repack膨胀从60%降到5%以下查询性能恢复了近3倍。经验大表VACUUM要避开业务高峰期。PostgreSQL的VACUUM进程虽然设计上不太影响读写但在IO已经饱和的实例上它依然会挤占带宽。我的习惯是凌晨3点到5点跑自动化VACUUM检查脚本发现问题自动调高cost limit来加速清理。3. 索引也要“体检”冗余、失效与重建索引是数据库加速查询的利器但用不好也会成为写入的累赘。生产环境跑半年索引冗余是普遍现象很多团队从不清理直到写入变得越来越慢才反应过来。3.1 冗余索引和重复索引的识别方法重复索引是完全相同的字段组合比如(user_id, created_at)建了两遍——这基本是开发过程中没人管、随手建的。冗余索引是字段顺序不同但覆盖相同查询的索引比如(user_id, created_at)和(created_at, user_id)虽然排序上服务不同查询但在实际业务里往往只有一个有用。用这条SQL可以查出重复索引SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname public ORDER BY tablename, indexdef;更精细的做法是查询pg_stat_user_indexes看哪些索引长期idx_scan为0SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE idx_scan 0 AND schemaname NOT IN (pg_catalog, pg_toast) ORDER BY relname;连续一个月idx_scan都是0的索引基本可以判定为“僵尸索引”但删除之前要在灰度环境验证确认确实没有查询依赖它。比如唯一约束的索引就不能随便删哪怕它的scan是0因为唯一性本身还要靠它保证。3.2 重建索引的两种常见方式索引用得久了尤其是频繁更新、删除的表索引页会变得稀疏扫描效率下降。这时候可以选择重建。常规方式是REINDEX INDEX idx_orders_created_at;这个命令会重建索引并释放膨胀空间但会在执行期间锁表。大表重建索引我推荐用CONCURRENTLY参数REINDEX INDEX CONCURRENTLY idx_orders_created_at;它会以不阻塞读写的方式完成重建代价是耗时更长、消耗更多资源且执行过程中如果出错会留下无效索引需要手动清理。我的经验是生产环境超过100万行的表重建索引一律用CONCURRENTLY宁可多等20分钟也不要让业务停顿50秒。3.3 索引失效最隐蔽的性能杀手PostgreSQL里没有“索引失效”这个说法但有一个等价的现象统计信息不准导致优化器放弃走索引。比如一张表的数据分布发生了剧烈变化而ANALYZE还没跑优化器基于过期统计信息做出的执行计划可能是全表扫描。所以定期ANALYZE和VACUUM不能分开谈ANALYZE更新统计信息VACUUM清理死元组并顺便触发统计信息更新。手动执行可以这样VACUUM (ANALYZE) table_name;4. 备份与恢复演练平时做足功课关键时刻救命备份是数据库维护里最枯燥但最不能省的一项。说实话很多团队的备份脚本是DBA入职第一天写的之后三年没人碰过直到数据库数据被误删才发现备份文件已经损坏、恢复流程写错了目录、或者备份只覆盖了数据没覆盖WAL。4.1 逻辑备份与物理备份怎么选类型工具优点缺点适合场景逻辑备份pg_dump / pg_dumpall跨版本恢复灵活可单表恢复恢复速度慢停机时间长中小库、迁移、单表误删恢复物理备份pg_basebackup恢复速度快粒度细支持PITR必须同版本同平台占空间大大规模、生产环境、容灾我生产上用的是物理备份为主、逻辑备份为辅的双轨策略。物理备份解决“整个库能不能回来”的问题逻辑备份解决“某张表被误删了能不能快速捞出来”的问题。两个都有事故处理时才不用手忙脚乱。4.2 用WAL归档实现任意时间点恢复要支持PITRPoint-in-Time Recovery必须开启WAL归档# postgresql.conf wal_level replica archive_mode on archive_command test ! -f /backup/wal/%f cp %p /backup/wal/%farchive_command的工作是把每个WAL段在切换后复制到归档目录。test ! -f防止重复归档文件被覆盖这个细节很关键——WAL段可能因为checkpoint被重用文件名可能重复如果直接cp会覆盖旧文件导致恢复链断裂。配合pg_basebackup做基础备份pg_basebackup -h 127.0.0.1 -D /backup/base -U backup_user -P -X stream恢复时把基础备份拷回数据目录在postgresql.conf里配置restore_command然后启动数据库进入恢复模式PostgreSQL会自动从备份点重放WAL恢复到你要的时间点restore_command cp /backup/wal/%f %p4.3 恢复演练一定要真的恢复一次定期做恢复演练的意义用一句话就能说透没验证过的备份等于没有备份。我见过一个团队备份每天跑但从没恢复过某次磁盘故障后恢复数据发现备份目录硬链接失效、archive_command报错半年了、监控居然没告警。我的建议是每个季度至少做一次全量恢复演练目标不是“确认备份文件存在”而是“把备份完整恢复到一台干净的机器上并跑通业务探活SQL”。演练中发现的问题要像生产事故一样记录和跟进这比任何监控工具都可靠。5. 版本升级与补丁别拿老版本硬扛很多团队的安全事故不是黑客打进来的而是PostgreSQL版本太老已知的严重BUG和安全漏洞没人修。社区版PostgreSQL每个大版本的生命周期是5年EOL之后不再有安全补丁。我见过还在跑9.6的生产实例那已经是好几年前EOL的版本了从功能到性能都落后一大截。5.1 版本选择策略别追新但也别停在远古如果你是新建项目当前推荐使用PostgreSQL 16或17这两个版本在并行查询、Vacuum性能、逻辑复制等方面都有明显改进。如果是存量实例建议先看当前版本是否还在社区支持期内不在就尽快规划升级。升级不要跨大版本太多步比如9.6到16直接一步跨风险极高。稳妥路径是9.6 → 11 → 13 → 16每个大版本都走一遍完整的测试验证。虽然步骤多但每一步的风险都可控回滚路径也清晰。5.2 常用升级方式对比逻辑升级pg_dump导出再导入到新版本实例。优点是简单通用缺点是大库导出导入时间很长业务停机窗口要足够。适合中小库。物理升级pg_upgrade工具可以直接复用数据文件速度远快于逻辑方式。支持--link模式用硬链接避免全量拷贝数据文件几乎可以在一分钟内完成升级。前提是数据目录和二进制目录在同一台机器上且新旧版本路径都可用。我升级一个800GB的生产库用pg_upgrade --link加短暂停写总共只花了十几分钟业务感知到的停机时间不到5分钟。这是目前大库升级最推荐的方式。5.3 Docker部署PostgreSQL的维护注意点热词里好多人搜“docker安装postgresql”确实容器化部署已经很普遍了。但容器环境有两个维护上的坑得提醒第一数据卷必须挂在宿主机或用命名卷容器重建才不会丢数据。没有持久化卷的容器docker rm一次数据就全没了这不是危言耸听。第二容器内部日志默认走stdoutlog_min_duration_statement等参数要在docker run时通过-c传参或者挂载自定义的postgresql.conf。很多人容器跑起来就不管日志了出了事才发现容器日志已经滚动覆盖了。docker run -d \ --name postgres \ -e POSTGRES_PASSWORDstrong_password \ -p 5432:5432 \ -v /data/postgres:/var/lib/postgresql/data \ -v /etc/postgresql.conf:/etc/postgresql/postgresql.conf \ postgres:16 \ -c config_file/etc/postgresql/postgresql.conf6. 连接数与连接池压垮数据库的最后一根稻草PostgreSQL的max_connections不是可以无限调大的。每个连接都是一个独立的操作系统进程按默认配置每个连接要消耗数MB内存。1000个连接就是好几个GB内存还要算上work_mem、排序缓存等额外开销。6.1 max_connections的真实成本max_connections调大后不活跃的连接也在占用内存和进程表项。更关键的是连接数越多PostgreSQL内部的锁竞争越严重整体吞吐量反而下降。我在一台64GB内存的机器上做过测试max_connections从100调到1000TPS不升反降从12000掉到8000左右。原因在于高连接数下上下文切换和锁等待消耗了太多CPU。生产环境更靠谱的方案是用连接池层把应用连接数收敛比如PgBouncer。它的事务级连接池能让1000个应用连接复用10到20个真实数据库连接内存开销和锁竞争大幅降低。6.2 PgBouncer快速配置示例# pgbouncer.ini [databases] mydb host127.0.0.1 port5432 dbnamemydb [pgbouncer] listen_port 6432 listen_addr 0.0.0.0 auth_type md5 auth_file /etc/pgbouncer/userlist.txt pool_mode transaction max_client_conn 1000 default_pool_size 20pool_mode transaction是关键连接在事务结束后就归还到池子里。应用侧只需要把数据库连接地址从5432改成6432不需要改任何代码。6.3 连接泄漏的排查思路连接数缓慢上涨、最终打爆数据库这种问题多半是应用层连接池泄漏。排查时可以看pg_stat_activitySELECT usename, client_addr, state, count(*) FROM pg_stat_activity GROUP BY usename, client_addr, state ORDER BY count(*) DESC;state idle in transaction数量偏高说明应用开启事务后没有正确提交或回滚。这类连接是连接数上涨的主要来源。再配合语句级追踪能看到是哪个SQL的事务没关就能追溯到具体代码位置。7. 常见问题排查与避坑实录这里整理一些我在生产环境里反复遇到、且网上文档很少写透的问题做成速查表症状可能原因快速定位与处理数据库突然只读磁盘满或达到max_connectionsdf -h查磁盘清理WAL或临时文件查pg_stat_activity活跃连接数查询越来越慢但SQL没变表膨胀、索引膨胀、统计信息过期看pg_stat_user_tables死元组比例VACUUM后重建索引大量连接处于idle in transaction应用事务未关闭查pg_stat_activity定位应用代码并修复复制延迟持续增长主库WAL生成太快或备库IO不足查pg_stat_replication的write_lag必要时调大max_wal_senders排查备库磁盘checkpoint频繁触发max_wal_size太小查看pg_stat_bgwriter的checkpoint_timed统计适当调大max_wal_size慢查询日志没输出log_min_duration_statement为-1确认参数已生效并检查log_statement是否覆盖到了需要的SQL级别还有一个很隐蔽的坑work_mem设置过大。很多人以为把work_mem调到256MB就能让排序更快但在高并发下100个并发排序就会吃掉25GB内存直接OOM。正确做法是保持默认或适度调大同时在语句级别按需设置SET work_mem 64MB;8. 写在最后的维护习惯建议做PostgreSQL维护这几年我最大的体会是数据库出问题九成以上不是单一原因而是多个小问题累积到临界点后的集中爆发。磁盘告警没人看、死元组比例高没人理、日志配置太简陋查不到现场这些单独看都不是致命伤但叠在一起足以让一个健康的实例在一夜之间崩掉。所以我现在的习惯是三层护盾监控层指标告警、预防层VACUUM索引重建备份恢复演练、升级层版本节奏和补丁管理。每周花半小时看一眼关键指标每月做一次死元组和索引冗余检查每季度做一次恢复演练。这套流程不复杂但坚持下来你会发现那些让你半夜爬起来处理的事故绝大多数都不会再发生。数据库维护这事儿说到底就是六个字把日常做好。