PostgreSQL：备份、恢复与连接排障

备份、独立库恢复与连接诊断

本轮仅做正文和源码静态审阅，以下命令、SQL、集成测试及恢复流程均未执行。过去真实运行记录保留在 migration/knowledge-rebuild/m4-next/postgres/verification.md 及其原始日志；历史结果不证明修改后的示例运行通过。

逻辑备份的边界

前置：完整项目。pg_dump 导出一个数据库的一致性逻辑快照，备份期间允许通常的读写；它不包含整个集群的角色和表空间定义。pg_dumpall --globals-only 可用于单独审查全局对象，但本实验仅恢复同一lab角色下的专用数据库，不声称全实例灾备完成。逻辑备份不是连续WAL归档，不能单靠每日dump承诺任意时间点恢复。

本例选择 pg_dump -Fc 自定义归档，可用pg_restore选择恢复对象。普通文本SQL则用psql导入，不能交给pg_restore。pg_restore --list 只能证明归档可读、列出目录，不能证明能恢复；必须在独立目标实际导入并查询。

使用同版本18.6客户端避免跨版本差异；pg_dump不能导出比自己新主版本的服务器。升级主版本前阅读目标版本发行说明、扩展兼容和恢复要求，不直接把旧数据卷交给新主版本启动。官方逻辑备份、pg_dump

独立数据库恢复与数据校验

完整脚本先在Linux容器内写二进制归档，再创建独立 postgres_restore 空数据库，pg_restore --exit-on-error --no-owner 导入，最后逐行比较 products 与 reservations 的全部列（包含创建时间）。避开PowerShell重定向转码二进制归档的问题。该目标和源库使用同一个实例，属于独立库恢复演练，不是独立主机或灾难隔离实测。

"""Custom archive into a NEW database on the dedicated instance; refuses an existing target."""
import json
import subprocess
from inventory import connect


def docker(*args):
    result = subprocess.run(["docker", *args], check=True, capture_output=True, text=True, encoding="utf-8")
    return result.stdout.strip()


name = "showdocs-m4b-postgres-main"
# pg_dump writes its binary archive inside Linux: PowerShell never transcodes it.
docker("exec", name, "pg_dump", "-U", "lab", "-d", "postgres_lab", "-Fc", "-f", "/tmp/postgres-lab.dump")
listing = docker("exec", name, "pg_restore", "--list", "/tmp/postgres-lab.dump")
# No DROP or --clean: a second invocation must fail at createdb, protecting the target.
docker("exec", name, "createdb", "-U", "lab", "-T", "template0", "postgres_restore")
docker("exec", name, "pg_restore", "-U", "lab", "--exit-on-error", "--no-owner", "-d", "postgres_restore", "/tmp/postgres-lab.dump")
with connect() as source, connect("postgres_restore") as restored:
    queries = ["SELECT sku,name,stock,price,note,created_at FROM products ORDER BY sku",
               "SELECT request_id,sku,quantity,created_at FROM reservations ORDER BY request_id"]
    for query in queries:
        assert source.execute(query).fetchall() == restored.execute(query).fetchall()
    count = restored.execute("SELECT count(*) FROM reservations").fetchone()[0]
    assert count > 0, "run verify.py first to seed meaningful data"
    print(json.dumps({"restoredReservations": count, "archiveListing": listing,
                      "database": "postgres_restore", "sameInstance": True}, ensure_ascii=False, indent=2))

PowerShell在仓库根目录、设置PGPASSWORD后运行：

examples/postgres/.venv/Scripts/python.exe -X utf8 examples/postgres/verify.py
examples/postgres/.venv/Scripts/python.exe -X utf8 examples/postgres/backup_restore.py

预期退出0，restoredReservations=1，脚本对两张表排序后的业务列逐值比较，包括中文名、numeric金额及 created_at；不检查角色权限、扩展或所有DDL等价性。脚本用 assert 做数据断言，不要用 python -O 运行。并发写入期间“实时源库”和备份时刻可能不同，本课校验期间暂停实验写入；生产演练应以备份快照时点的校验清单为依据。

脚本固定恢复库名 postgres_restore，且 Docker 操作固定容器名；执行前确认 Python 连接的 PGHOST/PGPORT 也指向同一容器的 127.0.0.1:25432，避免与远端源库混比。目标已经存在时 createdb 失败，脚本非零退出，不自动DROP或清空目标。重跑前先核对是否可丢弃该专用库，如要保留目标，需另行修改脚本及 connect 的库名白名单使用新的独立实验库；本例没有目标库命令行参数。否则由操作者显式删除本课恢复库。不要为了脚本成功加--clean覆盖未知数据库。恢复失败的部分目标需调查或重新创建空目标，不能把有些表成功当整体成功。

需要把归档带离容器时执行 docker cp showdocs-m4b-postgres-main:/tmp/postgres-lab.dump ./postgres-lab.dump，再记录文件哈希、备份时点、版本与恢复证据。本课归档留在任务容器内供检查，删除容器前应按需要保留；示例中没有将这个临时文件伪称外部持久备份。

连接、认证和 SQL 状态诊断

connection refused 先查实例是否运行、25432映射是否存在和实际PGHOST/PGPORT；不要先改SQL。pg_isready 的 accepting connections 只证明服务接受连接，不保证用户名密码正确。容器内本地socket成功也不能替代主机TCP认证检查。

docker ps --filter name=showdocs-m4b-postgres-main
docker exec showdocs-m4b-postgres-main pg_isready -U lab -d postgres_lab
docker logs --tail 50 showdocs-m4b-postgres-main

password authentication failed 检查角色和凭据；no pg_hba.conf entry 检查连接来源、数据库、用户及加密要求匹配的认证规则，按最小权限配置，不能把所有来源改trust。database does not exist 检查连接库名和实际目标，不是重新执行用户密码设置。

current transaction is aborted 表示当前事务中早前语句失败，先记录首个SQLSTATE再ROLLBACK；本课程库函数通过SAVEPOINT缩小失败范围。23505 唯一冲突、23503 外键冲突、23514 CHECK失败、23502 非空失败应当映射业务含义，不以重连掩盖数据问题。具体失败在真实测试可复现。

锁等待与长事务

查询 pg_stat_activity 的state、xact_start、wait_event_type、wait_event以及 pg_blocking_pids(pid)，先找持锁事务和业务来源。idle in transaction 表示客户端开着事务却没继续工作，可能持续持锁、保留旧版本。不要看到慢查询就直接终止进程；先判断事务是否在正常业务中，取消语句与终止会话影响不同。

SELECT pid,state,xact_start,wait_event_type,wait_event,
       pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE datname=current_database() ORDER BY pid;

查看他人会话可能需要额外权限。本课lab是镜像初始化角色，有较大权限，只用于隔离演示；生产应用应分开迁移角色和最小权限业务角色。历史测试记录有行锁超时，不声称完成主备故障、死锁全图或生产监控部署。

任务资源与清理

实验服务容器是 showdocs-m4b-postgres-main，数据卷 showdocs-m4b-postgres-data；历史交付还使用过 Linux 测试客户端容器 showdocs-m4b-postgres-python，当时已退出且没有专用数据卷；本轮未检查任何容器当前状态。清理前确认名称与需要保留的归档。历史 docker run 路径可显式删除确认可丢弃的 showdocs-m4b-postgres-main 及存在时的客户端容器，再 docker volume rm showdocs-m4b-postgres-data；该操作删除本课数据库，不能对其他容器或卷套通配符。若按本课 Compose 路径启动，先执行 docker compose -p showdocs-m4b-postgres -f examples/postgres/compose.yaml down（默认保留命名卷），确定数据可丢弃后再显式删除上述命名卷。本站原有showdocs容器不属于这些资源。

练习：解释为什么恢复后的SELECT成功仍不足以证明业务可恢复，设计最少两个附加检查。提示：把“数据相同”“对象与权限可用”“应用流程可完成”分别列出来，找脚本未覆盖的项。解答见复习。

PostgreSQL 连接失败

先确认服务可达，再检查数据库、用户和认证规则。

确定客户端实际连接的主机、端口和数据库。使用交互式密码输入或受保护的凭据文件，避免在命令行和日志中暴露密码。

排查步骤

1. 检查服务是否接受连接

pg_isready -h <host> -p 5432 -d <database> -U <user>

预期结果: 返回 accepting connections 时说明服务可达，但不代表凭据一定正确。

2. 取得完整认证错误

psql -h <host> -p 5432 -U <user> -d <database> -W

预期结果: 交互式输入密码并记录错误类型，不在命令中明文放密码。

3. 连接成功后核对目标

psql -h <host> -p 5432 -U <user> -d <database> -W -c "SELECT current_user, current_database();"

预期结果: 输出的用户与数据库和预期一致，排除连错环境。

常见报错

connection refused

原因: 目标主机端口没有可达的 PostgreSQL 监听。

处理方式: 检查主机名、端口、服务状态、listen_addresses 和网络策略。

password authentication failed / no pg_hba.conf entry

原因: 凭据不正确，或客户端来源与认证规则不匹配。

处理方式: 核对用户、数据库、来源地址与 pg_hba.conf 规则，按最小权限修复。

相关文档

官方资料

• https://www.postgresql.org/docs/current/app-pg-isready.html
• https://www.postgresql.org/docs/current/client-authentication-problems.html