写这篇博文的起因是我这两年接了不少PG数据库的“救火”需求。2025年了PostgreSQL在国内用得比前几年广得多很多团队从其他数据库迁过来功能上确实很顺手但一到运维环节就露怯服务半夜停了不知道先看什么磁盘报错不知道怎么快速恢复JSON字段明明用上了查询却慢得像全表扫描。这篇文章不打算讲高深原理我就把自己在实际运维里沉淀下来的处理链路、常用命令和踩坑教训整理出来按问题场景来写希望能给正在维护PG实例的同行一些能直接落地的东西。1. 服务突然停摆先别急着重启按这条链路排查PG服务意外宕掉是我遇到频率最高的一类故障尤其在业务高峰期。很多人的第一反应是执行pg_ctl restart或者重启容器我强烈建议先忍一下。未定位原因就重启短时间确实恢复了但没过几天大概率会再挂一次而且可能挂在同一个点上。我的习惯是严格按“日志、资源、恢复、加固”四步来。1.1 第一步日志里往往已经写明了答案PG的运行日志是排查停摆问题最直接的入口。不同安装方式日志位置不一样最常见的路径是$PGDATA/log这里$PGDATA一般对应/var/lib/postgresql/16/main或者/var/lib/pgsql/16/data。用Docker部署的实例日志通常直接打到标准输出可以通过docker logs查看。总体上记住一条先翻日志再动手。定位到日志文件之后我会用下面这条命令看最近200行tail -n 200 $PGDATA/log/postgresql-*.log常见几类日志特征可以对照排查日志关键字大概率对应的原因could not write block ... No space left on device磁盘满或inode耗尽server process was terminated by signal 9内存不足触发OOM killerPANIC: could not locate a valid checkpoint record数据文件损坏或磁盘故障terminating connection due to administrator command有人执行了重启或主动killFATAL: terminating connection due to conflict with recovery主从切换或恢复中的冲突out of shared memoryshared_buffers或连接内存配置不当日志会给一个大概方向但有时候会被日志轮转覆盖掉或者错误信息比较笼统。这时候就要进入第二步直接看操作系统层面的资源情况。1.2 第二步确认系统资源排除OOM和磁盘耗尽如果是内存不足导致的OOM系统日志里通常会有线索PG日志里则只留下孤零零的signal 9。我排查的时候会同步看三样东西# 查看磁盘剩余 df -h df -i # 查看内存 free -m # 查看系统日志中的OOM记录 dmesg -T | grep -i oom dmesg -T | grep -i postgresdf -i这一步很多人会漏掉。磁盘剩余空间明明很大但inode用满了PG一样写不进文件。我曾经遇到过/tmp分区inode耗尽导致数据库无法启动的情况当时df -h看起来还有将近20G空间折腾了好一会儿才发现是inode的问题。内存这一块PG的OOM通常是因为shared_buffers等参数设得过大加上机器上还跑着Java应用或Redis这类内存大户一旦物理内存吃紧内核的OOM killer会把PostgreSQL主进程当成最值得回收的目标杀掉。之前有客户把 shared_buffers 直接设成16G而机器只有32G内存业务一上来连同操作系统缓存一起超了最后整机卡顿PG主进程被强杀。后续我建议他们调整为8G并给关键进程配置了cgroup的内存限制问题才稳定住。1.3 第三步恢复启动之后还需要做几件事确认资源没大问题后就可以启动数据库了。先看当前数据目录再执行# 如果原来是正常关闭直接启动 pg_ctl -D $PGDATA start # 如果启动失败前台运行并把日志打出来 postgres -D $PGDATA启动成功后不要急着马上把业务切回主库。先验证几个点pg_isready -h 127.0.0.1 -p 5432返回accepting connectionspsql -U postgres -c select 1能正常执行查看日志里有没有新增的报错比如内存不够或者无法解析配置文件如果有从库检查流复制延迟是否正常。如果启动失败日志显示FATAL: lock file postmaster.pid already exists说明postmaster.pid是残留文件。确认没有其他PG进程后手动删除这个pid文件再启动。这里有句老话值得记住不要反复重启一个起不来的数据库看日志永远比盲目试启动更快。恢复之后还要做加固工作为PG配置系统的自动重启策略systemd或docker restart但要加合理的启动退避防止数据库反复崩溃重启把错误掩盖掉同时把这次故障时间点和根因记入运维手册下次再出现类似日志能直接对应到解决方案。2. JSON字段操作从取值、展开到GIN索引优化PostgreSQL的JSON支持是很多人从其他数据库迁移过来的重要原因。但实际运维中我经常发现有人把PG当文本数据库用JSON字段不做任何约束查询全靠like数据量一上去性能立刻崩。其实PG的jsonb类型加上一票内置函数完全能做到像查普通表字段一样高效。2.1 四个取值操作符的区别一次讲透先看一个简单场景。假设订单表里有一个items jsonb字段存储了商品明细{ order_no: A1001, user: { id: 1001, name: 张三 }, items: [ {product_id: 101, price: 19.9, quantity: 2}, {product_id: 102, price: 5.0, quantity: 5} ] }想取order_no有几种写法SELECT items - order_no FROM orders; -- 返回 A1001带双引号属于jsonb类型 SELECT items - order_no FROM orders; -- 返回 A1001文本类型-和-的区别是很多新手最先卡住的地方。-返回结果是JSONB类型-返回的是文本。测试时看起来差不多但一旦把结果传给函数或者做条件判断类型不匹配的坑就出来了。对于嵌套结构比如取user.name可以用#和#这两个操作符接受一个路径数组SELECT items # {user,name} FROM orders; -- 返回 张三我把四个操作符整理成一个表方便收藏操作符用途返回类型示例-取key值按key取值jsonbitems - order_no-取key值按key取文本textitems - order_no#按路径取jsonbjsonbitems # {user,id}#按路径取文本textitems # {user,id}记住“带返回的是JSON带返回的是文本”这个规律大部分用法就通了。2.2 批量展开JSON数组做统计常用函数与写法实际业务里最常用的场景是JSON数组展开成一行行数据然后参与聚合计算。以前我见到有人用jsonb_array_elements时不知道怎么和主表字段关联查出来的结果都是笛卡尔积数据量一大查询直接爆。正确写法是配合CROSS JOIN LATERAL使用SELECT o.id, o.items - order_no AS order_no, item.value - product_id AS product_id, (item.value - price)::numeric AS price, (item.value - quantity)::int AS quantity FROM orders o CROSS JOIN LATERAL jsonb_array_elements(o.items - items) AS item WHERE o.id 12345;这段SQL会把刚才示例里的两条商品明细拆成两行每条明细和订单号关联在一起之后就可以正常group by和统计了。如果要取JSON里面的所有key用jsonb_object_keys很合适比如查看某张表里JSON字段到底有哪些字段SELECT DISTINCT jsonb_object_keys(items) AS key_name FROM orders;如果想知道哪个订单买过product_id101可以用包含操作符这也是GIN索引最配合的条件写法SELECT id, items - order_no AS order_no FROM orders WHERE items - items [{product_id: 101}];这种写法对JSON数组语义的匹配非常准确也适合走索引。2.3 大表JSON检索性能优化GIN索引与表达式索引JSON字段导致慢查询绝大多数原因是没建索引。PG提供了GIN索引来加速、?等JSONB运算符的查询。去年给一张千万级订单表做过性能优化原来某个JSON过滤查询要跑7秒多加了GIN索引后降到几十毫秒效果非常明显。常规做法是CREATE INDEX idx_orders_items_gin ON orders USING GIN (items - items);如果你的查询经常直接对items整个字段做包含判断那么可以直接建CREATE INDEX idx_orders_items_all ON orders USING GIN (items);还有一类场景是JSON里某个key在业务里被频繁当作过滤条件比如items - order_no需要精确匹配。这种场景推荐建表达式索引CREATE INDEX idx_orders_order_no ON orders ((items - order_no));注意函数写法必须和查询时完全一致多一个空格或少一个括号都可能导致索引用不上。我在给团队培训时反复强调先看执行计划确认Node Type里出现Index Scan using才算真正走到索引否则只是白白多占存储空间。关于jsonb和json类型的选择我的建议是新表一律用jsonb它支持索引同时存储时自动去除重复key处理速度也更快老的json类型只在必须保留原始输入顺序或特殊兼容场景下才考虑毕竟它不支持GIN索引查起来基本都是全表扫。3. 备份与恢复这组命令要经常在测试环境里真实跑一遍“备份在不怕一万”这句话在PG运维里不太适用。我见过有人每天crontab跑一次pg_dump日志显示completed successfully结果某天要恢复时才发现备份文件是0字节或者恢复出来的库缺了好几张表。备份的真正价值在于“能恢复”所以恢复演练的优先级比做备份还要高。3.1 逻辑备份pg_dump和pg_restore的标准组合逻辑备份就是导出SQL或自定义格式文件适合单库或单表级别的备份。我最常用的命令是pg_dump -h 127.0.0.1 -U postgres -Fc -d yourdb -f yourdb.dump参数说明-Fc自定义格式压缩体积支持并行恢复-d目标数据库名-f输出文件名。如果要备份某个大表可以加上-t tablename只导出指定表减少耗时和文件大小。备份角色、表空间等全局对象用pg_dumpallpg_dumpall -h 127.0.0.1 -U postgres --globals-only -f global.dump恢复时先建数据库再使用pg_restorecreatedb -U postgres yourdb_restored pg_restore -h 127.0.0.1 -U postgres -d yourdb_restored -j 4 -c yourdb.dump-j 4表示用4个并行线程恢复大表速度能明显提升。这里要注意恢复前最好关闭目标库上的外部连接不然会出现对象冲突报错。3.2 物理备份与时间点恢复PITR的基本链路逻辑备份容易做但很多场景下无法满足“恢复到误操作之前的某一秒”这种需求。物理备份配合WAL日志可以实现PITR这也是生产环境最常用的降级方案。我一般先开启WAL归档以PG16为例在postgresql.conf中配置archive_mode on archive_command test ! -f /backup/pg_wal/%f cp %p /backup/pg_wal/%f wal_level replica max_wal_senders 10然后执行基础备份pg_basebackup -h 127.0.0.1 -U postgres -D /backup/base -Fp -Xs -P这样会在/backup/base下生成一个数据目录的完整镜像WAL文件也一并收集。之后每天的增量都体现在新的WAL归档里。到真正恢复时步骤大致如下停掉当前的PG实例把原数据目录改名备份防止误操作覆盖原始数据把基础备份的文件复制到数据目录在数据目录下创建standby.signal文件同时把postgresql.conf里的恢复目标参数写进去比如recovery_target_time 2025-03-18 14:32:00启动数据库PG会从基础备份开始重放WAL日志一直恢复到指定时间点。这个恢复链路必须提前演练否则真到出问题时你会发现archive_command路径、WAL文件权限、目录空间任何一个细节都可能卡住。3.3 恢复演练——我为什么强调不能只看备份成功简单说一下我自己的教训。有一年我负责的一个业务库数据量涨得特别快备份任务一直正常某天开发误删了一张核心配置表。我需要从当天的备份里找回数据。结果pg_restore跑到一半就报错备份文件里某些对象依赖关系不完整。最后只能回退到前一天的备份丢失了当天更新的一部分数据好在影响还能控制。从那之后我对备份的验证逻辑就变了不只检查备份命令退出的状态码还会随机抽取某个备份文件在隔离环境里真实恢复一次然后用表数量和关键表记录数比对原库。每次重大变更前再做一次针对性恢复演练。你可以用下面这条命令快速验证备份文件的完整性pg_restore -l yourdb.dump | wc -l pg_restore --list yourdb.dump | grep -i TABLE DATA前者统计备份内对象数量后者查找有哪些表的DATA。如果列表数量和实际业务表数量对不上说明备份可能漏了对象。每周做一次随机恢复演练比写十篇备份规范都管用。4. 下载安装到初始密码新环境落地的关键细节部署一套新的PG实例看起来简单实际上不少团队在初始阶段就埋下了隐患。比如安装完不知道该去哪里改密码或者改完密码之后本地连接还是需要输入密码。这一节我把从下载到首次登录的完整链路梳理一下。4.1 不同来源的安装包如何选PostgreSQL的安装方式主要分三类操作系统官方源、PGDG官方源、Docker镜像。生产环境里我更推荐操作系统的官方源或PGDG官方源因为安全和稳定性能跟上测试和开发环境用Docker最省事。以Ubuntu 22.04为例装PG 16sudo apt update sudo apt install postgresql-16 postgresql-client-16安装完成后默认数据目录在/var/lib/postgresql/16/main配置文件在/etc/postgresql/16/main/服务名称为postgresql16-main。如果用Dockerdocker run -d \ --name pg16 \ -e POSTGRES_PASSWORDyourpass \ -e POSTGRES_DBappdb \ -p 5432:5432 \ -v /data/pg:/var/lib/postgresql/data \ postgres:16这里提醒一句Docker环境务必把数据目录挂载到宿主机否则容器一删数据全没这是新手最常见的事故来源。4.2 首次登录、密码设置与pg_hba.conf配合安装完成后系统会创建一个名为postgres的操作系统用户数据库超级用户默认也是postgres。本地使用Unix套接字连接时认证方式通常为peer也就是说只要你当前系统用户是postgres就可以直接进数据库sudo -u postgres psql进入后设置密码ALTER USER postgres WITH PASSWORD 你的强密码;但这时你如果用密码方式连本机可能会发现连不上psql -h 127.0.0.1 -U postgres -W这多半是pg_hba.conf里针对TCP连接host条目的认证方式没有改。PG默认的pg_hba.conf在Ubuntu上位于/etc/postgresql/16/main/pg_hba.conf需要添加或调整如下内容host all all 127.0.0.1/32 scram-sha-256 host all all 0.0.0.0/0 scram-sha-256注意点有两个一是不要把0.0.0.0/0写在前面覆盖掉更严格的配置pg_hba.conf是自上而下匹配的第一条匹配就直接生效二是不要图省事用trusttrust等于免密放行一个错误配置可能导致数据库裸奔在公网上。改完之后执行sudo systemctl reload postgresql不需要重启reload就会重新加载pg_hba.conf。然后就可以用密码登录了psql -h 127.0.0.1 -U postgres -p 54324.3 安装后最值得先调的几个参数装完后直接跑默认参数不太现实尤其是内存和并发这组。我把最基础的调整列在下面你可以按机器规格先做一个保守估计参数默认值建议起步值说明shared_buffers128MB机器内存的25%PG共享缓存池太大也没用配合OS缓存work_mem4MB32MB~64MB排序、hash操作的内存预算太高容易爆内存maintenance_work_mem64MB256MB~512MB优化VACUUM、CREATE INDEX等维护操作max_connections100200~500连接数上限注意会占用一定内存wal_levelreplicareplica生产必须保持replica以上否则无法做流复制max_wal_size1GB2GB~4GB控制checkpoint频率和恢复时间如果有系统参数调优需要修改postgresql.conf后重启work_mem这类参数可以在会话级动态设置但全局值要修改配置文件。我不会一上来就把所有参数调到最大而是让数据库跑一段时间再根据监控慢慢调整。另外中文环境还建议确认一下lc_monetary和lc_numeric避免后续金额、数字格式化出现意想不到的结果。如果业务映射和字符集需求较高初期最好就在模板库里把encoding设置为UTF8。5. 慢查询和锁等待性能故障定位的基本功数据库性能变差通常表现为业务接口变慢、CPU上涨、连接数打满。这些问题的背后大多是慢SQL、锁等待和不合理的查询计划。性能调优的入门门槛不高关键是把定位工具用起来。5.1 用日志和auto_explain捕捉慢SQL最朴素的方法是开启慢查询日志。在postgresql.conf中设置log_min_duration_statement 1000这个配置表示执行超过1000毫秒的SQL会被记录到日志。配合auto_explain模块还能把执行计划自动打印出来这个信息对调优来说非常宝贵shared_preload_libraries auto_explain auto_explain.log_min_duration 1000 auto_explain.log_analyze on注意shared_preload_libraries需要在启动前配置修改后必须重启实例。auto_explain.log_min_duration是日志模块里的阈值设置成和慢查询日志相同每次慢SQL都会附带执行计划。重启后等业务跑一段时间再去日志文件里搜索带duration: 1000的SQL通常能找到系统最拖后腿的几个查询。5.2 pg_stat_statements找出吃资源的大户如果不想每次都在日志里翻可以安装pg_stat_statements扩展它会记录SQL、调用次数、平均耗时、总耗时、读取行数等统计信息。这个扩展默认在contrib包中需要和auto_explain一样提前配置shared_preload_libraries auto_explain,pg_stat_statements重启后执行CREATE EXTENSION pg_stat_statements;接下来就能通过SQL查看统计。比如找出累计执行时间最长的10条SQLSELECT queryid, calls, total_exec_time, mean_exec_time, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;这里有个版本差异PG13之前是total_timePG13之后改成了total_exec_time如果脚本报列不存在检查一下PG版本。用完这类数据后我会挑出calls次数多且耗时高的SQL去和开发确认业务场景再针对性加索引或改写法。5.3 锁等待的处理什么时候可以kill数据库卡住不一定是CPU或磁盘问题更多时候是行锁、表锁互相等待。我排查锁问题时第一步永远是查看pg_stat_activitySELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS query_duration, left(query, 120) AS query FROM pg_stat_activity WHERE pid pg_backend_pid() ORDER BY query_duration DESC NULLS LAST;这个查询会列出所有会话按已执行时间排序同时显示等待类型。如果某个pid的wait_event_type是Lock说明它在等锁。进一步可以查到它到底被谁阻塞SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid ANY(pg_blocking_pids(blocked.pid)) WHERE blocked.wait_event_type Lock;确认锁源之后再决定怎么处理。通常先和开发确认锁住的会话是否可以结束如果可以执行SELECT pg_terminate_backend(阻塞pid);如果阻塞的会话一直处于idle in transaction状态说明事务开了但没提交时间长了会导致数据库连接被占满这种一般可以直接终止。但如果是大批量更新任务执行到一半被kill回滚带来的资源消耗也需要评估不能盲目下手。6. 这些年在PG运维上保留的几个习惯最后聊几个我在长期运维里坚持的习惯算是给同样在一线的朋友们一点参考。备份的验证频率我给自己定的标准是一周至少一次。不是只看备份脚本退出码而是真正把备份拉到一台干净的环境里恢复然后用几条基础SQL对比表记录数。这个动作能暴露出的问题比你想的要多得多比如某个表因为权限问题根本没被备份进去、某些依赖对象恢复顺序不对这些在恢复前都很难看出来。日志和指标的记录我会用最简单的脚本把关键指标落到本地文件CPU、内存、磁盘、PG的活跃连接数、慢查询数量每天轮转。这些历史数据看起来原始但出问题时回头看往往能还原出故障前几个小时的变化比任何监控大屏都实在。不要为了上监控而上监控数据留存本身就有价值。遇到锁等待和连接数打满的情况我养成了第一个动作不是直接kill而是先看pg_stat_activity里每条连接的state。很多所谓的死锁实际上是事务没提交导致的找到源头之后和业务团队确认再处理比盲目清session稳妥得多。这个习惯救过我几次也让团队避免了不少误杀。PostgreSQL运维没有什么特别的秘诀无非是平时多看日志、多跑恢复演练、多关心慢SQL和锁等待背后的事实。把这几个基本功练扎实2025年的PG运维就不会总在救火中度过了。