备份恢复与运维

备份回答的是两个冷酷的问题：最多能丢多少数据（RPO），以及多快能把库拉回来（RTO）。本章从这两个问题出发给出选型路线，然后是 pg_dump / pg_basebackup / WAL 归档 PITR 的可跑配置，最后归拢日常运维的观测 SQL、日志、升级与监控——这些是数据库管理员每周真正在做的事。

前置阅读：WAL 与检查点机制见 01-架构与核心概念.md，死元组与 vacuum horizon 见 06-MVCC与Vacuum.md，复制槽见 10-复制与高可用.md。

决策框架：先定 RPO/RTO，再选路线

把备份想成两种完全不同的保险：pg_dump 导出的是"这份文档的当前副本"，恢复只能回到导出那一刻；pg_basebackup + WAL 归档是"监控录像"，可以把整个数据库倒带回录像覆盖内的任意一秒。

一个可以直接抄的组合策略：pg_basebackup 每周日全量 + archive_command 持续归档 WAL（WAL-G 或 pgBackRest 接管上传与保留策略）+ 每天一份关键库的 pg_dump 作为"不想按时间点倒带、只想捞回某张表"的逃生舱。三者不互斥，缺了 WAL 归档的数据库谈不上 RPO 接近零。

注意 先想清楚再动手：100 GB 的库用 pg_dump 备份恢复要数小时，而 basebackup 恢复加上归档 WAL 重放通常快一个数量级；反过来，只想救一张被误 DROP 的表，PITR 要把整库倒带另存再导表回去，不如一份现成的 dump。RPO/RTO 是业务指标，不是技术指标。

pg_dump / pg_restore 实战

pg_dump 是一致性快照导出：它在一个 REPEATABLE READ 事务里读出所有表，备份期间业务照常读写。记住这个实现方式，就能推出它最重要的运维含义——dump 大库 = 制造一个长事务，会钉住 vacuum horizon，死元组在 dump 期间只增不减（06-MVCC与Vacuum.md）。TB 级库的 dump 要安排在低峰，事后观察 autovacuum 是否"追不上"。

# 自定义格式（压缩、可选对象、支持并行恢复），URL 风格连接串
pg_dump "host=10.0.0.11 dbname=app user=pgback" -Fc -Z 6 -f app-2026-10-20.dump

# 恢复前先看清归档里有什么（TOC），这本身就是一次免费的体检
pg_restore --list app-2026-10-20.dump | less

# 恢复进目标库，8 路并行；--clean 先删后建，--if-exists 避免删不存在对象时报错
pg_restore -d app_new -j 8 --clean --if-exists app-2026-10-20.dump

# 只导/恢复某个 schema 或某几张表
pg_dump -n sales -t sales.orders -Fc -f slice.dump app

要点逐个说清：

• 并行要两头都开：pg_dump -j N 只在归档格式（-Fc/-Fd）下可用（纯文本不支持并行导出）；pg_restore -j N 必须配合 -d 直连目标库。大库恢复单线程可以慢到以天计，-j 是质的区别。
• -C（–create）让 dump 自带 CREATE DATABASE，恢复时先连到任意 maintenance 库；配合 --clean 就是"重建同名库"。跨版本迁移慎用——库的 owner、表空间、编码都会钉死。
• globals 是独立的坑：角色（含口令哈希）、表空间不在任何库里，pg_dump 导不到，必须 pg_dumpall -g 单独导出。新实例恢复的正确顺序是：先灌 globals，再恢复各库——dump 里对象引用的 owner 角色不存在时，轻则报权限错，重则整段跳过。
• 新版 dump 工具的过滤能力：PG 17 起 --filter 支持按文件包含/排除对象、pg_dump --exclude-extension 可把指定扩展整体跳过（17 release notes 确认）；大量大对象的库，17 起 pg_restore --transaction-size 能分批提交避免撑爆事务限制。

# 导出全局对象（角色/表空间），新实例第一步
pg_dumpall -g -f globals-2026-10-20.sql
psql -d postgres -f globals-2026-10-20.sql

pg_basebackup 与物理备份

pg_basebackup 直接从运行中的实例复制数据目录并流式带走备份期间的 WAL，不需要事先开归档（-X stream，默认值），备份完立刻可用——这使它同时是"物理全量备份"和"搭备机的克隆工具"（后者见 10-复制与高可用.md）。

# 定时物理备份：tar 格式 + gzip，-P 显示进度
pg_basebackup -h 10.0.0.11 -U pgback \
    -D /backup/base/$(date +%F) -Ft -z -Xs -P -v

两个必须知道的开销与行为：

• 备份开始会触发一次 fast checkpoint。主机会在这一刻集中刷脏页，写入重的库会出现一个短暂的卡顿窗口，且 checkpoint 之后到备份结束之间脏页积累更快。把备份排进低峰不是洁癖，是物理需求。
• 备份本身不校验页面，直到恢复回放才发现问题。防介质腐化的答案是 data checksums：initdb -k 在页头写入校验和，现代版本默认开启（PG 18 文档明确：除非指定 --no-data-checksums）。它不能在线补开——PG 12 起有 pg_checksums 工具，但必须整库停机逐页计算回写，时间和风险都大。所以"建库时开校验和"是少数无法事后补救的决定之一。

备份完整性的验证靠 manifest：

# plain 格式：备份目录自带 backup_manifest，一条命令逐页校验
pg_verifybackup /backup/base/2026-10-20

checksums 关闭时默认不生成 manifest，备份时要加 --manifest=force 才有东西可验。PG 18 起 pg_verifybackup 也支持校验 tar 格式备份（17 及之前只能验 plain 目录）——18 release notes 确认。

增量备份与 pg_combinebackup（17+）

PG 17 引入原生增量备份（release notes 确认）：summarize_wal = on 让后台的 WAL summarizer 进程把"哪些文件的哪些块被改过"记成摘要文件（可用 pg_walsummary 工具查看），pg_basebackup --incremental 拿着上次备份的 manifest 只回传变化的盘区：

# postgresql.conf（PG 17+），重启生效
summarize_wal = on

# 基准全量
pg_basebackup -h pri -U pgback -D /backup/full/01 -Fp -Xs -P

# 增量：--incremental 传入参考备份的 backup_manifest
pg_basebackup -h pri -U pgback -D /backup/incr/02 -Fp -Xs -P \
    --incremental=/backup/full/01/backup_manifest

# 合成可用全量：增量备份不能直接使用，必须 combine 依赖链
# 18 新增 -k/--link：可硬链的文件用硬链，省空间；但产物不能独立搬移（官方 notes 原话）
pg_combinebackup -o /backup/merged/02 /backup/full/01 /backup/incr/02

适用判断：数据量大、变更率低（每日增量只有几十 GB 里的几个 GB 变化）时最划算；高变更率或需要异地压缩上传的场景，pgBackRest/WAL-G 的增量仍更成熟。

WAL 归档与 PITR

物理备份让你回到"备份那一刻"，WAL 归档让你从那一刻"快进到任意时刻"。主库侧配置：

wal_level = replica
archive_mode = on
# 教学示例用本地复制；生产强烈建议交给工具（见下）
archive_command = 'test ! -f /mnt/pgarch/%f && cp %p /mnt/pgarch/%f'

%p 是待归档 WAL 的路径、%f 是文件名；test ! -f 保证幂等（重试不覆盖已归档段）。archive_mode=always 允许备机也参与归档——主机故障时正在归档的备机无缝顶上，不至于丢掉最后几个 WAL 段。生产环境把 archive_command 换成一条命令交给工具：

# WAL-G：压缩+加密+对象存储一条龙
archive_command = 'wal-g wal-push %p'
# pgBackRest：成熟的备份链/保留策略/校验管理
archive_command = 'pgbackrest --stanza=main archive-push %p'

恢复误删的半天数据（PITR）：准备一台空机器（或独立端口实例），从任一 basebackup 起步，配置恢复参数，指定时间点：

# 1. 解包基准备份到空数据目录
#    plain 格式（-Fp）本身就是一个数据目录，直接 cp -a 即可；
#    tar 格式（-Ft -z）含 data.tar.gz 与 pg_wal.tar.gz，分别解到对应位置
tar -xzf /backup/base/2026-10-20/data.tar.gz  -C /var/lib/pgsql/18/data
tar -xzf /backup/base/2026-10-20/pg_wal.tar.gz -C /var/lib/pgsql/18/data/pg_wal

# 2. 写恢复配置；recovery.signal 空文件告诉实例"你出生在恢复模式"
cat >> /var/lib/pgsql/18/data/postgresql.auto.conf <<'EOF'
restore_command = 'cp /mnt/pgarch/%f %p'
recovery_target_time = '2026-10-20 13:55:00+08'
recovery_target_action = 'promote'
EOF
touch /var/lib/pgsql/18/data/recovery.signal

# 3. 启动，回放 WAL 直到目标时间，自动 promote
pg_ctl start -D /var/lib/pgsql/18/data -l /tmp/pitr.log

除了 recovery_target_time，还有 recovery_target_lsn（精确到一个 LSN，跨备份也稳定）、recovery_target_name（配合 pg_create_restore_point 打的标记）、recovery_target = 'latest'（拉到归档链末端）。恢复完成后实例在新时间线上读写，别忘了立刻基于它做一次全新 basebackup 并重置归档目录的期望——倒带出来的库和原归档链已经是两条故事。

注意 PG 12 起不再用 recovery.conf（存在即拒绝启动），恢复语义全部搬进 postgresql.conf 的 recovery_target_* 加 recovery.signal 文件——搜到的教程若在编辑 recovery.conf，直接跳过。

没演练过 restore 的备份等于没有备份。 这条不是口号：备份文件损坏、归档链有洞（archive_command 悄悄失败了三周）、恢复参数记错，三类事故全部只有真恢复才能发现。季度性做一次全量演练：把备份恢复到一台临时机器，跑业务核心 SQL 校验行数和聚合值；每次 dump 后用 pg_restore --list 验证归档可读。

日常观测 SQL 集

以下查询直接可跑（PG 14 以上，个别标注版本）。观测的思路是顺序：连接与事务 → 空间 → WAL 与 IO → vacuum 与膨胀 → 锁与复制。

-- 当前在做什么：谁、什么语句、在等什么事件
SELECT pid, usename, application_name, client_addr, state,
       wait_event_type || '/' || wait_event AS waiting_on,
       now() - query_start AS running_for, left(query, 100) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_for DESC LIMIT 20;

-- 长事务与 idle in transaction（呼应 05-事务与锁.md：它们同时钉住 vacuum）
SELECT pid, usename, state, now() - xact_start AS xact_age,
       now() - state_change AS state_age, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '10 minutes'
ORDER BY xact_age;

-- WAL 累计计数器（pg_stat_wal 自 PG 14 起提供）：突增找写入异常
SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full,
       wal_write, wal_sync
FROM pg_stat_wal;

-- 检查点频率与耗时（pg_stat_bgwriter）：备份/抖动归因用
SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;

-- IO 画像（pg_stat_io 自 PG 16 起提供）：按读/写热度排
SELECT backend_type, object, context, reads, hits, evictions, writes, fsyncs
FROM pg_stat_io
WHERE reads + hits + writes > 0
ORDER BY reads + hits + writes DESC LIMIT 20;

-- 膨胀与 vacuum 追进度情况（详见 06-MVCC与Vacuum.md）
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 15;

-- 谁在全库范围钉住清理 horizon（长事务/废弃槽的元凶定位）
SELECT pid, usename, backend_xmin, now() - xact_start AS xact_age, state
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

-- 空间水位：库、最大的表、最大的索引
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;

-- 复制延迟（见 10-复制与高可用.md）与槽钉住的 WAL
SELECT application_name, state, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behind
FROM pg_stat_replication;
SELECT slot_name, slot_type, active, inactive_since,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- 锁等待链（完整版见 05-事务与锁.md）：谁被谁挡住
SELECT blocked.pid AS blocked_pid,
       left(blocked.query, 60) AS blocked_query,
       blk.pid AS blocking_pid,
       left(blk.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS x(bp) ON true
JOIN pg_stat_activity blk ON blk.pid = x.bp
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- 事务年龄（防回绕，见 06）与命中率
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;
SELECT datname,
       round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database WHERE datname IS NOT NULL;

日志与慢查询

默认日志直接打到 stderr，交给 systemd/docker 收——生产建议自己收（见 00-安装与配置.md）：

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'     -- 按天轮转，-%a 保证同周内文件不重名
log_rotation_age = 1d
log_min_duration_statement = 500       -- 毫秒；生产 250ms–1s 起步
log_checkpoints = on
log_lock_waits = on                    -- 锁等待超 deadlock_timeout 即记录
log_temp_files = 0                     -- 任何落盘临时文件都记录
log_line_prefix = '%m [%p] %u@%d %r '  -- 时间/pid/用户/库/来源

log_min_duration_statement 的取值逻辑：设太小会把正常语句刷成日志洪水（设成 0 等于全量记录，灾难），太大又漏掉"还没慢到报警、但已经慢到用户投诉"的区间。从 500ms 起步，结合业务 p99 收紧或放宽；慢查询的根因定位（先看执行计划）见 09-查询优化与EXPLAIN.md。

log_temp_files = 0 是排序/哈希溢盘信号——配合 09-查询优化与EXPLAIN.md 里 Sort Method: external merge 一起看，要么提 work_mem 要么修缺索引的排序。

超过阈值的语句直接要计划，auto_explain 比事后猜快得多：

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on          -- 真实执行统计，开销大，min_duration 别设太低
auto_explain.log_timing = off          -- 先关逐节点计时，够用且便宜

pg_stat_statements 提供按聚合耗时/调用次数的全局 top（它自己就是一个 schema，重启后才可查），与逐条慢日志互补：前者回答"整体谁在吃资源"，后者回答"这次抖动是哪条语句"。

数据生命周期与大版本升级

归档旧数据：时间序列表用分区（07-高级SQL.md），到期 DETACH PARTITION CONCURRENTLY 摘下来搬到慢盘/归档库再 DROP，比 DELETE FROM ... WHERE ts < ... 快几个数量级且不产生海量死元组；确认整表不要了就用 TRUNCATE（立即释放、不走 undo）。库内定时清理可上 pg_cron（第三方扩展，在库内跑 SQL 定时任务），简单的用外部 cron + psql 更直白。

大版本升级三条路线：

1. pg_upgrade：同机原地，新旧集群都装好后停机跑它。数据不搬运（默认拷贝，--link 硬链/--clone CoW 不复制），停机时间是分钟级——但注意 link 模式下旧目录再也删不得。17 起可迁移逻辑复制槽与订阅状态到新版本（官方 notes 限定旧集群需 ≥17）。
2. 逻辑复制：跨主机零停机，切流动作按 10-复制与高可用.md 的迁移流程执行。窗口内两边要各自手工同步 DDL；序列结构 18 起可纳入 publication，但序列当前值任何版本都不同步，切换前必须对每张带序列的表核对并 setval（18 文档 restriction 原话）。
3. dump/restore：最土但最可靠，停机 ≈ 全量导出+导入+重建索引。几十 GB 以下的小库或"顺便清理攒了几年的碎片"时反而是最优解。

升级前 checklist（任何路线通用）：

• pg_upgrade --check 先跑一遍：不兼容的数据类型（旧版本遗留的非法编码、sql_identifier 列等）会在这一步暴露；
• 对所有库（含 template0/template1）VACUUM FREEZE：存在接近回绕的未冻结行时，pg_upgrade 的 xid 检查会直接拒绝升级（waterfall 防线，原理见 06-MVCC与Vacuum.md）；
• 清理不活跃的复制槽与遗留 publication，避免把包袱带进新库；
• 新集群预装全部扩展二进制、对齐 locale/时区/initdb 参数（校验和！见上文，新集群这次记得开）;
• 升级完成后对新库 ANALYZE，并立刻做一次全新 basebackup——回退窗口只有"新集群还没写脏之前"。

扩展与生态

扩展是库内对象：CREATE EXTENSION 把函数/操作符/索引方法装进当前数据库，但二进制在服务器层 $libdir——先确认主机装了它。contrib 扩展随源码一起编译，Linux 发行包大多开箱可用，Windows 的 EDB 安装包只带子集，第三方扩展（PostGIS/pgvector/TimescaleDB）需要自己装对版本。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- 服务器级参数见日志一节
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA ext;

常见扩展各一句用途，作为选型地图：pg_stat_statements（全库语句级统计，性能排查第一站）、pg_trgm（三元组模糊匹配，LIKE '%abc%' 可走索引）、citext（大小写不敏感文本类型，用户名/邮箱列的省事解）、btree_gist（排除约束的基石，EXCLUDE USING gist 排时间段冲突）、postgis（地理空间事实标准）、pgvector（向量相似度检索，AI 应用标配）、timescaledb（自动时间分区+压缩+保留策略）。这一份只是运维视角的索引——扩展机制、哪些要 shared_preload_libraries、版本核验与逐个选型展开，见 15-扩展与生态.md 专章。

Docker/K8s 跑 PostgreSQL 的告诫（展开见 k8s/ 手册）：数据目录必须在宿主盘/本地 PV 上而不是网络盘；容器的重启策略对 PG 是毒药——PG 重启意味着 crash recovery，别把 livenessProbe 设得太紧让平台反复杀它（用 pg_isready 加业务级只读探测）；备份永远不要把 dump 打进镜像，PV 快照不能替代 WAL 归档；多副本要反亲和，否则一台宿主机宕机带走整个集群。经验结论：K8s 适合无状态服务，数据库上 K8s 只在平台能力成熟后考虑，否则 managed service 或裸机更省心。

监控告警最低集

没有预算做完整监控之前，这七条先配上（阈值按业务调）：

• 磁盘空间：数据目录 + pg_wal + 归档目录的使用率，80% 预警 90% 告警；归档目录膨胀往往意味着 archive_command 在静默失败。
• 归档健康：pg_stat_archiver 的 failed_count 增长、last_archived_time 距今超过预期段间隔。
• 复制与槽：replay_lag 超阈值、pg_replication_slots.retained 超磁盘余量 10%、inactive_since 非空且增长。
• 事务年龄：age(datfrozenxid) 超过 12 亿必须介入（回绕点 20 亿，autovacuum 的强制冻结阈值默认 2 亿，留足反应时间，详见 06-MVCC与Vacuum.md）。
• 长事务 / idle-in-transaction：超过 N 分钟告警，配合 idle_in_transaction_session_timeout 自动杀。
• 连接水位：count(*) FROM pg_stat_activity 与 max_connections 之比，超 80% 告警（连接池姿势见 08-Go客户端pgx.md）。
• 缓存命中率：pg_stat_database.blks_hit 比例断崖式下跌通常是新上线了全表扫描大户。

实战建议

三条军规，按优先级：

1. WAL 归档 + 工具链（WAL-G 或 pgBackRest）先于一切：没有归档链，pg_basebackup 只能把你带回备份那一刻；有了归档链才有 RPO 趋近于零和 PITR。
2. 定期演练恢复：每季度一次全量 PITR 演练到临时机器并用业务 SQL 验收；每次备份后 pg_verifybackup/pg_restore --list 自动校验。把"恢复演练成功"而不是"备份任务绿色"作为验收标准。
3. 监控事务年龄与槽/归档水位：xid 回绕、WAL 堆盘、归档断链这三类事故都有数天到数周的征兆期，缺的只是告警。

落地起步配置（最小且正确的一档）：

# postgresql.conf：备份与可观测的起点
wal_level = replica
archive_mode = on
archive_command = 'wal-g wal-push %p'
summarize_wal = on            # PG 17+，为大库增量备份铺路
logging_collector = on
log_min_duration_statement = 500
log_checkpoints = on
log_temp_files = 0
shared_preload_libraries = 'pg_stat_statements'

以上之外，initdb 阶段保留默认的数据校验和（-k），别用 --no-data-checksums——事后补开要全库停机（见上文）。

再加一句老运维的话：备份策略的价值在故障那天才被定价，而那天的你不会有心情研究恢复文档——所以文档里每一行恢复命令，都必须在演练里真正敲过一遍。