PostgreSQL 常用运维指令手册

适用版本：PostgreSQL 13 / 14 / 15 / 16 / 17 / 18（19 于 2026-11-19 GA） 约定：# root 或 sudo，$ 普通用户，postgres=# psql 提示符，<xxx> 为占位符。 差异标注：正文用 > **版本差异** 引用块标注跨版本不一致之处。 更新时间：2026-11

目录

1. 版本与支持周期
2. 下载地址与安装
3. 服务启停与初始化
4. psql 客户端与元命令
5. 配置文件与参数
6. 账号、权限与认证
7. 库表日常操作
8. 备份与恢复
9. WAL、归档与 PITR
10. 流复制与逻辑复制
11. 日志体系
12. 性能诊断与优化
13. VACUUM、膨胀与事务 ID
14. 空间与容量管理
15. 升级与迁移
16. 高频故障速查
17. 危险命令与避坑清单
18. 附录：巡检脚本与速查

1. 版本与支持周期

1.1 发布节奏

PostgreSQL 每年 9~10 月发一个大版本，之后每季度发一次小版本（2、5、8、11 月）。每个大版本支持 5 年，第 5 年后停止所有修复。

关键：PostgreSQL 的大版本升级不能原地替换二进制，必须 pg_upgrade 或 dump/restore。数据目录格式与版本号强绑定，直接换 binary 会拒绝启动。这是与 MySQL 最大的运维差异。

1.2 版本识别

psql --version
postgres --version
pg_config --version

SELECT version();
SHOW server_version;
SHOW server_version_num;            -- 180006 形式，脚本判断更方便
SELECT current_setting('server_version_num')::int >= 160000 AS is_16_plus;

2. 下载地址与安装

2.1 官方入口总表

国内镜像（同步 PGDG 仓库，路径以镜像站实际为准）：

# 清华 TUNA
https://mirrors.tuna.tsinghua.edu.cn/postgresql/
# 中科大
https://mirrors.ustc.edu.cn/postgresql/

2.2 RHEL / Rocky / Alma / Oracle Linux：YUM 安装

# 1) 安装 PGDG 仓库包（EL9 示例，EL8/EL10 改 URL 中的版本号）
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 2) 禁用系统自带 postgresql 模块，否则装到的是老版本
dnf -qy module disable postgresql

# 3) 安装服务端（包名带大版本号）
dnf install -y postgresql18-server postgresql18-contrib

# 4) 初始化数据目录
/usr/pgsql-18/bin/postgresql-18-setup initdb

# 5) 启动并设开机自启（服务名带版本号）
systemctl enable --now postgresql-18

# 6) 验证
sudo -u postgres psql -c "SELECT version();"

注意：PGDG 的包名和服务名都带大版本号（postgresql18-server、postgresql-18.service），因此同一台机器可以并存多个大版本，端口与数据目录需自行区分。这与 MySQL 单实例包的做法不同。

安装客户端与扩展：

dnf install -y postgresql18 postgresql18-libs postgresql18-devel
dnf install -y postgresql18-contrib postgresql18-server-devel

2.3 Ubuntu / Debian：APT 安装

# 1) 导入 PGDG 源（脚本会自动识别发行版代号）
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

# 2) 安装
sudo apt update
sudo apt install -y postgresql-18 postgresql-client-18 postgresql-contrib-18

# 3) 集群管理（Debian 系特有）
pg_lsclusters                       # 列出所有集群、版本、端口、状态
pg_ctlcluster 18 main start
pg_ctlcluster 18 main status

Debian 系用 pg_createcluster / pg_dropcluster 管理集群，配置文件在 /etc/postgresql/<版本>/<集群名>/，数据目录在 /var/lib/postgresql/<版本>/<集群名>/。

pg_createcluster 18 second --port 5433
pg_ctlcluster 18 second start
pg_dropcluster 18 second --stop     # 危险，删数据

2.4 源码编译安装

dnf install -y gcc gcc-c++ make readline-devel zlib-devel openssl-devel \
  libicu-devel bzip2-devel libxml2-devel libxslt-devel

./configure --prefix=/usr/local/pgsql18 \
  --with-openssl --with-icu --with-libxml --with-libxslt --with-zstd --with-lz4
make -j$(nproc) && make install

useradd -r -m -s /bin/bash postgres
mkdir -p /data/pg18 && chown postgres:postgres /data/pg18
sudo -u postgres /usr/local/pgsql18/bin/initdb -D /data/pg18 -E UTF8 --locale=C.UTF-8

2.5 Docker 部署

docker run -d --name pg18 \
  --restart unless-stopped \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD='Str0ng#Pass' \
  -e POSTGRES_DB=appdb \
  -e POSTGRES_INITDB_ARGS="--encoding=UTF8 --locale=C.UTF-8" \
  -e TZ=Asia/Shanghai \
  -v /data/pg18:/var/lib/postgresql/data \
  -v /data/pg18_conf:/etc/postgresql \
  postgres:18

docker logs -f pg18
docker exec -it pg18 psql -U postgres -d appdb

注意：官方镜像的数据目录在 PG 18 起改为 /var/lib/postgresql（旧版为 /var/lib/postgresql/data），挂载前先看镜像文档，挂错位置等于没持久化。

2.6 Windows 安装

用 EDB 的图形安装包（含 pgAdmin 与 Stack Builder），安装时指定端口、数据目录、locale、超级用户密码。也可用 ZIP 二进制包手工 initdb。

# ZIP 方式
initdb -D "C:\pgdata" -U postgres -E UTF8 --locale=C
pg_ctl -D "C:\pgdata" -l "C:\pgdata\logfile.log" start
psql -U postgres

2.7 初始化后的必做四步

# 1) 设 postgres 超级用户密码
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'Str0ng#Pass';"

-- 2) 确认认证方式与密码加密算法
SHOW password_encryption;                 -- 期望 scram-sha-256
SELECT * FROM pg_settings WHERE name='password_encryption';

-- 3) 确认字符集与排序规则
SELECT datname, pg_encoding_to_char(encoding) enc, datcollate, datctype FROM pg_database;

-- 4) 确认监听与端口
SHOW listen_addresses;
SHOW port;

# 5) 放行网络（RHEL 系）
firewall-cmd --add-service=postgresql --permanent && firewall-cmd --reload

3. 服务启停与初始化

3.1 systemd（RHEL 系 PGDG）

systemctl start postgresql-18
systemctl stop postgresql-18
systemctl restart postgresql-18
systemctl reload postgresql-18          # 等价 pg_ctl reload，SIGHUP
systemctl status postgresql-18
systemctl enable postgresql-18
journalctl -u postgresql-18 -n 200 --no-pager

3.2 pg_ctl（通用，源码安装必用）

# 必须用 postgres 用户执行，root 不允许启动
sudo -u postgres pg_ctl -D /data/pg18 start
sudo -u postgres pg_ctl -D /data/pg18 -l /data/pg18/server.log start
sudo -u postgres pg_ctl -D /data/pg18 stop -m fast
sudo -u postgres pg_ctl -D /data/pg18 restart -m fast
sudo -u postgres pg_ctl -D /data/pg18 reload
sudo -u postgres pg_ctl -D /data/pg18 status
sudo -u postgres pg_ctl -D /data/pg18 promote      # 备库提升为主库

停止模式对比：

危险：stop -m immediate 会让下次启动进入恢复流程，虽然数据一般安全，但恢复时间不可控。非卡死不要用。

3.3 初始化数据目录

# 标准初始化
sudo -u postgres initdb -D /data/pg18 \
  -E UTF8 --locale=C.UTF-8 \
  --auth-local=peer --auth-host=scram-sha-256 \
  -U postgres

# 带 WAL 归档目录的规划
mkdir -p /data/pg18/{pg_wal_archive,log}
chown -R postgres:postgres /data/pg18

建议：生产环境用 --locale=C.UTF-8 或 --locale=en_US.UTF-8 + --lc-collate 单独控制。不要用系统默认的中文 locale 建库，排序规则会带来跨平台不一致，后续迁移很痛苦。

3.4 多实例

每个大版本可以并存，同一版本多实例靠不同 PGPORT + 不同数据目录：

sudo -u postgres initdb -D /data/pg18_2 -E UTF8 --locale=C.UTF-8
sudo -u postgres pg_ctl -D /data/pg18_2 -o "-p 5433 -k /tmp" -l /data/pg18_2/server.log start

systemd 多实例用模板单元：

systemctl edit --force --full postgresql-18@.service
systemctl enable --now postgresql-18@pg18_2

4. psql 客户端与元命令

4.1 连接

psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
psql -h 127.0.0.1 -U app -d appdb -W            # 强制提示密码
psql "postgresql://app:pass@10.0.0.11:5432/appdb?sslmode=require"
psql -U postgres -c "SELECT now();"              # 单条执行
psql -U postgres -f init.sql                     # 执行脚本
psql -U postgres -v ON_ERROR_STOP=1 -f init.sql  # 出错即停，脚本必加
PGPASSWORD='xxx' psql -h 10.0.0.11 -U app -d appdb -c "select 1"

环境变量免交互：

export PGHOST=10.0.0.11
export PGPORT=5432
export PGUSER=app
export PGDATABASE=appdb
export PGPASSWORD=''            # 生产建议用 .pgpass 而不是环境变量

~/.pgpass（权限必须 600）：

10.0.0.11:5432:appdb:app:App#2026
*:*:*:postgres:Postgres#2026

4.2 元命令速查

4.3 导入导出（\copy 与 COPY）

-- 服务端执行，要求文件在服务端且 postgres 用户可读
COPY appdb.orders TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);
COPY appdb.orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

-- 只导部分列
COPY appdb.orders (id, user_id, amount) TO STDOUT WITH CSV HEADER;

# 客户端执行，文件在本地，最常用
psql -U app -d appdb -c "\copy orders TO '/tmp/orders.csv' CSV HEADER"
psql -U app -d appdb -c "\copy orders FROM '/tmp/orders.csv' CSV HEADER"

# 用管道压缩，避免落盘
psql -U app -d appdb -c "\copy (SELECT * FROM orders) TO STDOUT CSV" | gzip > orders.csv.gz

注意：COPY 走服务端、\copy 走客户端。用 COPY 报 must be superuser or have privileges of pg_read_server_files 时，不是权限给错了，而是该用 \copy。

5. 配置文件与参数

5.1 文件位置与职责

RHEL PGDG：/var/lib/pgsql/18/data/；Debian：/etc/postgresql/18/main/。

sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"
sudo -u postgres psql -c "SHOW data_directory;"

5.2 参数查询与修改

SHOW shared_buffers;
SELECT name, setting, unit, context, source FROM pg_settings WHERE name='work_mem';
SELECT current_setting('max_connections');

-- 会话级
SET work_mem = '64MB';
SET search_path = app, public;

-- 全局持久化（写入 postgresql.auto.conf）
ALTER SYSTEM SET max_connections = 500;
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM RESET max_connections;
SELECT pg_reload_conf();                -- 让 reload 类参数生效

-- 取消某条 SQL 的并行，临时排障用
SET max_parallel_workers_per_gather = 0;

参数生效方式看 pg_settings.context：

5.3 生产必调参数

# postgresql.conf 生产参考（32C/64G 专用机）
listen_addresses = '*'
port = 5432
max_connections = 500
superuser_reserved_connections = 5
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
huge_pages = try

wal_level = replica
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
wal_compression = on
archive_mode = on
archive_command = 'test ! -f /data/pg_wal_archive/%f && cp %p /data/pg_wal_archive/%f'

random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 200

autovacuum = on
autovacuum_max_workers = 6
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 500MB
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 10MB
log_autovacuum_min_duration = 0
log_line_prefix = '%m [%p] %q%u@%d/%a '

timezone = 'Asia/Shanghai'
datestyle = 'iso, ymd'
password_encryption = scram-sha-256

5.4 pg_hba.conf

格式：类型 数据库 用户 来源地址 认证方式 [选项]

# TYPE  DATABASE      USER        ADDRESS           METHOD
local   all           postgres                      peer
local   all           all                           scram-sha-256
host    all           all         127.0.0.1/32      scram-sha-256
host    appdb         app         10.0.0.0/24       scram-sha-256
host    all           repl        10.0.0.0/24       scram-sha-256
host    all           all         0.0.0.0/0         reject

hostssl all           all         0.0.0.0/0         scram-sha-256

# 改完不用重启
sudo -u postgres psql -c "SELECT pg_reload_conf();"
# 或
systemctl reload postgresql-18

避坑：pg_hba.conf 是从上到下第一条匹配生效，不是最精确匹配。把 reject 写在前面会拒掉所有人。 trust 等于免密，任何情况下都不要对 0.0.0.0/0 用。

6. 账号、权限与认证

6.1 角色模型

PostgreSQL 里"用户"就是带 LOGIN 的角色，没有单独的用户概念。

-- 建角色
CREATE ROLE app LOGIN PASSWORD 'App#2026';
CREATE ROLE readonly LOGIN PASSWORD 'RO#2026';
CREATE ROLE dba LOGIN SUPERUSER PASSWORD 'Dba#2026';

-- 改属性
ALTER ROLE app PASSWORD 'New#2026';
ALTER ROLE app VALID UNTIL '2027-01-01';
ALTER ROLE app NOLOGIN;                   -- 禁止登录
ALTER ROLE app CONNECTION LIMIT 50;       -- 限制并发连接
ALTER ROLE app SET search_path = app, public;   -- 角色级默认参数

-- 角色继承（组角色）
CREATE ROLE role_read;
GRANT role_read TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO role_read;

-- 查看
\du+
SELECT rolname, rolsuper, rolcanlogin, rolconnlimit, rolvaliduntil FROM pg_roles;

6.2 授权模型（四层）

PG 的权限是 数据库 → schema → 表/序列/函数 → 列 四层，少给一层就查不到。

-- 1) 允许连接数据库
GRANT CONNECT ON DATABASE appdb TO app;

-- 2) 允许使用 schema（15 之前 public 默认可写，15 起默认收回）
GRANT USAGE ON SCHEMA app TO app;
GRANT CREATE ON SCHEMA app TO app;

-- 3) 表、序列、函数
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app;

-- 4) 列级
GRANT SELECT (id, name) ON app.users TO readonly;

-- 只读账号完整配方
GRANT CONNECT ON DATABASE appdb TO readonly;
GRANT USAGE ON SCHEMA app TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO readonly;

-- 收回
REVOKE DELETE ON app.orders FROM app;

-- 查看
\dp app.orders
SELECT * FROM information_schema.table_privileges WHERE grantee='app';

为新表自动授权（关键，否则每次建表都要重授）：

ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app;

-- 只读角色也需要，且要指定"谁建的表"
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA app
  GRANT SELECT ON TABLES TO readonly;

版本差异：PG 15 起，public schema 的 CREATE 权限默认从 PUBLIC 收回，只保留 USAGE。从 14 及以前升级到 15+ 后，普通用户建表会突然报 permission denied for schema public，这是最常见的一次升级故障。

6.3 密码与认证

-- 密码加密算法（建/改密码时生效）
SHOW password_encryption;                  -- scram-sha-256
SET password_encryption = 'scram-sha-256';
ALTER USER app PASSWORD 'App#2026';

-- 检查是否有 md5 老密码（PG18 起 md5 已弃用）
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

# 生成 SCRAM 密文离线写入
python3 -c "import hashlib,base64,os;print('SCRAM-SHA-256$4096:'+base64.b64encode(os.urandom(16)).decode())"

版本差异：PG 14 起默认 scram-sha-256；PG 18 起 MD5 认证被标记弃用并会在日志中告警，PG 19 移除了 MD5 密码告警相关兼容行为。仍有 md5 账号的实例应在升级前全部改密。

6.4 安全基线

# postgresql.conf
listen_addresses = '10.0.0.11'       # 只监听内网
ssl = on
ssl_cert_file = '/data/pg18/server.crt'
ssl_key_file  = '/data/pg18/server.key'

-- 撤销 public 上的默认权限
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 确认没有无密码角色
SELECT rolname FROM pg_authid WHERE rolpassword IS NULL AND rolcanlogin;

# 强制 SSL 连接验证
psql "host=10.0.0.11 dbname=appdb user=app sslmode=verify-full sslrootcert=/etc/pg/ca.crt"

7. 库表日常操作

7.1 库与 schema

CREATE DATABASE appdb WITH ENCODING 'UTF8' LC_COLLATE 'C.UTF-8' LC_CTYPE 'C.UTF-8' TEMPLATE template0;
CREATE DATABASE appdb OWNER app;
DROP DATABASE appdb;                       -- 危险，无回收站
ALTER DATABASE appdb RENAME TO appdb2;
ALTER DATABASE appdb SET timezone = 'Asia/Shanghai';
ALTER DATABASE appdb CONNECTION LIMIT 200;

\l+                                        -- 库列表带大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;

CREATE SCHEMA app AUTHORIZATION app;
DROP SCHEMA app CASCADE;                   -- 危险，连表一起删
SET search_path = app, public;
SHOW search_path;

7.2 表操作

CREATE TABLE app.orders (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL,
  amount      numeric(12,2) NOT NULL DEFAULT 0,
  status      smallint NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user_created ON app.orders (user_id, created_at DESC);
CREATE INDEX CONCURRENTLY idx_orders_status ON app.orders (status);   -- 不锁写，生产必用

ALTER TABLE app.orders ADD COLUMN remark text;
ALTER TABLE app.orders ALTER COLUMN amount TYPE numeric(14,2);
ALTER TABLE app.orders RENAME COLUMN remark TO note;
ALTER TABLE app.orders DROP COLUMN note;

TRUNCATE app.orders;                        -- 危险，可加 RESTART IDENTITY
TRUNCATE app.orders RESTART IDENTITY CASCADE;
DROP TABLE app.orders;                      -- 危险

\d+ app.orders
SELECT pg_size_pretty(pg_total_relation_size('app.orders'));

重要：CREATE INDEX 会持有 SHARE 锁，阻塞写入；CREATE INDEX CONCURRENTLY 不阻塞但更慢、失败会留 invalid 索引。生产建索引一律用后者，并在低峰执行。

7.3 分区表

CREATE TABLE app.logs (
  id bigserial,
  created_at date NOT NULL,
  msg text
) PARTITION BY RANGE (created_at);

CREATE TABLE app.logs_2026_11 PARTITION OF app.logs
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- 默认分区兜底，避免插入无匹配分区时报错
CREATE TABLE app.logs_default PARTITION OF app.logs DEFAULT;

-- 查看分区
SELECT inhrelid::regclass AS partition, inhparent::regclass AS parent FROM pg_inherits;

-- 摘除分区（秒级，常用于归档）
ALTER TABLE app.logs DETACH PARTITION app.logs_2026_10;
DROP TABLE app.logs_2026_10;

-- 附加已有表为分区
ALTER TABLE app.logs ATTACH PARTITION app.logs_2026_12 FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

版本差异：PG 17 起支持 DETACH PARTITION ... CONCURRENTLY（需先建 CONCURRENTLY 约束），可避免长时间锁表；PG 14 之前 DETACH 会取 ACCESS EXCLUSIVE 锁。

7.4 统计信息

ANALYZE app.orders;
ANALYZE app.orders (user_id);                       -- 只收集指定列
VACUUM (ANALYZE, VERBOSE) app.orders;

-- 提升关键列统计精度
ALTER TABLE app.orders ALTER COLUMN user_id SET STATISTICS 500;

-- 查看统计信息
SELECT * FROM pg_stats WHERE tablename='orders' AND attname='status';

-- 扩展统计（多列相关性）
CREATE STATISTICS st_orders_multi (dependencies, ndistinct)
  ON status, user_id FROM app.orders;

8. 备份与恢复

8.1 三种备份方式

3-2-1 原则 + 定期恢复演练，没验证过的备份不算备份。

8.2 pg_dump

# 自定义格式（推荐，支持并行恢复与选择性恢复）
pg_dump -h 127.0.0.1 -U postgres -Fc -Z9 -d appdb -f /backup/appdb_$(date +%F).dump

# 纯 SQL（便于人工检查）
pg_dump -h 127.0.0.1 -U postgres -Fp -d appdb -f /backup/appdb_$(date +%F).sql

# 只要结构 / 只要数据
pg_dump -U postgres -s -d appdb -f appdb_schema.sql
pg_dump -U postgres -a -d appdb -f appdb_data.sql

# 单表 / 多表
pg_dump -U postgres -t 'app.orders*' -d appdb -Fc -f orders.dump

# 排除大日志表
pg_dump -U postgres -d appdb -T 'app.logs*' -Fc -f appdb_nolog.dump

# 全实例（含角色、表空间）
pg_dumpall -U postgres -f /backup/all_$(date +%F).sql
pg_dumpall -U postgres --globals-only -f /backup/globals.sql

关键参数：

# 目录格式并行导出，大库首选
pg_dump -U postgres -Fd -j 8 -d appdb -f /backup/appdb_dir

8.3 恢复

# 自定义格式
pg_restore -h 127.0.0.1 -U postgres -d appdb_new -j 8 --no-owner --verbose /backup/appdb.dump

# 只恢复某张表
pg_restore -U postgres -d appdb -t orders --no-owner /backup/appdb.dump

# 只恢复结构
pg_restore -U postgres -d appdb -s --no-owner /backup/appdb.dump

# 先建库再恢复（-C 可自动建库）
createdb -U postgres appdb_new
pg_restore -U postgres -d appdb_new --clean --if-exists --no-owner -j 8 /backup/appdb.dump

# 纯 SQL 恢复
psql -U postgres -d appdb -v ON_ERROR_STOP=1 -f /backup/appdb.sql

避坑：pg_restore 导入时索引与外键会在数据之后重建，速度比 mysqldump 恢复慢是正常的。想快就调大 maintenance_work_mem 与 max_parallel_maintenance_workers 再恢复。

8.4 pg_basebackup

# 物理全备，-R 自动写 standby 配置，-X stream 同步 WAL
pg_basebackup -h 10.0.0.11 -p 5432 -U repl -D /data/pg18_backup \
  -Fp -Xs -P -R -z

# 打 tar 包
pg_basebackup -h 10.0.0.11 -U repl -D /backup -Ft -z -Xs -P

# 参数说明
# -Fp 明文目录 / -Ft tar
# -Xs stream WAL（推荐）/ -Xf 归档 WAL
# -R 写 recovery 配置，直接可作从库
# -P 显示进度
# -c fast|spread 检查点模式

9. WAL、归档与 PITR

9.1 归档配置

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /data/pg_wal_archive/%f && cp %p /data/pg_wal_archive/%f'
# 或用 pgBackRest / WAL-G 替代 cp
restore_command = 'cp /data/pg_wal_archive/%f %p'

sudo -u postgres psql -c "SELECT pg_reload_conf();"
mkdir -p /data/pg_wal_archive && chown postgres:postgres /data/pg_wal_archive

-- 检查归档是否正常
SELECT * FROM pg_stat_archiver;
-- failed_count 持续增长 = 归档失败，WAL 会无限堆积直到磁盘满

9.2 手工基础备份（不开 pg_basebackup 时）

SELECT pg_start_backup('manual_backup', true, false);
-- 此时用 rsync/tar 拷贝整个数据目录
SELECT pg_stop_backup(false);

sudo -u postgres psql -c "SELECT pg_start_backup('b1', true, false);"
rsync -a --exclude=pg_wal /var/lib/pgsql/18/data/ /backup/pgdata/
sudo -u postgres psql -c "SELECT pg_stop_backup(false);"

9.3 PITR 时间点恢复

# 1) 停库，移走损坏的数据目录
systemctl stop postgresql-18
mv /var/lib/pgsql/18/data /var/lib/pgsql/18/data.broken

# 2) 恢复基础备份
cp -a /backup/pgdata /var/lib/pgsql/18/data
chown -R postgres:postgres /var/lib/pgsql/18/data
rm -rf /var/lib/pgsql/18/data/pg_wal/*

# 3) 创建 recovery.signal（PG12+ 用信号文件，不再改 recovery.conf）
touch /var/lib/pgsql/18/data/recovery.signal

# 4) 在 postgresql.auto.conf 写恢复目标
cat >> /var/lib/pgsql/18/data/postgresql.auto.conf <<'EOF'
restore_command = 'cp /data/pg_wal_archive/%f %p'
recovery_target_time = '2026-11-05 10:15:00+08'
recovery_target_action = 'promote'
EOF

# 5) 启动，观察日志
systemctl start postgresql-18
tail -f /var/lib/pgsql/18/data/log/postgresql-*.log | grep -i 'recovery'

-- 恢复完成确认
SELECT pg_is_in_recovery();           -- 应为 f
SELECT now(), pg_last_wal_replay_lsn();

版本差异：PG 12 起取消 recovery.conf，改用 recovery.signal / standby.signal 信号文件 + 参数写入 postgresql.conf。网上大量 11 及以前的教程直接照抄会失败。

10. 流复制与逻辑复制

10.1 流复制（物理，整实例）

主库配置：

wal_level = replica
max_wal_senders = 10
wal_keep_size = 2GB
hot_standby = on

-- 复制账号（必须具备 REPLICATION）
CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD 'Repl#2026';

# pg_hba.conf
host replication repl 10.0.0.0/24 scram-sha-256

从库搭建：

systemctl stop postgresql-18
rm -rf /var/lib/pgsql/18/data/*
sudo -u postgres pg_basebackup -h 10.0.0.11 -U repl -D /var/lib/pgsql/18/data \
  -Fp -Xs -P -R
systemctl start postgresql-18

-R 会自动生成 standby.signal 与 primary_conninfo。若手工配置：

primary_conninfo = 'host=10.0.0.11 port=5432 user=repl password=Repl#2026'
primary_slot_name = 'replica1'
hot_standby = on

监控：

-- 主库看从库
SELECT client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- 从库看接收与回放
SELECT status, receive_start_lsn, written_lsn, flushed_lsn, latest_end_lsn, last_msg_send_time
FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;
SELECT pg_is_in_recovery();

复制槽（防止主库清理未传完的 WAL）：

SELECT * FROM pg_replication_slots;
SELECT pg_create_physical_replication_slot('replica1');
SELECT pg_drop_replication_slot('replica1');

-- 逻辑槽的滞后查看（重点：不消费会撑爆磁盘）
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

切换与重建：

# 主库故障，提升从库
sudo -u postgres pg_ctl -D /data/pg18 promote

# 原主库修复后回切（pg_rewind 只同步差异块，比重新 basebackup 快得多）
sudo -u postgres pg_rewind --target-pgdata=/data/pg18 \
  --source-server="host=10.0.0.12 port=5432 user=postgres"

10.2 逻辑复制（表级，跨版本）

-- 发布端
CREATE PUBLICATION pub_orders FOR TABLE app.orders;
CREATE PUBLICATION pub_all FOR ALL TABLES;
ALTER PUBLICATION pub_orders ADD TABLE app.order_items;

-- 订阅端（表结构需先手工建好）
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=10.0.0.11 port=5432 dbname=appdb user=repl password=Repl#2026'
  PUBLICATION pub_orders;

-- 监控
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_origin_status;

关键限制：逻辑复制只同步 DML，不同步 DDL。加列、改类型必须两端手工执行，否则订阅端会报错中断。序列值在 PG 16 之前也不同步。

11. 日志体系

11.1 日志类型

SHOW log_destination;
SHOW logging_collector;
SHOW log_directory;
SHOW log_filename;
SHOW log_min_duration_statement;
SHOW log_line_prefix;

11.2 常用日志参数

log_destination = 'csvlog'          # csvlog 便于 pgBadger 分析
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 500MB
log_truncate_on_rotation = on
log_min_duration_statement = 1000   # 记录 >1s 的语句
log_min_duration_sample = 100       # PG13+ 采样记录，降低开销
log_statement_sample_rate = 0.1
log_checkpoints = on
log_lock_waits = on
log_temp_files = 10MB
log_autovacuum_min_duration = 0
log_error_verbosity = default
log_line_prefix = '%m [%p] %q%u@%d/%a '

注意：log_statement='all' 会记录所有语句，生产上 IO 与日志量会暴涨，只用于短期排障。

11.3 日志分析

# 直接查 csvlog 里的错误
grep -i 'ERROR' /var/lib/pgsql/18/data/log/postgresql-2026-11-05.csv | tail -20

# 统计错误类型 TOP
awk -F',' '{print $12}' postgresql-*.csv | grep -v '^$' | sort | uniq -c | sort -rn | head

# pgBadger 生成报告
pgbadger -f stderr -o /tmp/pg_report.html /var/lib/pgsql/18/data/log/postgresql-*.log
pgbadger --prefix '%t [%p] %u@%d ' -o report.html postgresql.log

11.4 日志清理

# 用 logrotate（logging_collector 关闭时）
cat > /etc/logrotate.d/postgresql <<'EOF'
/var/lib/pgsql/18/data/log/*.log {
    daily
    rotate 14
    compress
    missingok
    notifempty
    copytruncate
}
EOF

注意：logging_collector=on 时 PG 自己管理轮转，再用 logrotate 处理会冲突。二选一。

12. 性能诊断与优化

12.1 一眼看现状

SELECT version();
SELECT now() - pg_postmaster_start_time() AS uptime;

-- 连接数
SELECT count(*) AS total,
       count(*) FILTER (WHERE state='active') AS active,
       count(*) FILTER (WHERE state='idle') AS idle,
       count(*) FILTER (WHERE state='idle in transaction') AS idle_in_tx
FROM pg_stat_activity;

SHOW max_connections;
SELECT setting, (SELECT count(*) FROM pg_stat_activity) AS used FROM pg_settings WHERE name='max_connections';

-- 数据库级统计
SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit,
       tup_returned, tup_fetched, deadlocks, temp_files, temp_bytes
FROM pg_stat_database WHERE datname='appdb';

缓存命中率：

SELECT datname,
       ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0','template1');
-- OLTP 应 > 99%

12.2 当前活动与会话

SELECT pid, usename, datname, client_addr, state,
       now() - query_start AS duration, wait_event_type, wait_event,
       LEFT(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

-- 找长事务（阻塞 VACUUM 的元凶）
SELECT pid, usename, state, now() - xact_start AS xact_age, LEFT(query,100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

-- idle in transaction 超过 5 分钟，必须处理
SELECT pid, now() - state_change AS idle_age, LEFT(query,80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;

-- 取消查询 / 终止连接
SELECT pg_cancel_backend(12345);        -- 温和，等价 Ctrl+C
SELECT pg_terminate_backend(12345);     -- 强制断连
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state='idle in transaction' AND now()-state_change > interval '10 minutes';

# 命令行方式
psql -U postgres -c "SELECT pg_terminate_backend(12345);"

12.3 锁与阻塞

-- 谁在等谁（PG 9.6+ 最简写法）
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, wait_event,
       LEFT(query,100) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 锁明细
SELECT l.pid, l.locktype, l.relation::regclass, l.mode, l.granted,
       a.state, LEFT(a.query,80) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted
ORDER BY l.pid;

-- 阻塞树（含根源）
WITH RECURSIVE lock_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blockers, 1 AS depth
  FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT lt.pid, pg_blocking_pids(a.pid), lt.depth+1
  FROM lock_tree lt JOIN pg_stat_activity a ON a.pid = ANY(lt.blockers)
  WHERE lt.depth < 5
)
SELECT DISTINCT pid, blockers, depth FROM lock_tree ORDER BY depth;

重点：PG 的 ACCESS EXCLUSIVE 锁（ALTER TABLE、DROP、VACUUM FULL）会排在所有锁后面，一旦前面有长事务，后续所有查询都会被堵住，形成雪崩。发现 ALTER TABLE 卡住要立刻查 pg_locks。

12.4 执行计划

EXPLAIN SELECT * FROM app.orders WHERE user_id = 100;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;      -- 真实执行 + 缓存命中
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

关键判读：

-- 查看某条语句的规划时间与执行时间
EXPLAIN (ANALYZE, TIMING) SELECT ...;

-- 强制走/不走索引（排障对比用，不要写进生产）
SET enable_seqscan = off;
SET enable_nestloop = off;

12.5 pg_stat_statements

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 总耗时 TOP 10
SELECT calls, ROUND(total_exec_time::numeric,1) AS total_ms,
       ROUND(mean_exec_time::numeric,2) AS mean_ms,
       rows, ROUND(100.0*shared_blks_hit/NULLIF(shared_blks_hit+shared_blks_read,0),1) AS hit_pct,
       LEFT(query,100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 单次最慢
SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- 重置
SELECT pg_stat_statements_reset();

版本差异：PG 13 起列名从 total_time / mean_time 改为 total_exec_time / mean_exec_time，并新增 total_plan_time。老监控 SQL 升级后会报列不存在。

12.6 系统层排障

top -H -p $(head -1 /var/lib/pgsql/18/data/postmaster.pid)
pidstat -p $(head -1 /var/lib/pgsql/18/data/postmaster.pid) 1
iostat -x 1 5
vmstat 1 5
free -h
dmesg | grep -i -E 'oom|killed process'

-- 每个后端进程的资源消耗
SELECT pid, usename, state, backend_type, now()-query_start AS dur
FROM pg_stat_activity ORDER BY dur DESC NULLS LAST LIMIT 10;

-- PG 16+ IO 统计
SELECT backend_type, object, context, reads, writes, hits,
       ROUND(reads*100.0/NULLIF(reads+hits,0),2) AS read_pct
FROM pg_stat_io ORDER BY reads DESC LIMIT 15;

13. VACUUM、膨胀与事务 ID

13.1 为什么必须 VACUUM

PG 的 MVCC 用"旧版本行"实现，UPDATE/DELETE 只是标记失效，空间由 VACUUM 回收。不 VACUUM 会导致表膨胀 + 事务 ID 回卷（数据库强制停机）。

VACUUM app.orders;                              -- 常规，不锁写
VACUUM (VERBOSE, ANALYZE) app.orders;
VACUUM FULL app.orders;                         -- 危险，ACCESS EXCLUSIVE 锁，重写整表
VACUUM (FREEZE, VERBOSE) app.orders;

-- 查看 autovacuum 进度
SELECT * FROM pg_stat_progress_vacuum;
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

13.2 事务 ID 回卷

-- 各库最老未冻结事务的年龄，超过 20 亿会强制停机
SELECT datname, age(datfrozenxid) AS xid_age,
       ROUND(100.0*age(datfrozenxid)/2147483647, 2) AS pct_used
FROM pg_database ORDER BY age(datfrozenxid) DESC;

-- 阈值参数
SHOW autovacuum_freeze_max_age;      -- 默认 2 亿
SHOW vacuum_freeze_table_age;
SHOW vacuum_freeze_min_age;

-- 表级排查
SELECT relname, age(relfrozenxid) FROM pg_class
WHERE relkind='r' ORDER BY age(relfrozenxid) DESC LIMIT 10;

致命故障：xid_age 逼近 21 亿时 PG 会拒绝所有新事务并打印 database is not accepting commands to avoid wraparound data loss。此时只能停库做全库 VACUUM FREEZE（单用户模式 postgres --single），大库可能要几小时。监控必须覆盖这一项。

13.3 膨胀检测

-- 表膨胀估算（需 pgstattuple 扩展，精确但有开销）
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('app.orders');
-- dead_tuple_percent > 20% 就该处理

-- 快速估算（不装扩展，基于统计信息，仅参考）
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       ROUND(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# 在线重建，不长时间锁表（推荐）
pg_repack -h 127.0.0.1 -U postgres -d appdb -t app.orders --no-superuser-check

对比：VACUUM FULL 会取排他锁、重写整表，期间业务停写；pg_repack 通过触发器和影子表在线重建，只短暂取锁，生产首选。MySQL 的 OPTIMIZE TABLE 对应 pg_repack 的角色。

13.4 autovacuum 调优

-- 单表覆盖全局设置（大表必做）
ALTER TABLE app.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2
);

-- 查看当前生效值
SELECT relname, reloptions FROM pg_class WHERE relname='orders';

14. 空间与容量管理

14.1 库表大小

-- 库大小排行
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- 表大小（含索引与 TOAST）
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS table,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

-- 索引大小排行
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 15;

-- 单表精确值
SELECT pg_size_pretty(pg_total_relation_size('app.orders'));

14.2 磁盘占用盘点

df -h
du -sh /var/lib/pgsql/18/data/*
du -sh /var/lib/pgsql/18/data/pg_wal
ls -lh /var/lib/pgsql/18/data/pg_wal | tail
du -sh /data/pg_wal_archive

常见"磁盘莫名满了"的原因：

1. WAL 堆积：归档失败（pg_stat_archiver.failed_count 增长）、复制槽不消费、max_wal_size 过大。
2. 日志文件：logging_collector 配置了但没轮转。
3. 表/索引膨胀：长期不 VACUUM，或 autovacuum 被长事务阻塞。
4. 临时文件：work_mem 不足导致大量落盘，log_temp_files 可定位。
5. core dump：崩溃后留下的 core 文件。

14.3 WAL 清理

-- 查看 WAL 目录占用与文件数
SELECT count(*), pg_size_pretty(sum(size)) FROM pg_ls_waldir();

-- 手工切换 WAL 段（归档测试用）
SELECT pg_switch_wal();

-- 创建还原点
SELECT pg_create_restore_point('before_upgrade');

-- 检查点
CHECKPOINT;

# 只清理已归档的旧 WAL（archive 模式下 pg_archivecleanup）
pg_archivecleanup /data/pg_wal_archive 000000010000000000000050

危险：绝对不要手工 rm pg_wal 里的文件。PG 依赖 WAL 做崩溃恢复，删错一个文件整库可能无法启动。需要清理用 pg_archivecleanup 或调整 max_wal_size 等参数让其自然回收。

14.4 表空间

CREATE TABLESPACE fast_ssd LOCATION '/data/ssd/pg_tblspc';
ALTER TABLE app.orders SET TABLESPACE fast_ssd;
ALTER INDEX app.idx_orders_user SET TABLESPACE fast_ssd;

SELECT spcname, pg_tablespace_location(oid),
       pg_size_pretty(pg_tablespace_size(oid))
FROM pg_tablespace;

15. 升级与迁移

15.1 升级路径

小版本（18.5 → 18.6）：直接替换二进制，重启即可，数据格式兼容
大版本（17 → 18）：必须 pg_upgrade 或 dump/restore，不可原地换 binary

小版本升级（RPM）：

dnf update -y postgresql18-server postgresql18-contrib
systemctl restart postgresql-18

15.2 pg_upgrade（原地升级，快）

# 0) 两个版本必须都装好，且新版本已 initdb 一个空集群
dnf install -y postgresql18-server
/usr/pgsql-18/bin/postgresql-18-setup initdb

# 1) 停旧库
systemctl stop postgresql-17

# 2) 预检（--check 只检查不改动，必做）
sudo -u postgres /usr/pgsql-18/bin/pg_upgrade \
  --check \
  -b /usr/pgsql-17/bin \
  -B /usr/pgsql-18/bin \
  -d /var/lib/pgsql/17/data \
  -D /var/lib/pgsql/18/data

# 3) 正式升级（--link 用硬链接，快但旧集群不可回退；不加则全量拷贝）
sudo -u postgres /usr/pgsql-18/bin/pg_upgrade \
  -b /usr/pgsql-17/bin \
  -B /usr/pgsql-18/bin \
  -d /var/lib/pgsql/17/data \
  -D /var/lib/pgsql/18/data \
  --link

# 4) 按提示执行 analyze 脚本，然后启动新库
/var/lib/pgsql/18/analyze_new_cluster.sh
systemctl start postgresql-18

危险：--link 会硬链接数据文件，升级后旧集群不可用，无法回退。生产建议先不加 --link 做一次完整拷贝升级，验证通过再择机用 link 方式割接。升级前必须有 pg_basebackup 全备。

15.3 逻辑迁移（跨大版本/跨云最稳）

# 导出（目录格式并行）
pg_dump -h old -U postgres -Fd -j 8 -d appdb -f /backup/appdb_dir

# 导入
createdb -h new -U postgres appdb
pg_restore -h new -U postgres -d appdb -j 8 --no-owner /backup/appdb_dir

# 全实例（含角色）
pg_dumpall -h old -U postgres --globals-only -f /backup/globals.sql
psql -h new -U postgres -f /backup/globals.sql

降停机迁移（大库）：先 pg_dump 全量，再用逻辑复制追增量，最后短暂停写切换。

15.4 升级后必查

-- 1) 版本
SELECT version();

-- 2) 无效索引（pg_upgrade 后可能出现）
SELECT indexrelid::regclass, indrelid::regclass FROM pg_index WHERE NOT indisvalid;

-- 3) 未 ANALYZE 的表
SELECT relname FROM pg_class c
WHERE relkind='r' AND NOT EXISTS (
  SELECT 1 FROM pg_stat_user_tables s WHERE s.relid=c.oid AND s.last_analyze IS NOT NULL);

-- 4) 扩展是否需要更新
SELECT extname, extversion FROM pg_extension;
ALTER EXTENSION pg_stat_statements UPDATE;

-- 5) public schema 权限（15+ 升级重灾区）
\dn+

16. 高频故障速查

16.1 连不上

sudo -u postgres psql -c "SHOW listen_addresses; SHOW port;"
ss -lntp | grep 5432
sudo -u postgres psql -c "SELECT count(*), state FROM pg_stat_activity GROUP BY state;"

16.2 连接数打满救火

-- 找出占用最多的来源
SELECT client_addr, usename, count(*) FROM pg_stat_activity
GROUP BY 1,2 ORDER BY 3 DESC;

-- 清掉长时间 idle 的连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state='idle' AND now()-state_change > interval '30 minutes'
  AND pid <> pg_backend_pid();

-- 临时提高上限（需重启，PG 不支持热改 max_connections）
ALTER SYSTEM SET max_connections = 800;

注意：max_connections 是 postmaster 级参数，pg_reload_conf() 不生效，必须重启。真正常态化的解法是上 pgbouncer。

16.3 表被锁 / DDL 卡住

-- 找到阻塞源，优先处理长事务
SELECT pid, state, now()-xact_start AS xact_age, LEFT(query,100)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5;

-- 取消 DDL，别硬杀（杀了也要回滚）
SELECT pg_cancel_backend(<ddl_pid>);

-- 安全 DDL 模板：先设短锁超时，拿不到锁就放弃，避免堵住全表
SET lock_timeout = '3s';
ALTER TABLE app.orders ADD COLUMN note text;
-- 失败就重试，不要在没设 lock_timeout 的情况下直接 ALTER

16.4 复制中断

-- 从库看状态
SELECT status, last_msg_send_time, last_msg_receipt_time, latest_end_lsn FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS delay;

16.5 磁盘满

df -h
du -sh /var/lib/pgsql/18/data/*
du -sh /var/lib/pgsql/18/data/pg_wal

SELECT count(*), pg_size_pretty(sum(size)) FROM pg_ls_waldir();
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots ORDER BY retained DESC;

急救：清归档日志（确认已备份）→ 删无效复制槽 → 清临时文件 → 扩盘。千万不要在磁盘满时做 VACUUM FULL，它需要等量临时空间。

16.6 事务 ID 告警

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

# 已进入只读保护时的抢救（单用户模式冻结）
systemctl stop postgresql-18
sudo -u postgres /usr/pgsql-18/bin/postgres --single -D /var/lib/pgsql/18/data postgres
# 在单用户提示符下执行
VACUUM FREEZE;

16.7 时区与时间

SHOW timezone;
SELECT now(), now() AT TIME ZONE 'UTC', current_timestamp;
SET timezone = 'Asia/Shanghai';
ALTER DATABASE appdb SET timezone = 'Asia/Shanghai';

建表建议：用 timestamptz 而不是 timestamp。前者存 UTC、按会话时区展示；后者不带时区，跨时区迁移必出问题。

16.8 启动失败常见原因

tail -200 /var/lib/pgsql/18/data/log/postgresql-*.log

17. 危险命令与避坑清单

17.1 变更前检查清单

# 1) 确认连的是哪个实例
psql -c "SELECT inet_server_addr(), inet_server_port(), current_database(), version();"

# 2) 备份
pg_dump -Fc -d <db> -f /backup/pre_change_$(date +%F_%H%M).dump

# 3) 看表大小（决定用哪种 DDL 方式）
psql -c "SELECT pg_size_pretty(pg_total_relation_size('<table>'));"

# 4) 看长事务（有长事务时不要做 DDL）
psql -c "SELECT pid, now()-xact_start FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY 2 DESC LIMIT 5;"

# 5) DDL 带锁超时
psql -c "SET lock_timeout='3s'; ALTER TABLE ...;"

# 6) 观察复制延迟
psql -c "SELECT client_addr, pg_wal_lsn_diff(sent_lsn, replay_lsn) FROM pg_stat_replication;"

18. 附录：巡检脚本与速查

18.1 一分钟巡检

#!/usr/bin/env bash
# 用法：PGPASSWORD=xxx ./pg_health.sh  （或用 ~/.pgpass）
set -euo pipefail

psql -X -P pager=off <<'SQL'
\echo '--- 版本与运行时长 ---'
SELECT version();
SELECT now() - pg_postmaster_start_time() AS uptime;

\echo '--- 连接数 ---'
SELECT count(*) AS total,
       count(*) FILTER (WHERE state='active') AS active,
       count(*) FILTER (WHERE state='idle in transaction') AS idle_in_tx,
       (SELECT setting::int FROM pg_settings WHERE name='max_connections') AS max_conn
FROM pg_stat_activity;

\echo '--- 缓存命中率 ---'
SELECT datname, ROUND(100.0*blks_hit/NULLIF(blks_hit+blks_read,0),2) AS hit_pct,
       deadlocks, temp_files, pg_size_pretty(temp_bytes) AS temp
FROM pg_stat_database WHERE datname NOT IN ('template0','template1');

\echo '--- 长时间运行语句 TOP5 ---'
SELECT pid, state, now()-query_start AS dur, LEFT(query,70) AS query
FROM pg_stat_activity WHERE state='active' ORDER BY dur DESC LIMIT 5;

\echo '--- 阻塞 ---'
SELECT pid, pg_blocking_pids(pid) AS blockers, LEFT(query,60) AS query
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

\echo '--- 复制状态 ---'
SELECT client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
FROM pg_stat_replication;

\echo '--- 死元组 TOP5 ---'
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       ROUND(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),1) AS dead_pct
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

\echo '--- 事务 ID 年龄 ---'
SELECT datname, age(datfrozenxid) AS xid_age,
       ROUND(100.0*age(datfrozenxid)/2147483647,2) AS pct
FROM pg_database ORDER BY 2 DESC;

\echo '--- 库大小 TOP5 ---'
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC LIMIT 5;

\echo '--- 归档状态 ---'
SELECT archived_count, failed_count, last_archived_time, last_failed_time FROM pg_stat_archiver;
SQL

18.2 单行命令速查

pg_dump -Fc -d appdb -f appdb.dump                 # 备份单库
pg_restore -d appdb -j 8 appdb.dump                # 恢复
pg_dumpall --globals-only -f globals.sql           # 备份角色
pg_basebackup -D /backup -Ft -z -Xs -P -R          # 物理全备
pg_ctl -D $PGDATA reload                           # 重载配置
pg_ctl -D $PGDATA status                           # 状态
pg_isready -h 127.0.0.1 -p 5432                    # 存活探测
pg_ctlcluster 18 main start                        # Debian 系启停
psql -l                                            # 列出数据库
psql -c "SELECT pg_reload_conf();"                 # 重载
vacuumdb --all --analyze-only -j 4                 # 全库分析
reindexdb -d appdb -j 4                            # 重建索引

18.3 关键视图速查

18.4 常用端口与目录

附：与工作区其他手册的配合

• 操作系统层操作见 CentOS-7-10-常规运维指令手册.md / Ubuntu-常用运维指令手册.md
• 放行端口：firewall-cmd --add-service=postgresql --permanent 或 ufw allow 5432/tcp
• SELinux 下改数据目录：semanage fcontext -a -t postgresql_db_t "/data/pg18(/.*)?" + restorecon -Rv /data/pg18
• 与 MySQL 的核心差异：PG 大版本不能原地升级、权限分四层、必须 VACUUM、事务 ID 会回卷