企业官网建设流程全解析

接手一套 PostgreSQL 环境，第一件事不是急着建索引，而是先把几个基础信息摸清楚：数据目录在哪、运行日志写到哪里、日志文件已经占了多大、当前数据库的 WAL 写到哪个位置了。这四个问题看起来零散，其实都围绕着同一个核心——日志与存储。数据目录和运行日志相关配置决定了告警时要往哪看，日志文件大小告诉你磁盘会不会在半夜挂掉，而当前 LSN 则是理解主从复制、备份恢复的钥匙。这篇记录把 pgsql 排查中最常用的命令整理一遍，顺便讲讲我实际环境里踩过的坑，适合刚上手数据库的运维和开发同学，也适合写监控脚本时不确定用哪个函数的人直接翻对照。

1. 数据目录：先定位，才能不迷路

1.1 一条 SQL 看数据目录

连接上数据库之后，最快确认数据目录的方式就是执行SHOW data_directory;，这是 PostgreSQL 里我用得最多的巡检命令之一。

SHOW data_directory;

返回结果可能是/var/lib/postgresql/15/main、/opt/pgsql/data这类绝对路径。只要拿到这个路径，后面查日志、查 WAL、估算磁盘占用就都有了坐标。注意别只看 psql 启动时写的-D参数，那只是启动时的指定位置，实际运行中可能被 postgresql.conf 里的data_directory覆盖，所以用SHOW data_directory;拿到的才是当前实例真正在用的路径。

从操作系统层面还可以用pg_controldata做交叉验证，这个命令不需要连接数据库，直接用 postgres 系统用户执行即可：

sudo -u postgres pg_controldata /var/lib/postgresql/15/main | sed -n '1,10p'

输出里的system identifier、Latest checkpoint location都是重要的全局信息，和后面要说的 LSN 是同一套体系。如果在维护多实例的机器上，尤其是同一台服务器同时跑了多个 PostgreSQL 版本或实例，先用SHOW data_directory;确认自己连的是哪个实例，能避免很多“改错库”的事故。

1.2 数据目录里有哪些“大块头”

拿到数据目录之后，下一步通常是看目录下到底谁占的磁盘最多。数据目录的结构比较固定，几个关键子目录一定要认识：

如果想快速看占用排序，直接在服务器上执行：

sudo du -sh /var/lib/postgresql/15/main/* | sort -rh | head -20

这个命令比ls -lh更可靠，因为ls看到的单文件大小不包含硬链接和目录内递归累计。实际排障时，我见过很多同事对着ls -lh里的base目录发呆，其实du -sh一敲就知道哪个库是“磁盘杀手”。如果某个库占用异常，再进到base/<数据库 OID>里用find配合ls -lS找大表、大索引。

1.3 日志目录和数据目录千万别搞混

我刚开始接触 PostgreSQL 的时候，想当然地以为运行日志就在数据目录下，结果翻遍base都没有找到postgresql.log。后来才知道，数据目录和日志目录是两个概念：数据目录存的是数据库物理文件，运行日志目录由log_directory参数决定，默认值一般是相对数据目录的log，但很多发行版或云数据库会把它改成/var/log/postgresql这样的绝对路径。

所以排查日志之前，先执行一条 SQL：

SHOW log_directory; SHOW logging_collector;

log_directory告诉你日志写在哪个目录，logging_collector告诉你日志收集器是否开启。如果logging_collector=off，那日志不会写入 log_directory，而是直接打到启动 PostgreSQL 的那个进程的标准错误输出，可能是终端、也可能是 systemd journal。想当然地去找.log文件，大概率什么也找不到。

建议在每台新接手的主机上，把SHOW data_directory;和SHOW log_directory;当成一组固定命令连着敲，先把“家底”确认清楚，再往下排查才有意义。

2. 运行日志相关配置：参数决定日志往哪写

2.1 看懂核心配置项

PostgreSQL 的日志参数分散在postgresql.conf里，很多新手一上来就被十几个参数搞晕。实际高频用到的其实就这几个，我先给一张速查表：

log_destination如果包含csvlog，会额外生成一份带逗号分隔的 CSV 日志，方便导入分析工具，但 CSV 日志和普通日志会同时存在，磁盘占用会翻倍。我在生产环境一般只留stderr，因为文本日志更直观，配合log_line_prefix和log_statement足够定位绝大多数问题。

2.2 查询当前配置，判断能否热加载

配置文件改完到底要不要重启，这是群里问得最多的问题。不要凭记忆猜，直接查pg_settings的context列：

SELECT name, setting, unit, context, source FROM pg_settings WHERE name IN ( 'logging_collector', 'log_destination', 'log_directory', 'log_filename', 'log_rotation_age', 'log_rotation_size', 'log_truncate_on_rotation', 'log_statement', 'log_line_prefix' );

context字段的值表示参数生效时机：postmaster表示必须重启实例，sighup表示修改配置文件后执行pg_reload_conf()即可，superuser表示有超级用户权限时可以直接ALTER SYSTEM然后 reload，user表示普通会话内可以用SET临时修改。

举个例子，临时开启 SQL 审计，不需要重启：

ALTER SYSTEM SET log_statement = 'all'; SELECT pg_reload_conf(); SHOW log_statement;

这时候日志就会开始记录所有 DDL 和 DML SQL。但要注意ALTER SYSTEM只会写入postgresql.auto.conf，如果哪天需要还原，用ALTER SYSTEM RESET log_statement;再 reload 一次就行。如果用的是云数据库，很多实例没有超级用户权限，ALTER SYSTEM会被拒绝，这时候只能通过控制台的参数组修改，再等待配置下发生效。

2.3 日志行前缀的经验推荐

日志行前缀log_line_prefix是一个容易被忽略但极其重要的参数。默认值可能只有一个时间，出了问题根本不知道是哪台机器、哪个用户、哪个应用发的请求。我一般会改成这样：

ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d from %h ';

含义分别是%m毫秒级时间戳、%p进程号、%u用户名、%d数据库名、%h客户端 IP。这样在分析慢日志和错误信息时，一眼就能看到“谁在什么时候从哪个 IP 登录了哪个库”。如果你是看日志文件来做安全审计，这行前缀能直接帮你把多个应用混在一起的问题拆开。

注意%h在本地 Unix Socket 连接时可能为空，不影响阅读。还有一个%q选项可以在备库上显示特殊前缀，但涉及流复制场景时再配置也不迟，普通单机环境没必要增加复杂度。

3. 日志文件大小：统计、轮转与清理

3.1 先查日志目录和文件大小

日志文件大小是磁盘告警里最常被忽视的元凶。很多人眼里只有数据目录和 WAL，结果某天磁盘突然满了，一查发现/var/log/postgresql下躺着几十 GB 的旧日志。

先定位日志目录，再统计大小：

# 进入数据目录上下文，按默认相对路径结构统计 sudo du -sh "$(sudo -u postgres psql -Atc 'SHOW data_directory;')/log" # 看目录下文件的大小和修改时间 sudo ls -lht "$(sudo -u postgres psql -Atc 'SHOW data_directory;')/log" | head -20

如果是绝对路径的日志目录，直接替换路径即可。我建议把du -sh和ls -lht组合使用：du看总量，ls看最活跃的日志文件。很多环境里“单个日志文件已经 500MB”比“总共占用 5GB”更危险，意味着日志轮转配置可能已经失效。

3.2 不登服务器，通过 SQL 看日志大小

有时候你只有数据库连接权限，没有服务器 Shell，那也有办法。PostgreSQL 提供了两个能“跨到服务器文件系统”的函数：pg_ls_dir列出目录内容，pg_stat_file返回文件元信息。只要log_directory是相对路径（默认就是log），可以在 SQL 里直接这样查：

SELECT f.filename, (pg_stat_file('log/' || f.filename)).size AS bytes, (pg_stat_file('log/' || f.filename)).modification AS modified FROM pg_ls_dir('log') AS f(filename) WHERE f.filename LIKE '%.log' ORDER BY modified DESC;

执行结果能直接看到每个日志文件名、字节数和最后修改时间。pg_stat_file的字段是复合类型，用括号括起来再取子字段，语法不要搞错。如果log_directory配置成了绝对路径，pg_ls_dir会受限于数据目录范围，没法读到外部绝对路径，这时候要么拿到服务器权限，要么只依赖日志名规律用历史经验判断。

有一说一，这个 SQL 方案更适合写监控脚本，比如每 5 分钟采集一次日志目录大小，超过阈值就告警。它不需要 SSH 密钥，也避免了巡检机器人到处开权限，安全边界更清晰。

3.3 轮转参数与“文件越来越大”的坑

很多环境日志文件越来越大，不是业务量太大，而是轮转没生效。最典型的坑就是log_truncate_on_rotation = off。

默认情况下，PostgreSQL 轮转日志时会生成一个新文件名，比如postgresql-2025-01-10_000000.log。但如果某个运维同事手动把log_filename改成了固定的postgresql.log，那么每次轮转或重启，同名文件被打开时默认是追加写模式，旧的没清、新的继续写，文件自然无限膨胀。这时候只要把log_truncate_on_rotation改成on，轮转后同名文件会被清空重写，空间能立刻释放，但代价是旧内容被截断，最好先备份。

我建议的配置是保留默认的日期时间文件名模板，或者至少把日期放进去，然后设置双重轮转：

ALTER SYSTEM SET log_rotation_age = 1440; ALTER SYSTEM SET log_rotation_size = '200MB'; SELECT pg_reload_conf();

这样即使某天日志轮转进程卡住，单文件超过 200MB 也会强制切换。别小看双重轮转，我实际遇到过排查到凌晨才发现日志轮转没跑起来的情况，要不是配置了大小轮转，磁盘早就爆了。

3.4 手动轮转、压缩与删除的正确姿势

临时要清理日志，最忌讳直接rm当前正在写的那个文件。因为 PostgreSQL 进程已经持有了该文件的文件描述符，你删除之后空间不会立刻释放，新的日志还是会继续往已删除的 inode 里写，直到进程切换日志文件。所以第一步永远是先轮转：

SELECT pg_rotate_logfile();

这个函数会通知日志收集器切换一个新文件，返回true表示成功。如果logging_collector=off，它会返回false，这时候你只能到系统层面处理，比如journalctl日志。

轮转完成后，再对旧日志做压缩归档，例如保留最近 7 天：

cd "$LOG_DIR" find . -name "*.log" -mtime +7 -print find . -name "*.log" -mtime +7 -exec gzip {} \;

如果想彻底释放早期日志占用的磁盘，压缩确认完成后把旧的.gz再按保留策略删除。注意压缩动作的时机，千万别一边压缩一边让新日志往同一目录写，压缩前先确认没有应用正在 tail 这个日志。

还有一个小技巧：如果日志文件已经巨大，轮转后旧文件依然占空间，可以先用gzip压缩再删，但别以为rm掉就好，确认du已经降下来再继续操作。

4. 当前 LSN：理解 WAL 的指针，监控复制延迟

4.1 LSN 是什么

LSN（Log Sequence Number，日志序列号）是 PostgreSQL 中用来标识 WAL 日志位置的数字，可以理解成一本书的“页码加行号”。每次事务提交、每次写入 WAL 记录，LSN 都会单调递增。它的显示格式类似16/2B4AC11，前半部分是“段号”，后半部分是“段内偏移”，用十六进制表示。

LSN 之所以重要，是因为它把物理 WAL 文件、逻辑复制位置、主备延迟、备份恢复起点统一成了一根尺子。比如pg_basebackup备份时会告诉你起始 LSN；备库回放时也有自己的replay_lsn；两个 LSN 一对比，就知道备库落后主库多少字节。理解 LSN 之后，监控复制延迟就不再是黑盒了。

4.2 常用查询函数

PostgreSQL 10 之后，查询当前 LSN 的函数名统一变成了pg_current_wal_*系列，老版本用的是pg_current_xlog_*。下面这条 SQL 可以同时看插入点、写入点和刷盘点：

SELECT pg_current_wal_lsn() AS current_lsn, pg_current_wal_insert_lsn() AS insert_lsn, pg_current_wal_flush_lsn() AS flush_lsn;

简单区分一下：

• pg_current_wal_lsn()：当前正在写入的 WAL 位置，可以认为是最新的日志记录位置。
• pg_current_wal_insert_lsn()：事务日志插入缓冲区的位置，通常和上一个很接近。
• pg_current_wal_flush_lsn()：已经刷到磁盘上的 WAL 位置，只有到了这里才真正持久化。

平时看主库健康度，直接看pg_current_wal_flush_lsn()更有意义，因为它代表已经落盘的持久化进度。老版本里的对应函数可以按这个表对号入座：

如果环境还没升级，写完 SQL 后发现函数不存在，不要慌，去查一下版本再换对应的老函数。

4.3 用 LSN 做备份恢复和主从监控

LSN 最常见的实际用途之一，是把 LSN 转成对应的 WAL 文件名。比如：

SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

返回结果是类似00000001000000160000002B的 24 位文件名。这能直接告诉你当前 WAL 在哪个文件里，配合pg_switch_wal()可以主动切换日志段，方便手工归档。

主从延迟的监控更直接。在主库上执行下面的查询，能同时看到每个备库的网络发送、写入、刷盘、回放四个维度的延迟：

SELECT application_name, state, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) AS write_lag, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) AS flush_lag, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag FROM pg_stat_replication;

这里pg_wal_lsn_diff()返回两个 LSN 相差的字节数，是数值类型，所以可以直接pg_size_pretty()转成可读大小。注意千万别直接拿两个 LSN 字符串相减，那结果纯属乱来。

在备库上，可以用pg_last_wal_receive_lsn()和pg_last_wal_replay_lsn()来统计接收和回放之间的积压：

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS receive_replay_gap;

如果这个值持续增长，说明备库的磁盘 IO 或 CPU 跟不上主库写入速度，该扩容或者优化回放了。

4.4 使用 LSN 的几个坑

先说最容易被坑的一点：不要试图手动比较两个 LSN 字符串的大小。LSN 本质是 64 位整数，显示成段/偏移是为了给人看，真要比较大小和差值全部交给pg_wal_lsn_diff()，它返回的才是可靠字节数。

另一个坑是版本差异。如果代码里写的是pg_current_xlog_location()，在 PG10 以上直接报函数不存在；反过来，如果监控系统还在 PG9.6，写pg_current_wal_lsn()同样不行。写跨版本兼容脚本时，最好先执行SHOW server_version;做版本判断。

还有一个理念上的坑：LSN 不是“事务 ID”，它标识的是 WAL 写入位置，不是逻辑顺序号。同一个事务可能产生多条 WAL 记录，多个事务也都会推进 LSN。所以分析问题时，记得 LSN 只能告诉你“日志写到哪了”，不能替代事务 ID 去理解事务先后或锁冲突顺序。

5. 实用速查与排障实录

5.1 命令速查表

把上面涉及的命令汇总成一张表，方便日常贴到笔记里：

建议把这几个命令写成一个psql脚本，每次巡检执行一次，输出不超过一屏，非常高效。

5.2 日志不生成或写到了别处

如果你设置了日志参数，但就是找不到日志文件，按下面的顺序排查：

SHOW logging_collector; SHOW log_destination; SHOW log_directory;

logging_collector=off是第一大原因。此时没有独立日志文件，只有启动 postgres 进程的标准错误输出。在 systemd 管理的环境里，用journalctl -u postgresql-* --since '30 minutes ago'就能看到实时日志；如果是从终端手动启动的，日志就在那个终端里。

确认 collector 开启后，检查目录权限。PostgreSQL 服务进程通常以postgres用户运行，日志目录如果被 root 或者别的用户创建，进程可能没权限写入。这时用ls -ld看目录权限，确认属主是否为 postgres。我遇到过客户迁库时把整个目录chown成业务用户，导致 PG 启动后静默不写日志，折腾半天的教训。

5.3 磁盘被 WAL 撑满，先看归档

LSN 和 WAL 关系最紧密的故障之一，就是 WAL 堆积导致磁盘写满。如果数据目录下的pg_wal占用异常，先看是不是归档卡住了：

SELECT * FROM pg_stat_archiver;

重点看failed_count是否持续增加，以及last_failed_wal是不是停在一个旧文件名上。归档命令如果一直失败，WAL 段不会被清理，pg_wal就会无限增长。

在需要快速恢复服务时，可以手动切换并触发归档：

SELECT pg_switch_wal();

再配合pg_ls_waldir()查看目录下的文件：

SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 20;

如果确认归档配置短期内修不好，临时办法是调大wal_keep_size或检查归档命令的存储目标是否满了。但要注意，这只能缓解，最终还是要修复归档链路，否则过两天又会被 WAL 撑爆。

5.4 巡检脚本与习惯

最后分享一个我自己的习惯。接手任何一套 PostgreSQL，我都会先做一轮“身份确认”：

psql -U postgres -h 127.0.0.1 -p 5432 -Atc " SELECT 'data_dir=' || current_setting('data_directory'); SELECT 'log_dir=' || current_setting('log_directory'); SELECT 'lsn=' || pg_current_wal_lsn(); "

然后立刻去看日志目录大小和最近有没有失败轮转。这套组合拳执行下来，基本能在 5 分钟内了解一个实例的存储和日志状态。后续如果要写监控告警，优先采集日志目录大小、WAL 目录大小、pg_stat_archiver的失败计数、主库和备库的 LSN 差值，这四个指标能覆盖大部分和日志、复制有关的磁盘故障。

我个人在实际操作中的体会是：数据目录和日志目录这类“地基信息”越早确认越好，千万别等出了问题才临时翻配置文件。每次收尾前顺手执行一遍SHOW data_directory;、SHOW log_directory;、SELECT pg_current_wal_lsn();，已经成了我处理 PostgreSQL 的肌肉记忆，希望你也能把这几个命令变成自己的第一反应。