PostgreSQL 容器数据库备份与恢复手册

适用环境：容器 sub2api-postgres，数据库 sub2api，用户 sub2api
前提：sub2api 用户已拥有超级用户或数据库所有者权限，本环境无 postgres 角色。

一、备份数据库#

在宿主机终端执行，将数据库导出为 SQL 文件：

1
docker exec -t sub2api-postgres pg_dump -U sub2api --no-owner sub2api > sub2api_backup.sql

--no-owner：移除对象所有者，恢复时所有对象将属于执行恢复的用户。
若需连同权限一起导出，去掉 --no-owner 但恢复时需确保目标环境中存在相同角色。
如需密码：docker exec -e PGPASSWORD=你的密码 -t ...

二、恢复数据库（全量重建方式）#

当需要将备份恢复到 同一个数据库（覆盖现有数据）时，必须清空目标库并重新导入。

1. 清理现有数据库（可选，若库为空可跳过）#

连接到 postgres 跳板库，终止连接、删除并重建 sub2api：

1
# 断开所有到 sub2api 的连接
2
docker exec -i sub2api-postgres psql -U sub2api -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'sub2api' AND pid <> pg_backend_pid();"
3

4
# 删除数据库
5
docker exec -i sub2api-postgres psql -U sub2api -d postgres -c "DROP DATABASE sub2api;"
6

7
# 重新创建并指定所有者
8
docker exec -i sub2api-postgres psql -U sub2api -d postgres -c "CREATE DATABASE sub2api OWNER sub2api;"

2. 授权 public 模式#

1
docker exec -i sub2api-postgres psql -U sub2api -d sub2api -c "GRANT ALL ON SCHEMA public TO sub2api;"

3. 导入备份#

1
cat sub2api_backup.sql | docker exec -i sub2api-postgres psql -U sub2api -d sub2api

三、仅清空表结构（保留数据库）#

若不想删除数据库，仅删除 public 模式下所有对象：

1
docker exec -i sub2api-postgres psql -U sub2api -d sub2api -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"
2
docker exec -i sub2api-postgres psql -U sub2api -d sub2api -c "GRANT ALL ON SCHEMA public TO sub2api;"

然后执行备份导入即可。

四、验证恢复结果#

1
# 查看所有表
2
docker exec -it sub2api-postgres psql -U sub2api -d sub2api -c "\dt"
3

4
# 检查序列值
5
docker exec -it sub2api-postgres psql -U sub2api -d sub2api -c "SELECT * FROM pg_sequences;"

五、常见错误及解决#

错误信息	原因	解决方法
`role "postgres" does not exist`	容器内无 `postgres` 角色	全部操作改用 `-U sub2api`
`database "sub2api" does not exist`	未指定数据库，默认连接同名库	加 `-d postgres` 指定跳板库
`cannot drop the currently open database`	当前连接在要删除的库中	先 `-d postgres` 再执行删除
`ERROR: multiple primary keys ... already exists`	目标库中已有表，导入时冲突	删除并重建数据库或清空 public 模式
`permission denied for schema public`	用户缺少 public 权限	执行 `GRANT ALL ON SCHEMA public TO sub2api;`
`extension "xxx" does not exist`	备份中含扩展，目标库未安装	手动创建：`CREATE EXTENSION IF NOT EXISTS pgcrypto;`

六、一键备份与恢复脚本#

备份脚本 (backup.sh)#

1
#!/bin/bash
2
docker exec -t sub2api-postgres pg_dump -U sub2api --no-owner sub2api > sub2api_backup_$(date +%Y%m%d_%H%M%S).sql
3
echo "备份完成"

恢复脚本 (restore.sh) – 重建数据库后导入#

1
#!/bin/bash
2
BACKUP_FILE="$1"
3
if [ -z "$BACKUP_FILE" ]; then
4
    echo "用法: $0 <备份文件.sql>"
5
    exit 1
6
fi
7

8
# 清理并重建
9
docker exec -i sub2api-postgres psql -U sub2api -d postgres <<EOF
10
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'sub2api' AND pid <> pg_backend_pid();
11
DROP DATABASE IF EXISTS sub2api;
12
CREATE DATABASE sub2api OWNER sub2api;
13
EOF
14

15
# 授权
16
docker exec -i sub2api-postgres psql -U sub2api -d sub2api -c "GRANT ALL ON SCHEMA public TO sub2api;"
17

18
# 导入
19
cat "$BACKUP_FILE" | docker exec -i sub2api-postgres psql -U sub2api -d sub2api
20
echo "恢复完成"

注意事项：

备份文件默认保存在当前目录，请确保磁盘空间充足。
恢复操作会删除现有数据，请提前确认或先备份。
建议定期验证备份文件可用性，可通过恢复到临时数据库测试。