1. 报错信息到底在说什么先把定位思路理顺很多人拿到 PostgreSQL 报错的第一反应是复制整段英文丢进搜索框然后在一堆互不相关的答案里挑一个最像的试。这个习惯在处理 MySQL 的时候勉强能混过去放到 PostgreSQL 上效率会非常低。原因不复杂PostgreSQL 的报错信息写得相当克制而且结构化它其实已经把“谁、在哪一步、因为什么失败了”这三件事交代得比较清楚只是大部分人没耐心读完那一行。我真正开始系统处理这些问题的转折点是把注意力从“报错文本”转到“SQLSTATE 错误码”上。文本会随着版本、locale、甚至客户端语言环境变化错误码不会。一旦养成先看码再看文案的习惯排障速度能提升一大截。下面这些内容是我这几年维护过几十套 PostgreSQL 实例之后把踩过的坑重新按“阶段 错误码”整理出来的适合刚上手 PostgreSQL 的开发者也适合已经用了几年但一直靠搜索解决问题的运维同学。先给一个结论PostgreSQL 的错误按发生阶段大致可以分成四层——安装与初始化层、连接与认证层、权限与对象层、运行与资源层。这四层的排查入口、日志位置、可用的工具都完全不同。把层级先分对基本就成功了一半。1.1 SQLSTATE 五字符错误码比错误文本更可靠的锚点PostgreSQL 遵循 SQL 标准定义的 SQLSTATE是五个字符组成的编码前两位表示类别后三位表示具体条件。你可以用\errverbose在 psql 里把最近一次错误的详细信息全部打出来包括 SQLSTATE、详情DETAIL、提示HINT、上下文CONTEXT和出错位置。这个命令的价值在于普通模式下 psql 只显示一行主消息把最有用的 HINT 藏起来了。-- 在 psql 中执行任意报错语句后 \errverbose日常排障里命中率最高的错误码大概就这么一批建议直接记住SQLSTATE含义典型触发场景28P01密码认证失败密码错、加密方式不匹配28000认证方式被拒pg_hba.conf 没匹配到规则3D000数据库不存在连错库名、库还没建42P01表或视图不存在search_path 不对、建表失败42501权限不足schema USAGE 或对象权限缺失23505唯一约束冲突并发插入、幂等逻辑写错40P01检测到死锁事务加锁顺序不一致53300连接数超限连接池配置或连接泄漏53100磁盘空间不足WAL 或临时文件写满盘25006只读事务中执行写操作主备切换后连到了备库57P03数据库正在启动中启动未完成就接受连接把这几个码记住绝大多数报错你能在十秒内判断出该往哪个方向查。尤其是40P01和53300这两个问题的处理思路完全相反前者要减少并发冲突后者要控制并发数量一旦搞混越调越乱。1.2 三层排查顺序客户端提示、服务端日志、操作系统层确定错误码之后我习惯按“客户端 → 服务端日志 → 操作系统”这个顺序推进原因是成本递增。客户端提示是免费的服务端日志需要你有权限读操作系统层往往要登机器、查内核参数最费时间。第一层客户端提示。psql、JDBC、psycopg 抛出的错误里DETAIL 和 HINT 是最值钱的部分。比如FATAL: no pg_hba.conf entry for host 10.0.0.21这条DETAIL 会直接告诉你哪个用户、哪个库、是否使用了 SSL把这些信息抄下来就能直接去改配置不用猜。第二层服务端日志。这一层的信息量远大于客户端因为服务端会把慢查询、锁等待、检查点、临时文件、autovacuum 的行为全部记录下来前提是你把logging_collector和几个log_*参数打开。默认配置下 PostgreSQL 只记录启动信息和严重错误很多问题在客户端只看到一个笼统的失败真正的根因躺在日志里。第三层操作系统。到了这一层通常意味着问题不在数据库内部了数据目录属主不对、端口被别的进程占用、ulimit限制文件句柄数、OOM Killer 把 postmaster 干掉了、磁盘 inode 耗尽。这些现象在日志里的表现往往很反常比如数据库进程突然消失却没有报错那就要去查dmesg。提示很多人排障时只看客户端报错就下结论结果在错误方向上折腾几小时。养成“客户端提示定方向、服务端日志找证据、系统层查根因”的习惯能省下大量时间。2. 安装与初始化阶段的坑从一堆“装不上”说起安装环节的问题有个共同特点它们几乎都发生在数据库真正可用之前所以你没有pg_stat_activity、没有慢查询日志、也没有任何 SQL 层面的抓手只能靠安装器输出和系统日志。这也是新手最容易卡死的地方因为搜索引擎给出的答案经常来自完全不同的版本和平台照抄必然翻车。我在帮别人处理安装问题时总结出一条经验先把平台、安装方式、目标版本这三个变量固定下来再去搜答案。Windows 图形化安装、Linux 包管理器安装、源码编译、容器镜像这四条路径遇到的问题几乎没有交集。2.1 Windows 图形化安装卡住与失败的真实原因Windows 上用官方安装器装 PostgreSQL最常见的失败点是初始化数据目录这一步界面上往往只给一句很含糊的提示让人无从下手。结合实际处理过的案例原因集中在四个方面。第一是路径问题。数据目录里带中文、空格或者特殊字符某些情况下会导致初始化失败。我现在的习惯是统一用C:\pgdata\16这种纯英文短路径不放在用户目录下也绝不放 OneDrive 同步目录里——同步盘会在数据库运行时锁文件直接把实例搞崩。第二是 locale 与编码。安装向导里有 locale 选项如果选了系统里实际不存在的 locale初始化阶段就会报错。稳妥做法是 locale 选C编码选UTF8后续如果确实需要本地化排序规则再通过CREATE DATABASE ... LC_COLLATE单独指定。这里有个细节C排序规则下的字符串比较是按字节走的性能最好但中文排序结果不符合拼音顺序业务上如果有中文排序需求就得用zh_CN.UTF-8这类 locale代价是索引体积和比较开销都会增加。第三是端口占用。安装器默认用 5432如果机器上已经跑着一个 PostgreSQL 或者别的服务占了这个端口初始化能过但服务起不来。装之前先确认# Windows PowerShell / CMD netstat -ano | findstr :5432第四是安全软件拦截。这一点很难自查因为拦截往往悄无声息。表现是安装过程没有任何报错但服务注册失败或者 initdb 中途退出。临时关闭实时防护再装一次问题就消失了。装完之后记得把数据目录加入白名单否则运行期也会偶发文件访问失败。2.2 Linux 与国产化平台上的初始化、属主和 localeLinux 上包管理器安装通常很顺真正的问题出在“手动指定数据目录”和“换用户跑服务”这两件事上。最常见的报错是FATAL: data directory /var/lib/postgresql/16/main has wrong ownership。PostgreSQL 出于安全考虑拒绝以数据目录属主之外的用户身份启动也拒绝 root 直接跑initdb会明确提示cannot be run as root。正确做法是切换到 postgres 用户再操作sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /data/pg16/data -E UTF8 --localeC sudo chown -R postgres:postgres /data/pg16/data sudo chmod 700 /data/pg16/data数据目录权限必须是700这一点没有商量余地PostgreSQL 会主动检查。我见过有人为了“方便查看”把权限改成 755结果服务直接拒绝启动改回去就好了。第二个坑是 locale 缺失。某些精简版系统和部分国产化发行版默认只装了极少数 localeinitdb时会报invalid locale name。先用locale -a看一眼系统里有什么如果没有需要的安装对应的 locale 包或者干脆用--localeC别硬扛。第三个坑是 systemd 托管。手动pg_ctl start能起来、systemctl start postgresql却失败这种情况九成是 systemd 单元文件里的PGDATA或PGPORT与实际不一致。排查入口是systemctl status postgresql16-main journalctl -u postgresql16-main -n 100 --no-pager顺带说一句版本号。PostgreSQL 从 10 开始采用“主版本.小版本”的两段式命名比如 14.24 表示 14 这个主版本的第 24 个小版本修订。如果你看到类似14.24.2这种三段式写法那通常是第三方打包方自己的构建编号不是官方版本号。这个问题看着小但影响实际选型扩展包、驱动、备份工具的兼容性判断都得基于正确的版本号来做。2.3 扩展装不上以 pgvector 为例的通用排查链扩展安装失败几乎是所有 PostgreSQL 使用者都会遇到的一关不管是向量检索、全文检索还是地理信息类扩展报错形式高度相似。处理思路可以固化成一条链。第一步确认扩展文件有没有装到 PostgreSQL 能识别的位置。用pg_config看两个关键路径pg_config --sharedir # 控制文件 .control 应该在这里的 extension 子目录 pg_config --pkglibdir # 动态库 .so 应该在这里第二步确认版本匹配。pg_config输出的路径如果指向的是另一套 PostgreSQL比如系统自带的和你自己编译的那扩展会被装到错误的目录CREATE EXTENSION自然找不到。这是多版本共存环境里最高频的问题。第三步确认编译依赖。以源码编译方式安装向量扩展为例典型的流程是sudo apt install -y postgresql-server-dev-16 build-essential cd pgvector make sudo make installmake阶段报头文件找不到基本都是postgresql-server-dev-*没装或版本不对。第四步进库执行并检查CREATE EXTENSION vector; SELECT * FROM pg_available_extensions WHERE name vector;注意离线环境比如内网服务器需要提前把扩展源码、编译工具链和 PostgreSQL 开发包一起准备好。我一般是先在一台同版本的联网机器上把编译产物目录整体打包再拷到目标机器按相同路径释放比在目标机上现装工具链省事得多。3. 连接类错误日常排障里出现频率最高的一大类如果让我统计这几年的排障工单连接类问题能占到一半左右。原因也很容易理解应用和数据库之间隔着一层网络、一层认证配置、一层权限模型任何一层不一致都会表现为“连不上”而报错文本又往往只有一句话。处理这类问题的效率差异主要体现在“有没有按顺序排除”。我见过不少人从数据库内部开始查查了半天发现是防火墙没放开也见过有人一上来就改pg_hba.conf结果根因是应用连错了端口。下面按从外到内的顺序拆。3.1 五个高频连接报错逐条拆解第一类连接被拒绝。报错长这样connection to server at 10.0.0.5, port 5432 failed: Connection refused。注意关键词是 “Connection refused”它意味着 TCP 层就被拒绝了根本没走到数据库的认证流程。可能的原因有三个数据库没启动、监听地址不包含该网卡、或者防火墙挡了。判断顺序是先在服务器本地psql -h 127.0.0.1 -U postgres试一次本地能连说明实例是活的那问题就在监听或网络本地也连不上先去看服务状态。第二类no pg_hba.conf entry。完整的报错会带上主机、用户、数据库和 SSL 状态比如FATAL: no pg_hba.conf entry for host 10.0.0.21, user app, database appdb, SSL off这条信息其实已经把答案写出来了——pg_hba.conf里没有任何一条规则同时匹配这个来源地址、用户和库。要特别警惕的是很多人只改了pg_hba.conf却没重载配置改完自我感觉良好实际根本没生效。重载方式有两种SELECT pg_reload_conf();sudo -u postgres pg_ctl reload -D /data/pg16/data第三类密码认证失败。FATAL: password authentication failed for user app的表面含义很直白但真实原因有好几种。最常见的是加密方式不匹配PostgreSQL 10 引入了 SCRAM-SHA-25614 版本起新建用户的默认密码加密方式就是它。如果pg_hba.conf里写的是scram-sha-256而库里这个用户的密码是用老的md5方式存储的认证就会失败而且报错信息不会告诉你这个原因。修法是重新设一次密码-- 确认当前默认加密方式 SHOW password_encryption; -- 设为 scram-sha-256 后重设密码 SET password_encryption scram-sha-256; ALTER USER app WITH PASSWORD NewPassw0rd;另一种原因是老客户端驱动不支持 SCRAM。JDBC 需要在 42.2.10 以上版本才完整支持Go 的lib/pq也有类似限制。升级驱动通常比降级认证方式更安全别为了图省事把pg_hba.conf改成md5。第四类too many clients already。这是资源层问题后面第 5 节会详细讲参数估算这里先记住它对应的 SQLSTATE 是 53300而且它经常是连接泄漏的症状不是配置太小的症状。盲目调大max_connections只是把问题往后推迟。第五类库或角色不存在。FATAL: database appdb does not exist和FATAL: role app does not exist都属于这一类没什么技术含量但排查时容易忽略大小写。PostgreSQL 的标识符默认折叠为小写如果建库时用了双引号包裹大写名字后面不带引号就找不到这类问题在从 MySQL 迁移过来的团队里特别常见。3.2 pg_hba.conf 的匹配顺序与配置参数速查pg_hba.conf的核心规则只有一条从上往下逐条匹配命中第一条就停止不再继续往下看。这条规则解释了绝大部分“配置看着没问题但就是连不上”的案例——你在文件末尾加了一条允许规则但前面有一条范围更大的拒绝规则已经命中了。一个可用的模板长这样# TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all scram-sha-256 host appdb app 10.0.0.0/24 scram-sha-256 host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 host all all 0.0.0.0/0 reject几个容易踩的点值得单独说。local类型的连接走的是 Unix socketpsql不带-h参数时默认就是这种方式此时peer认证会拿操作系统用户名去比对数据库角色名所以sudo -u postgres psql能进换成普通用户就被拒这不是配置错了是 peer 机制的预期行为。ADDRESS字段的写法也有讲究10.0.0.0/24表示一个网段0.0.0.0/0在 IPv4 里表示全部地址但IPv6 连接不会匹配 IPv4 的规则服务器开了 IPv6 的话::1/128这条必须单独写。方法字段的选择上能用的值包括trust无认证只适合本地临时调试、peer、md5、scram-sha-256、cert、gss、ldap、radius、pam。生产环境里用scram-sha-256就行trust只在容器初始化和临时排障时用用完立刻改回来。3.3 监听地址、端口与容器场景的差异listen_addresses的默认值是localhost意味着只监听本地回环。远程连不上但本地正常第一件事就是查这个参数SHOW listen_addresses; SHOW port;改成*之后必须重启不是重载才能生效这一点和pg_hba.conf不一样容易记混。改完之后记得用ss -lntp | grep 5432确认监听状态我遇到过改完配置重启了但端口实际没起来的情况根因是另一处配置有语法错误postmaster 回退到了上一个可用配置。容器场景要额外注意两点。第一镜像里的pg_hba.conf默认只允许来自同一容器的连接映射端口到宿主机后从宿主机连进去的来源地址是 Docker 网关地址通常是172.17.0.1规则没覆盖就会报no pg_hba.conf entry。第二数据卷挂载到/var/lib/postgresql/data时如果宿主目录是新建的空目录初始化能正常走但如果目录里存在一个不完整的数据集容器会跳过初始化直接启动然后失败退出。用一个 compose 片段把关键点标出来services: db: image: postgres:16 environment: POSTGRES_PASSWORD: ChangeMe_2024 POSTGRES_DB: appdb PGDATA: /var/lib/postgresql/data/pgdata ports: - 5432:5432 volumes: - pgdata:/var/lib/postgresql/data healthcheck: test: [CMD-SHELL, pg_isready -U postgres -d appdb] interval: 10s timeout: 3s retries: 5 volumes: pgdata:这里把PGDATA指向数据卷下的子目录是为了避开某些文件系统上lostfound之类的残留文件干扰初始化。健康检查用pg_isready而不是psql因为这个命令不建立真实连接只探测服务状态开销极小适合高频调用。4. 权限与对象错误permission denied 的完整解法连上了、认证过了接下来遇到的就是权限问题。这类报错的特点是信息量少得可怜ERROR: permission denied for table orders只告诉你结果不告诉你缺哪一层权限。而 PostgreSQL 的权限模型是分层的缺任何一层都会失败。4.1 PostgreSQL 权限模型的三个层次我把这个模型拆成三层来理解排查时按层往下走。第一层是角色自身的属性。角色是否有LOGIN、SUPERUSER、CREATEDB、CREATEROLE这些是登录和建对象的前提。用下面的语句看一眼SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin FROM pg_roles WHERE rolname app;第二层是 schema 上的权限。这是最容易被忽略的一层。要在某个 schema 下访问对象你必须先拥有该 schema 的USAGE权限。数据库默认有publicschema而PostgreSQL 15 起收紧了publicschema 的默认权限普通用户不再默认拥有CREATE权限很多从 14 升级上来或者照着旧教程操作的人会在这里撞墙。报错形式是ERROR: permission denied for schema public对应的 SQLSTATE 是 42501。第三层才是对象本身的权限。表、视图、序列、函数各自有独立的权限位。序列这一项特别容易漏因为INSERT到带自增列的表里实际上还要有序列的USAGE权限报错会明确指向序列名但很多人第一眼看不出关联。4.2 高频权限报错与可直接抄的修复语句把三层理顺之后权限修复其实就是按需授权。下面这组语句覆盖了我日常处理的绝大部分场景顺序也很重要先 schema 再对象-- 1. 角色可登录 ALTER ROLE app WITH LOGIN; -- 2. schema 使用权 GRANT USAGE ON SCHEMA public TO app; -- 3. 按需给建表权15 及以上版本必须显式给 GRANT CREATE ON SCHEMA public TO app; -- 4. 已有对象的读写权 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app; -- 5. 让未来新建的对象自动继承权限这一步最容易被漏掉 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app;第 5 步是这套流程里最有价值的一步也是我踩过坑之后才养成的习惯。GRANT ... ON ALL TABLES只对执行那一刻已经存在的对象生效之后新建的表不会自动继承权限。表现就是开发同学今天加了一张表应用明天开始报permission denied而 DBA 查了半天发现权限配置“明明没问题”。用ALTER DEFAULT PRIVILEGES把这个缺口补上从此省心。有个细节要注意ALTER DEFAULT PRIVILEGES是按执行者身份生效的。也就是说这条语句必须以“将来建表的那个角色”的身份执行或者是管理员显式指定FOR ROLE 建表角色。执行者和建表者不一致的话规则不会生效。这是个非常隐蔽的坑我见过团队里照抄语句后仍然报权限错的案例根因就在这里。实操心得给应用账号授权时别图省事直接给 SUPERUSER。一旦应用被注入或者有代码逻辑缺陷超级用户权限意味着整库都能被删。生产环境里我一般遵循最小权限原则业务账号只给具体对象的增删改查外加必要时的序列使用权够用就行。5. 运行期高发问题锁、连接数、磁盘与日志过了安装和连接这两关系统跑起来之后问题会换一种形式出现不再是“进不去”而是“越来越慢”和“偶尔卡死”。这类问题的处理难度比前两类高因为它往往需要结合当前负载、历史趋势和参数配置一起看。我处理过的最耗时的一次线上故障根因是两个业务模块加锁顺序不一致导致的间歇性死锁日志里每半小时出现一次 40P01业务侧表现为随机超时。5.1 锁等待与死锁的定位与处置先分清两个概念。锁等待是正常的一个事务在等另一个事务释放行锁等一会儿就好了。死锁是异常的两个事务互相持有对方需要的锁谁也不肯先放必须由数据库主动介入杀掉其中一个事务报出 40P01。判断当前是不是卡在锁上我用的第一条 SQL 是这个SELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS running_time, left(query, 80) AS query_snippet FROM pg_stat_activity WHERE wait_event_type Lock ORDER BY running_time DESC;关键是wait_event_type Lock这个条件它能把等待锁的会话精准筛出来。如果查出一堆会话下一步是找“谁是阻塞源”。从 PostgreSQL 9.6 开始有个很方便的函数pg_blocking_pidsSELECT pid, pg_blocking_pids(pid) AS blocking_pids, state, left(query, 80) AS query_snippet FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;blocking_pids字段里的 PID 就是挡路的那些会话。找到之后先看它在干什么再决定是等、是pg_cancel_backend温和取消还是pg_terminate_backend强制断开。我的习惯是先取消因为取消只会中止当前查询不断开会话对连接池友好强制断开会让客户端收到连接中断连接池需要重建连接代价更大。至于死锁真正的根因排查要去读日志。发生死锁时PostgreSQL 会在日志里打印完整的加锁关系包括两个事务各自执行到哪条语句、在等哪把锁。看到这类日志基本就能定位到具体是哪两段业务逻辑加锁顺序反了。修法很简单让所有事务访问多张表时保持一致的顺序比如统一按表名字母序或者按主键大小序加锁冲突概率会大幅下降。5.2 连接数上限与 max_connections 的估算too many clients already这个报错最容易被误处理。很多人第一反应是把max_connections从 100 调到 500改完当时确实不报了但一两周后数据库整体变慢。原因是每个连接在 PostgreSQL 里都是一个独立进程都会占用内存连接数上去之后内存压力和上下文切换开销同步上升。我的做法是先判断这是“配置不够”还是“连接泄漏”。查一下当前连接来源分布SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity GROUP BY usename, application_name, client_addr ORDER BY count(*) DESC;如果发现某个应用占了绝大部分连接而且都是idle状态那就是连接池配得过大或者有连接没归还该去改应用侧不是改数据库侧。反过来如果所有来源的连接数都合理、总量确实接近上限再考虑调整参数。参数估算我一般这么算max_connections 应用连接池峰值之和 运维预留 其中 应用侧3个微服务 × 每服务池上限20 60 后台任务与报表2个任务 × 10 20 管理连接DBA、备份、监控 10 安全余量约总量的 15% 约 14 -------------------------------------------------- 合计 ≈ 104这个数字算出来之后再看内存能不能撑住。每个连接的基础内存开销加上work_mem可能的放大倍数才是真实占用粗略估算方式是最坏情况内存 ≈ max_connections × work_mem × 每个查询最多哈希/排序节点数假设work_mem设成 4MB一个复杂查询里可能同时有 3 个排序或哈希节点那么单连接最坏就是 12MB100 个连接就是 1.2GB。这个数字如果超过了服务器内存的合理比例就要把work_mem降下来而不是硬扛。实践中work_mem设太大会在多并发场景下引发 OOM这个坑我踩过一次教训很深。还有一个容易被忽略的点PostgreSQL 会为超级用户预留一部分连接由superuser_reserved_connections控制默认 3。预留的意义是当普通连接把池子占满时管理员还能连进去做紧急处置。所以实际可用给应用的连接数是max_connections - superuser_reserved_connections配连接池的时候要按这个数字算别按max_connections算。5.3 WAL 膨胀、磁盘写满与只读状态磁盘写满是个典型的“平时没感觉一旦发生就全线瘫痪”的问题。PostgreSQL 在磁盘空间不足时会主动把数据库切成只读状态新写入直接报错SQLSTATE 是 25006 或 53100。这时候连删除数据的操作都可能执行不了因为删除也要写 WAL。容易撑爆磁盘的通常是三样东西WAL 日志、临时文件、表和索引膨胀。WAL 的归档清理依赖复制槽如果建了一个逻辑复制槽但消费端早就下线了这个槽会一直保留 WAL 文件不放磁盘一点点被吃满。排查语句是SELECT slot_name, slot_type, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;active为 false 且retained_wal很大的槽基本可以判定是废弃的确认业务上确实不需要之后删掉它磁盘空间立刻就回来了。临时文件的问题则是work_mem不够时溢写到pgsql_tmp目录导致的日志里打开log_temp_files 0之后每个临时文件的生成都会被记录配合时长和大小信息很容易定位到是哪条 SQL 在频繁溢写。表膨胀这一项要交给 autovacuum。如果发现某张表的死元组比例长期很高先看 autovacuum 有没有被阻塞。长事务会阻止 vacuum 回收空间一个跑了几个小时的只读长事务能让整库的清理工作全部卡住。这种情况在报表任务里很常见解决办法是给所有长事务设置合理的statement_timeout或者idle_in_transaction_session_timeout。6. 我常用的排查工具与配置清单前面五节讲的是“遇到问题怎么办”这一节讲“怎么让问题自己暴露出来”。这部分的价值在于前置投入小、长期收益大属于典型的一次配置长期受益。我接手一套新实例时前两件事一定是看日志配置和确认排查入口因为这两件事决定了后面所有问题的处理效率。6.1 日志配置让问题自己说话默认的日志配置信息量太少出问题时只能靠猜。下面这组参数是我在大多数生产实例上会开的兼顾信息完整和磁盘占用logging_collector on log_directory log log_filename postgresql-%Y-%m-%d.log log_min_duration_statement 200ms log_checkpoints on log_connections on log_disconnections on log_lock_waits on log_temp_files 0 log_autovacuum_min_duration 1s log_line_prefix %m [%p] %q%u%d/%a 逐条说下理由。log_min_duration_statement 200ms记录所有超过 200 毫秒的语句这个阈值在 OLTP 场景里比较合适再低日志会太吵。log_lock_waits打开之后任何等待时间超过deadlock_timeout默认 1 秒的锁等待都会被记录这是排查锁问题的第一手资料。log_temp_files 0记录所有临时文件用于定位work_mem不足。log_line_prefix里的%m是带毫秒的时间戳%p是进程号%q会终止非会话进程的输出%u%d/%a是用户、库名和应用名——这几个字段在关联客户端行为时非常有用尤其是%aapplication_name它能让日志和具体服务一一对应上。这里的%a有个前提客户端要主动设置application_name。JDBC 可以在连接串里加ApplicationNameorder-service连接池也支持在初始化时统一设置。设好之后日志里一眼就能看出是哪个服务在捣乱比靠 IP 猜靠谱得多。注意log_connections和log_disconnections在连接数极高的系统上会产生大量日志磁盘写入压力不小。如果实例 QPS 很高可以考虑只开log_disconnections或者干脆都关掉转而依赖连接池自己的监控指标。6.2 手边常备的排查 SQL除了前面提到的锁和连接查询还有几条 SQL 我几乎每周都会用。整理成一张表方便直接抄用途关键视图或函数关注字段找最耗时的活跃查询pg_stat_activityquery_start、state找阻塞关系pg_blocking_pids(pid)阻塞者 PID 列表看表的大小和膨胀pg_total_relation_size、pg_stat_user_tablesn_dead_tup、n_live_tup看索引是否被用pg_stat_user_indexesidx_scan长期为 0 可考虑清理查缓存命中率pg_stat_databaseblks_hit / (blks_hit blks_read)看复制槽占用pg_replication_slotsactive、滞留 WAL 量查长事务pg_stat_activityxact_start与当前时间差缓存命中率这条特别值得盯。正常情况下应该在 99% 以上如果掉到 95% 以下说明大量请求在走磁盘shared_buffers可能偏小或者有全表扫描把缓存冲掉了。shared_buffers的经验值是物理内存的 25%但这个比例在内存特别大的机器上不必严格遵循超过 8GB 之后收益递减剩下的内存留给操作系统页缓存效果更好。索引那一条也是我经常处理的场景。idx_scan长期为 0 的索引不但没用还会拖慢写入因为每次 INSERT 都要同步维护它。但删索引前必须谨慎有些索引只在月末报表或者年度结算时才被用到idx_scan在短期内看着是 0。稳妥做法是观察一个完整的业务周期比如覆盖一次月度结算再做决定。6.3 驱动、客户端与版本匹配的细节最后说一个不常被提起但很关键的方面客户端与服务端的版本匹配。PostgreSQL 的服务端和客户端是向后兼容的但兼容不等于没有坑。JDBC 驱动方面42.2.10 之前的版本不支持 SCRAM-SHA-256在老项目里升级 PostgreSQL 服务端之后经常遇到认证失败根因就在驱动版本。Python 的psycopg2在高并发场景下如果用psycopg2的同步接口配合大量线程会出现连接占用问题换成psycopg3或者配连接池会好很多。Go 生态里lib/pq已经进入维护状态新项目更推荐pgx它对 SCRAM、sslmodeverify-full、批量操作的支持都更完善。版本号读法也值得再强调一次。服务端 16.x 的实例配 16 系列的扩展包和驱动不要把 15 的二进制扩展装到 16 上版本不匹配时CREATE EXTENSION可能成功、但运行时加载动态库报符号错误这类问题的排查成本极高因为报错信息指向的是加载失败而不是版本冲突。另外图形化客户端连接服务端时如果客户端版本远高于服务端某些新特性相关的功能会报错反过来服务端远高于客户端时一些新类型在客户端里显示不出来。团队里维持客户端和服务端大版本接近是个成本很低收益不错的做法。我在实际维护中体会最深的一点是PostgreSQL 的报错信息其实相当诚实它几乎不会骗你问题往往在于我们只看了主消息而忽略了 DETAIL、HINT 和日志。把这几个信息源串起来用配合前面说的四层分阶段思路大部分问题都能在半小时内定位到。反倒是那些跳过信息收集、直接改参数的操作最容易把小问题拖成大事故。 PostgreSQL 报错排查实战：SQLSTATE、连接、权限与运行期高频问题
1. 报错信息到底在说什么先把定位思路理顺很多人拿到 PostgreSQL 报错的第一反应是复制整段英文丢进搜索框然后在一堆互不相关的答案里挑一个最像的试。这个习惯在处理 MySQL 的时候勉强能混过去放到 PostgreSQL 上效率会非常低。原因不复杂PostgreSQL 的报错信息写得相当克制而且结构化它其实已经把“谁、在哪一步、因为什么失败了”这三件事交代得比较清楚只是大部分人没耐心读完那一行。我真正开始系统处理这些问题的转折点是把注意力从“报错文本”转到“SQLSTATE 错误码”上。文本会随着版本、locale、甚至客户端语言环境变化错误码不会。一旦养成先看码再看文案的习惯排障速度能提升一大截。下面这些内容是我这几年维护过几十套 PostgreSQL 实例之后把踩过的坑重新按“阶段 错误码”整理出来的适合刚上手 PostgreSQL 的开发者也适合已经用了几年但一直靠搜索解决问题的运维同学。先给一个结论PostgreSQL 的错误按发生阶段大致可以分成四层——安装与初始化层、连接与认证层、权限与对象层、运行与资源层。这四层的排查入口、日志位置、可用的工具都完全不同。把层级先分对基本就成功了一半。1.1 SQLSTATE 五字符错误码比错误文本更可靠的锚点PostgreSQL 遵循 SQL 标准定义的 SQLSTATE是五个字符组成的编码前两位表示类别后三位表示具体条件。你可以用\errverbose在 psql 里把最近一次错误的详细信息全部打出来包括 SQLSTATE、详情DETAIL、提示HINT、上下文CONTEXT和出错位置。这个命令的价值在于普通模式下 psql 只显示一行主消息把最有用的 HINT 藏起来了。-- 在 psql 中执行任意报错语句后 \errverbose日常排障里命中率最高的错误码大概就这么一批建议直接记住SQLSTATE含义典型触发场景28P01密码认证失败密码错、加密方式不匹配28000认证方式被拒pg_hba.conf 没匹配到规则3D000数据库不存在连错库名、库还没建42P01表或视图不存在search_path 不对、建表失败42501权限不足schema USAGE 或对象权限缺失23505唯一约束冲突并发插入、幂等逻辑写错40P01检测到死锁事务加锁顺序不一致53300连接数超限连接池配置或连接泄漏53100磁盘空间不足WAL 或临时文件写满盘25006只读事务中执行写操作主备切换后连到了备库57P03数据库正在启动中启动未完成就接受连接把这几个码记住绝大多数报错你能在十秒内判断出该往哪个方向查。尤其是40P01和53300这两个问题的处理思路完全相反前者要减少并发冲突后者要控制并发数量一旦搞混越调越乱。1.2 三层排查顺序客户端提示、服务端日志、操作系统层确定错误码之后我习惯按“客户端 → 服务端日志 → 操作系统”这个顺序推进原因是成本递增。客户端提示是免费的服务端日志需要你有权限读操作系统层往往要登机器、查内核参数最费时间。第一层客户端提示。psql、JDBC、psycopg 抛出的错误里DETAIL 和 HINT 是最值钱的部分。比如FATAL: no pg_hba.conf entry for host 10.0.0.21这条DETAIL 会直接告诉你哪个用户、哪个库、是否使用了 SSL把这些信息抄下来就能直接去改配置不用猜。第二层服务端日志。这一层的信息量远大于客户端因为服务端会把慢查询、锁等待、检查点、临时文件、autovacuum 的行为全部记录下来前提是你把logging_collector和几个log_*参数打开。默认配置下 PostgreSQL 只记录启动信息和严重错误很多问题在客户端只看到一个笼统的失败真正的根因躺在日志里。第三层操作系统。到了这一层通常意味着问题不在数据库内部了数据目录属主不对、端口被别的进程占用、ulimit限制文件句柄数、OOM Killer 把 postmaster 干掉了、磁盘 inode 耗尽。这些现象在日志里的表现往往很反常比如数据库进程突然消失却没有报错那就要去查dmesg。提示很多人排障时只看客户端报错就下结论结果在错误方向上折腾几小时。养成“客户端提示定方向、服务端日志找证据、系统层查根因”的习惯能省下大量时间。2. 安装与初始化阶段的坑从一堆“装不上”说起安装环节的问题有个共同特点它们几乎都发生在数据库真正可用之前所以你没有pg_stat_activity、没有慢查询日志、也没有任何 SQL 层面的抓手只能靠安装器输出和系统日志。这也是新手最容易卡死的地方因为搜索引擎给出的答案经常来自完全不同的版本和平台照抄必然翻车。我在帮别人处理安装问题时总结出一条经验先把平台、安装方式、目标版本这三个变量固定下来再去搜答案。Windows 图形化安装、Linux 包管理器安装、源码编译、容器镜像这四条路径遇到的问题几乎没有交集。2.1 Windows 图形化安装卡住与失败的真实原因Windows 上用官方安装器装 PostgreSQL最常见的失败点是初始化数据目录这一步界面上往往只给一句很含糊的提示让人无从下手。结合实际处理过的案例原因集中在四个方面。第一是路径问题。数据目录里带中文、空格或者特殊字符某些情况下会导致初始化失败。我现在的习惯是统一用C:\pgdata\16这种纯英文短路径不放在用户目录下也绝不放 OneDrive 同步目录里——同步盘会在数据库运行时锁文件直接把实例搞崩。第二是 locale 与编码。安装向导里有 locale 选项如果选了系统里实际不存在的 locale初始化阶段就会报错。稳妥做法是 locale 选C编码选UTF8后续如果确实需要本地化排序规则再通过CREATE DATABASE ... LC_COLLATE单独指定。这里有个细节C排序规则下的字符串比较是按字节走的性能最好但中文排序结果不符合拼音顺序业务上如果有中文排序需求就得用zh_CN.UTF-8这类 locale代价是索引体积和比较开销都会增加。第三是端口占用。安装器默认用 5432如果机器上已经跑着一个 PostgreSQL 或者别的服务占了这个端口初始化能过但服务起不来。装之前先确认# Windows PowerShell / CMD netstat -ano | findstr :5432第四是安全软件拦截。这一点很难自查因为拦截往往悄无声息。表现是安装过程没有任何报错但服务注册失败或者 initdb 中途退出。临时关闭实时防护再装一次问题就消失了。装完之后记得把数据目录加入白名单否则运行期也会偶发文件访问失败。2.2 Linux 与国产化平台上的初始化、属主和 localeLinux 上包管理器安装通常很顺真正的问题出在“手动指定数据目录”和“换用户跑服务”这两件事上。最常见的报错是FATAL: data directory /var/lib/postgresql/16/main has wrong ownership。PostgreSQL 出于安全考虑拒绝以数据目录属主之外的用户身份启动也拒绝 root 直接跑initdb会明确提示cannot be run as root。正确做法是切换到 postgres 用户再操作sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /data/pg16/data -E UTF8 --localeC sudo chown -R postgres:postgres /data/pg16/data sudo chmod 700 /data/pg16/data数据目录权限必须是700这一点没有商量余地PostgreSQL 会主动检查。我见过有人为了“方便查看”把权限改成 755结果服务直接拒绝启动改回去就好了。第二个坑是 locale 缺失。某些精简版系统和部分国产化发行版默认只装了极少数 localeinitdb时会报invalid locale name。先用locale -a看一眼系统里有什么如果没有需要的安装对应的 locale 包或者干脆用--localeC别硬扛。第三个坑是 systemd 托管。手动pg_ctl start能起来、systemctl start postgresql却失败这种情况九成是 systemd 单元文件里的PGDATA或PGPORT与实际不一致。排查入口是systemctl status postgresql16-main journalctl -u postgresql16-main -n 100 --no-pager顺带说一句版本号。PostgreSQL 从 10 开始采用“主版本.小版本”的两段式命名比如 14.24 表示 14 这个主版本的第 24 个小版本修订。如果你看到类似14.24.2这种三段式写法那通常是第三方打包方自己的构建编号不是官方版本号。这个问题看着小但影响实际选型扩展包、驱动、备份工具的兼容性判断都得基于正确的版本号来做。2.3 扩展装不上以 pgvector 为例的通用排查链扩展安装失败几乎是所有 PostgreSQL 使用者都会遇到的一关不管是向量检索、全文检索还是地理信息类扩展报错形式高度相似。处理思路可以固化成一条链。第一步确认扩展文件有没有装到 PostgreSQL 能识别的位置。用pg_config看两个关键路径pg_config --sharedir # 控制文件 .control 应该在这里的 extension 子目录 pg_config --pkglibdir # 动态库 .so 应该在这里第二步确认版本匹配。pg_config输出的路径如果指向的是另一套 PostgreSQL比如系统自带的和你自己编译的那扩展会被装到错误的目录CREATE EXTENSION自然找不到。这是多版本共存环境里最高频的问题。第三步确认编译依赖。以源码编译方式安装向量扩展为例典型的流程是sudo apt install -y postgresql-server-dev-16 build-essential cd pgvector make sudo make installmake阶段报头文件找不到基本都是postgresql-server-dev-*没装或版本不对。第四步进库执行并检查CREATE EXTENSION vector; SELECT * FROM pg_available_extensions WHERE name vector;注意离线环境比如内网服务器需要提前把扩展源码、编译工具链和 PostgreSQL 开发包一起准备好。我一般是先在一台同版本的联网机器上把编译产物目录整体打包再拷到目标机器按相同路径释放比在目标机上现装工具链省事得多。3. 连接类错误日常排障里出现频率最高的一大类如果让我统计这几年的排障工单连接类问题能占到一半左右。原因也很容易理解应用和数据库之间隔着一层网络、一层认证配置、一层权限模型任何一层不一致都会表现为“连不上”而报错文本又往往只有一句话。处理这类问题的效率差异主要体现在“有没有按顺序排除”。我见过不少人从数据库内部开始查查了半天发现是防火墙没放开也见过有人一上来就改pg_hba.conf结果根因是应用连错了端口。下面按从外到内的顺序拆。3.1 五个高频连接报错逐条拆解第一类连接被拒绝。报错长这样connection to server at 10.0.0.5, port 5432 failed: Connection refused。注意关键词是 “Connection refused”它意味着 TCP 层就被拒绝了根本没走到数据库的认证流程。可能的原因有三个数据库没启动、监听地址不包含该网卡、或者防火墙挡了。判断顺序是先在服务器本地psql -h 127.0.0.1 -U postgres试一次本地能连说明实例是活的那问题就在监听或网络本地也连不上先去看服务状态。第二类no pg_hba.conf entry。完整的报错会带上主机、用户、数据库和 SSL 状态比如FATAL: no pg_hba.conf entry for host 10.0.0.21, user app, database appdb, SSL off这条信息其实已经把答案写出来了——pg_hba.conf里没有任何一条规则同时匹配这个来源地址、用户和库。要特别警惕的是很多人只改了pg_hba.conf却没重载配置改完自我感觉良好实际根本没生效。重载方式有两种SELECT pg_reload_conf();sudo -u postgres pg_ctl reload -D /data/pg16/data第三类密码认证失败。FATAL: password authentication failed for user app的表面含义很直白但真实原因有好几种。最常见的是加密方式不匹配PostgreSQL 10 引入了 SCRAM-SHA-25614 版本起新建用户的默认密码加密方式就是它。如果pg_hba.conf里写的是scram-sha-256而库里这个用户的密码是用老的md5方式存储的认证就会失败而且报错信息不会告诉你这个原因。修法是重新设一次密码-- 确认当前默认加密方式 SHOW password_encryption; -- 设为 scram-sha-256 后重设密码 SET password_encryption scram-sha-256; ALTER USER app WITH PASSWORD NewPassw0rd;另一种原因是老客户端驱动不支持 SCRAM。JDBC 需要在 42.2.10 以上版本才完整支持Go 的lib/pq也有类似限制。升级驱动通常比降级认证方式更安全别为了图省事把pg_hba.conf改成md5。第四类too many clients already。这是资源层问题后面第 5 节会详细讲参数估算这里先记住它对应的 SQLSTATE 是 53300而且它经常是连接泄漏的症状不是配置太小的症状。盲目调大max_connections只是把问题往后推迟。第五类库或角色不存在。FATAL: database appdb does not exist和FATAL: role app does not exist都属于这一类没什么技术含量但排查时容易忽略大小写。PostgreSQL 的标识符默认折叠为小写如果建库时用了双引号包裹大写名字后面不带引号就找不到这类问题在从 MySQL 迁移过来的团队里特别常见。3.2 pg_hba.conf 的匹配顺序与配置参数速查pg_hba.conf的核心规则只有一条从上往下逐条匹配命中第一条就停止不再继续往下看。这条规则解释了绝大部分“配置看着没问题但就是连不上”的案例——你在文件末尾加了一条允许规则但前面有一条范围更大的拒绝规则已经命中了。一个可用的模板长这样# TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all scram-sha-256 host appdb app 10.0.0.0/24 scram-sha-256 host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 host all all 0.0.0.0/0 reject几个容易踩的点值得单独说。local类型的连接走的是 Unix socketpsql不带-h参数时默认就是这种方式此时peer认证会拿操作系统用户名去比对数据库角色名所以sudo -u postgres psql能进换成普通用户就被拒这不是配置错了是 peer 机制的预期行为。ADDRESS字段的写法也有讲究10.0.0.0/24表示一个网段0.0.0.0/0在 IPv4 里表示全部地址但IPv6 连接不会匹配 IPv4 的规则服务器开了 IPv6 的话::1/128这条必须单独写。方法字段的选择上能用的值包括trust无认证只适合本地临时调试、peer、md5、scram-sha-256、cert、gss、ldap、radius、pam。生产环境里用scram-sha-256就行trust只在容器初始化和临时排障时用用完立刻改回来。3.3 监听地址、端口与容器场景的差异listen_addresses的默认值是localhost意味着只监听本地回环。远程连不上但本地正常第一件事就是查这个参数SHOW listen_addresses; SHOW port;改成*之后必须重启不是重载才能生效这一点和pg_hba.conf不一样容易记混。改完之后记得用ss -lntp | grep 5432确认监听状态我遇到过改完配置重启了但端口实际没起来的情况根因是另一处配置有语法错误postmaster 回退到了上一个可用配置。容器场景要额外注意两点。第一镜像里的pg_hba.conf默认只允许来自同一容器的连接映射端口到宿主机后从宿主机连进去的来源地址是 Docker 网关地址通常是172.17.0.1规则没覆盖就会报no pg_hba.conf entry。第二数据卷挂载到/var/lib/postgresql/data时如果宿主目录是新建的空目录初始化能正常走但如果目录里存在一个不完整的数据集容器会跳过初始化直接启动然后失败退出。用一个 compose 片段把关键点标出来services: db: image: postgres:16 environment: POSTGRES_PASSWORD: ChangeMe_2024 POSTGRES_DB: appdb PGDATA: /var/lib/postgresql/data/pgdata ports: - 5432:5432 volumes: - pgdata:/var/lib/postgresql/data healthcheck: test: [CMD-SHELL, pg_isready -U postgres -d appdb] interval: 10s timeout: 3s retries: 5 volumes: pgdata:这里把PGDATA指向数据卷下的子目录是为了避开某些文件系统上lostfound之类的残留文件干扰初始化。健康检查用pg_isready而不是psql因为这个命令不建立真实连接只探测服务状态开销极小适合高频调用。4. 权限与对象错误permission denied 的完整解法连上了、认证过了接下来遇到的就是权限问题。这类报错的特点是信息量少得可怜ERROR: permission denied for table orders只告诉你结果不告诉你缺哪一层权限。而 PostgreSQL 的权限模型是分层的缺任何一层都会失败。4.1 PostgreSQL 权限模型的三个层次我把这个模型拆成三层来理解排查时按层往下走。第一层是角色自身的属性。角色是否有LOGIN、SUPERUSER、CREATEDB、CREATEROLE这些是登录和建对象的前提。用下面的语句看一眼SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin FROM pg_roles WHERE rolname app;第二层是 schema 上的权限。这是最容易被忽略的一层。要在某个 schema 下访问对象你必须先拥有该 schema 的USAGE权限。数据库默认有publicschema而PostgreSQL 15 起收紧了publicschema 的默认权限普通用户不再默认拥有CREATE权限很多从 14 升级上来或者照着旧教程操作的人会在这里撞墙。报错形式是ERROR: permission denied for schema public对应的 SQLSTATE 是 42501。第三层才是对象本身的权限。表、视图、序列、函数各自有独立的权限位。序列这一项特别容易漏因为INSERT到带自增列的表里实际上还要有序列的USAGE权限报错会明确指向序列名但很多人第一眼看不出关联。4.2 高频权限报错与可直接抄的修复语句把三层理顺之后权限修复其实就是按需授权。下面这组语句覆盖了我日常处理的绝大部分场景顺序也很重要先 schema 再对象-- 1. 角色可登录 ALTER ROLE app WITH LOGIN; -- 2. schema 使用权 GRANT USAGE ON SCHEMA public TO app; -- 3. 按需给建表权15 及以上版本必须显式给 GRANT CREATE ON SCHEMA public TO app; -- 4. 已有对象的读写权 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app; -- 5. 让未来新建的对象自动继承权限这一步最容易被漏掉 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app;第 5 步是这套流程里最有价值的一步也是我踩过坑之后才养成的习惯。GRANT ... ON ALL TABLES只对执行那一刻已经存在的对象生效之后新建的表不会自动继承权限。表现就是开发同学今天加了一张表应用明天开始报permission denied而 DBA 查了半天发现权限配置“明明没问题”。用ALTER DEFAULT PRIVILEGES把这个缺口补上从此省心。有个细节要注意ALTER DEFAULT PRIVILEGES是按执行者身份生效的。也就是说这条语句必须以“将来建表的那个角色”的身份执行或者是管理员显式指定FOR ROLE 建表角色。执行者和建表者不一致的话规则不会生效。这是个非常隐蔽的坑我见过团队里照抄语句后仍然报权限错的案例根因就在这里。实操心得给应用账号授权时别图省事直接给 SUPERUSER。一旦应用被注入或者有代码逻辑缺陷超级用户权限意味着整库都能被删。生产环境里我一般遵循最小权限原则业务账号只给具体对象的增删改查外加必要时的序列使用权够用就行。5. 运行期高发问题锁、连接数、磁盘与日志过了安装和连接这两关系统跑起来之后问题会换一种形式出现不再是“进不去”而是“越来越慢”和“偶尔卡死”。这类问题的处理难度比前两类高因为它往往需要结合当前负载、历史趋势和参数配置一起看。我处理过的最耗时的一次线上故障根因是两个业务模块加锁顺序不一致导致的间歇性死锁日志里每半小时出现一次 40P01业务侧表现为随机超时。5.1 锁等待与死锁的定位与处置先分清两个概念。锁等待是正常的一个事务在等另一个事务释放行锁等一会儿就好了。死锁是异常的两个事务互相持有对方需要的锁谁也不肯先放必须由数据库主动介入杀掉其中一个事务报出 40P01。判断当前是不是卡在锁上我用的第一条 SQL 是这个SELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS running_time, left(query, 80) AS query_snippet FROM pg_stat_activity WHERE wait_event_type Lock ORDER BY running_time DESC;关键是wait_event_type Lock这个条件它能把等待锁的会话精准筛出来。如果查出一堆会话下一步是找“谁是阻塞源”。从 PostgreSQL 9.6 开始有个很方便的函数pg_blocking_pidsSELECT pid, pg_blocking_pids(pid) AS blocking_pids, state, left(query, 80) AS query_snippet FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;blocking_pids字段里的 PID 就是挡路的那些会话。找到之后先看它在干什么再决定是等、是pg_cancel_backend温和取消还是pg_terminate_backend强制断开。我的习惯是先取消因为取消只会中止当前查询不断开会话对连接池友好强制断开会让客户端收到连接中断连接池需要重建连接代价更大。至于死锁真正的根因排查要去读日志。发生死锁时PostgreSQL 会在日志里打印完整的加锁关系包括两个事务各自执行到哪条语句、在等哪把锁。看到这类日志基本就能定位到具体是哪两段业务逻辑加锁顺序反了。修法很简单让所有事务访问多张表时保持一致的顺序比如统一按表名字母序或者按主键大小序加锁冲突概率会大幅下降。5.2 连接数上限与 max_connections 的估算too many clients already这个报错最容易被误处理。很多人第一反应是把max_connections从 100 调到 500改完当时确实不报了但一两周后数据库整体变慢。原因是每个连接在 PostgreSQL 里都是一个独立进程都会占用内存连接数上去之后内存压力和上下文切换开销同步上升。我的做法是先判断这是“配置不够”还是“连接泄漏”。查一下当前连接来源分布SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity GROUP BY usename, application_name, client_addr ORDER BY count(*) DESC;如果发现某个应用占了绝大部分连接而且都是idle状态那就是连接池配得过大或者有连接没归还该去改应用侧不是改数据库侧。反过来如果所有来源的连接数都合理、总量确实接近上限再考虑调整参数。参数估算我一般这么算max_connections 应用连接池峰值之和 运维预留 其中 应用侧3个微服务 × 每服务池上限20 60 后台任务与报表2个任务 × 10 20 管理连接DBA、备份、监控 10 安全余量约总量的 15% 约 14 -------------------------------------------------- 合计 ≈ 104这个数字算出来之后再看内存能不能撑住。每个连接的基础内存开销加上work_mem可能的放大倍数才是真实占用粗略估算方式是最坏情况内存 ≈ max_connections × work_mem × 每个查询最多哈希/排序节点数假设work_mem设成 4MB一个复杂查询里可能同时有 3 个排序或哈希节点那么单连接最坏就是 12MB100 个连接就是 1.2GB。这个数字如果超过了服务器内存的合理比例就要把work_mem降下来而不是硬扛。实践中work_mem设太大会在多并发场景下引发 OOM这个坑我踩过一次教训很深。还有一个容易被忽略的点PostgreSQL 会为超级用户预留一部分连接由superuser_reserved_connections控制默认 3。预留的意义是当普通连接把池子占满时管理员还能连进去做紧急处置。所以实际可用给应用的连接数是max_connections - superuser_reserved_connections配连接池的时候要按这个数字算别按max_connections算。5.3 WAL 膨胀、磁盘写满与只读状态磁盘写满是个典型的“平时没感觉一旦发生就全线瘫痪”的问题。PostgreSQL 在磁盘空间不足时会主动把数据库切成只读状态新写入直接报错SQLSTATE 是 25006 或 53100。这时候连删除数据的操作都可能执行不了因为删除也要写 WAL。容易撑爆磁盘的通常是三样东西WAL 日志、临时文件、表和索引膨胀。WAL 的归档清理依赖复制槽如果建了一个逻辑复制槽但消费端早就下线了这个槽会一直保留 WAL 文件不放磁盘一点点被吃满。排查语句是SELECT slot_name, slot_type, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;active为 false 且retained_wal很大的槽基本可以判定是废弃的确认业务上确实不需要之后删掉它磁盘空间立刻就回来了。临时文件的问题则是work_mem不够时溢写到pgsql_tmp目录导致的日志里打开log_temp_files 0之后每个临时文件的生成都会被记录配合时长和大小信息很容易定位到是哪条 SQL 在频繁溢写。表膨胀这一项要交给 autovacuum。如果发现某张表的死元组比例长期很高先看 autovacuum 有没有被阻塞。长事务会阻止 vacuum 回收空间一个跑了几个小时的只读长事务能让整库的清理工作全部卡住。这种情况在报表任务里很常见解决办法是给所有长事务设置合理的statement_timeout或者idle_in_transaction_session_timeout。6. 我常用的排查工具与配置清单前面五节讲的是“遇到问题怎么办”这一节讲“怎么让问题自己暴露出来”。这部分的价值在于前置投入小、长期收益大属于典型的一次配置长期受益。我接手一套新实例时前两件事一定是看日志配置和确认排查入口因为这两件事决定了后面所有问题的处理效率。6.1 日志配置让问题自己说话默认的日志配置信息量太少出问题时只能靠猜。下面这组参数是我在大多数生产实例上会开的兼顾信息完整和磁盘占用logging_collector on log_directory log log_filename postgresql-%Y-%m-%d.log log_min_duration_statement 200ms log_checkpoints on log_connections on log_disconnections on log_lock_waits on log_temp_files 0 log_autovacuum_min_duration 1s log_line_prefix %m [%p] %q%u%d/%a 逐条说下理由。log_min_duration_statement 200ms记录所有超过 200 毫秒的语句这个阈值在 OLTP 场景里比较合适再低日志会太吵。log_lock_waits打开之后任何等待时间超过deadlock_timeout默认 1 秒的锁等待都会被记录这是排查锁问题的第一手资料。log_temp_files 0记录所有临时文件用于定位work_mem不足。log_line_prefix里的%m是带毫秒的时间戳%p是进程号%q会终止非会话进程的输出%u%d/%a是用户、库名和应用名——这几个字段在关联客户端行为时非常有用尤其是%aapplication_name它能让日志和具体服务一一对应上。这里的%a有个前提客户端要主动设置application_name。JDBC 可以在连接串里加ApplicationNameorder-service连接池也支持在初始化时统一设置。设好之后日志里一眼就能看出是哪个服务在捣乱比靠 IP 猜靠谱得多。注意log_connections和log_disconnections在连接数极高的系统上会产生大量日志磁盘写入压力不小。如果实例 QPS 很高可以考虑只开log_disconnections或者干脆都关掉转而依赖连接池自己的监控指标。6.2 手边常备的排查 SQL除了前面提到的锁和连接查询还有几条 SQL 我几乎每周都会用。整理成一张表方便直接抄用途关键视图或函数关注字段找最耗时的活跃查询pg_stat_activityquery_start、state找阻塞关系pg_blocking_pids(pid)阻塞者 PID 列表看表的大小和膨胀pg_total_relation_size、pg_stat_user_tablesn_dead_tup、n_live_tup看索引是否被用pg_stat_user_indexesidx_scan长期为 0 可考虑清理查缓存命中率pg_stat_databaseblks_hit / (blks_hit blks_read)看复制槽占用pg_replication_slotsactive、滞留 WAL 量查长事务pg_stat_activityxact_start与当前时间差缓存命中率这条特别值得盯。正常情况下应该在 99% 以上如果掉到 95% 以下说明大量请求在走磁盘shared_buffers可能偏小或者有全表扫描把缓存冲掉了。shared_buffers的经验值是物理内存的 25%但这个比例在内存特别大的机器上不必严格遵循超过 8GB 之后收益递减剩下的内存留给操作系统页缓存效果更好。索引那一条也是我经常处理的场景。idx_scan长期为 0 的索引不但没用还会拖慢写入因为每次 INSERT 都要同步维护它。但删索引前必须谨慎有些索引只在月末报表或者年度结算时才被用到idx_scan在短期内看着是 0。稳妥做法是观察一个完整的业务周期比如覆盖一次月度结算再做决定。6.3 驱动、客户端与版本匹配的细节最后说一个不常被提起但很关键的方面客户端与服务端的版本匹配。PostgreSQL 的服务端和客户端是向后兼容的但兼容不等于没有坑。JDBC 驱动方面42.2.10 之前的版本不支持 SCRAM-SHA-256在老项目里升级 PostgreSQL 服务端之后经常遇到认证失败根因就在驱动版本。Python 的psycopg2在高并发场景下如果用psycopg2的同步接口配合大量线程会出现连接占用问题换成psycopg3或者配连接池会好很多。Go 生态里lib/pq已经进入维护状态新项目更推荐pgx它对 SCRAM、sslmodeverify-full、批量操作的支持都更完善。版本号读法也值得再强调一次。服务端 16.x 的实例配 16 系列的扩展包和驱动不要把 15 的二进制扩展装到 16 上版本不匹配时CREATE EXTENSION可能成功、但运行时加载动态库报符号错误这类问题的排查成本极高因为报错信息指向的是加载失败而不是版本冲突。另外图形化客户端连接服务端时如果客户端版本远高于服务端某些新特性相关的功能会报错反过来服务端远高于客户端时一些新类型在客户端里显示不出来。团队里维持客户端和服务端大版本接近是个成本很低收益不错的做法。我在实际维护中体会最深的一点是PostgreSQL 的报错信息其实相当诚实它几乎不会骗你问题往往在于我们只看了主消息而忽略了 DETAIL、HINT 和日志。把这几个信息源串起来用配合前面说的四层分阶段思路大部分问题都能在半小时内定位到。反倒是那些跳过信息收集、直接改参数的操作最容易把小问题拖成大事故。