pg_dump 定时备份到 S3 兼容存储:上传后怎么核对,恢复后该数什么
很多团队的 PostgreSQL 备份是这样的:一个定时任务跑 pg_dump,传到 S3 或者别的兼容 S3 的对象存储,任务是绿的,就当备份没问题。真要恢复的时候才发现文件不全、缺扩展、角色没建,或者干脆少了几张表。
我是 BackupDrill 的开发者之一,我们三个人一起做这个产品,做的就是 Supabase 数据库的备份和定期恢复演练。下面的内容不依赖我们的产品,只用 pg_dump、AWS CLI 和 Docker。重点讲三件事:上传前后怎么核对文件,定时任务放在 GitHub Actions 上要注意什么,以及恢复到一次性库以后到底该核哪些东西。最后列几个我这次实测碰到的失败。
备份脚本的另一种写法(带 age 加密和 systemd timer),我放在 pg_dump 定时备份到 S3 这篇英文指南里了,这里不重复,换个角度讲校验。
一、实验环境
- 源库:
pgvector/pgvector:pg17镜像起的 PostgreSQL 17.12,库名shop,装了vector和pg_trgm两个扩展 - 四张表:
customers1200 行(每 100 行有一条带换行和制表符的备注),orders8000 行,events30 万行(JSONB 加 GIN 索引),documents500 行(有一个vector(3)列,开了 RLS,策略给app_reader角色) - 对象存储:本机 Docker 里的 SeaweedFS 3.97,开 S3 接口
- 客户端:pg_dump / pg_restore 17.11,AWS CLI 2.40.3,Docker 29.8.2
custom 格式的 dump 是 13,207,998 字节,文中所有输出都是这套环境里跑出来的。
二、备份脚本:传完再问桶一次
#!/usr/bin/env bash
# pg-backup.sh:导出 → 本地自检 → 上传 → 上传后核对。任何一步失败都以非 0 退出
set -euo pipefail
: "${DATABASE_URL:?DATABASE_URL 没设}" "${S3_BUCKET:?S3_BUCKET 没设}"
stamp="$(date -u +%Y-%m-%dT%H-%M-%SZ)"
key="${S3_PREFIX:-pg}/$stamp.dump"
work="$(mktemp -d "${TMPDIR:-/tmp}/pg-backup.XXXXXX")"
trap 'rm -rf "$work"' EXIT
dump="$work/db.dump"
pg_dump --format=custom --file="$dump" "$DATABASE_URL"
# 把整个归档解压读一遍,导到一半断掉的文件在这里就会失败
pg_restore --file=/dev/null "$dump"
size="$(wc -c < "$dump" | tr -d ' ')"
sha="$(sha256sum "$dump" | cut -d' ' -f1)"
aws s3 cp "$dump" "s3://$S3_BUCKET/$key" --only-show-errors --metadata "sha256=$sha"
# 上传后核对:桶里这个对象的大小、元数据里的 sha256,要和本地一致
read -r remote_size remote_sha < <(aws s3api head-object --bucket "$S3_BUCKET" --key "$key" \
--query '[ContentLength, Metadata.sha256]' --output text)
if [ "$remote_size" != "$size" ] || [ "$remote_sha" != "$sha" ]; then
echo "上传后核对失败:本地 ${size} 字节 ${sha},桶里 ${remote_size} 字节 ${remote_sha}" >&2
exit 1
fi
echo "ok s3://$S3_BUCKET/$key $size 字节 sha256=$sha"
跑一次:
$ ./pg-backup.sh
ok s3://pg-backups/pg/shop/2026-11-16T15-28-39Z.dump 13207998 字节 sha256=b84666d4dfe1e1e417a4814f0399f06e98433c54ef461390791d3c11d8e95dab
几个地方解释一下。
先落盘,不走管道。 pg_dump | aws s3 cp - 的问题是 pg_dump 半路失败时,上传那头只看到输入结束,照样把半截文件传上去。落到本地再用 pg_restore --file=/dev/null 完整解压一遍,坏文件在离开机器之前就会报错。
sha256 写进对象元数据。 --metadata sha256=... 会变成对象上的 x-amz-meta-sha256 头,跟着对象走。演练时 head-object 一下就能拿到当初的校验和,校验和和文件存在同一个对象上,不用另存一个 .sha256 文件。
上传后的核对只能证明一半。 head-object 拿回来的是桶里对象的字节数和我们自己写进去的元数据。字节数对上,说明没有传一半;但每个字节对不对,要等演练时把文件下载回来重新算 sha256 才知道。这一步放在演练脚本里。
别拿 ETag 当 MD5 比。 网上不少脚本用 ETag 校验上传结果。ETag 能不能当 MD5 用,取决于上传方式和加密方式;AWS CLI 对大文件默认走分片上传,分片上传的 ETag 不是整个文件的 MD5:
$ md5sum shop.dump
db5d5a2efcb0c5df53fc4f86212227d6 shop.dump
$ aws s3api head-object --bucket pg-backups --key test/shop.dump --query ETag
"\"bc14b0dee79506cfb08207afab515b7c-4\""
后面带 -4 的就是分片上传的 ETag,拿它和文件 MD5 比,每次都会报不一致。AWS S3 现在也支持对象级的 CRC、SHA 校验和,但 S3 兼容服务支持得参差不齐。这次我加了 --checksum-mode ENABLED 去查 SeaweedFS,返回里没有任何 Checksum 字段。所以我用的是自己算的 sha256,写进对象元数据,下载后再核对,不依赖存储服务自己的校验和。
三、定时:放到 GitHub Actions 上
数据库在云上、手里又没有一台长期开着的机器,GitHub Actions 的定时任务是最省事的地方。workflow 文件大概是这样:
# .github/workflows/pg-backup.yml
name: pg-backup
on:
schedule:
- cron: "30 18 * * *" # UTC 时间,北京时间每天凌晨 2:30
workflow_dispatch: {} # 留一个手动触发,第一次先手动跑
concurrency:
group: pg-backup
cancel-in-progress: false # 上一次没跑完,下一次排队等,不要并发
jobs:
backup:
runs-on: ubuntu-24.04
timeout-minutes: 60
steps:
- uses: actions/checkout@v4
- name: 装和服务端同一主版本的客户端
run: |
sudo apt-get update
sudo apt-get install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt-get install -y postgresql-client-17
echo "/usr/lib/postgresql/17/bin" >> "$GITHUB_PATH"
- name: 备份
run: |
pg_dump --version
bash scripts/pg-backup.sh
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}
S3_BUCKET: ${{ secrets.S3_BUCKET }}
S3_PREFIX: pg/shop
AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
AWS_DEFAULT_REGION: us-east-1
AWS_ENDPOINT_URL: ${{ secrets.AWS_ENDPOINT_URL }} # 用 AWS S3 的话删掉这行
安装客户端那几行,我在 ubuntu:24.04 容器里跑过,装出来是 pg_dump (PostgreSQL) 17.12 (Ubuntu 17.12-1.pgdg24.04+1)。workflow 文件本身请先手动触发一次,看日志里 pg_dump --version 打出来的是不是你要的版本。$GITHUB_PATH 那一行的作用是把 17 的 bin 目录放到 PATH 最前面:runner 上可能已经装着别的版本,/usr/bin/pg_dump 是 Debian 的包装脚本,不一定挑中 17。
用 GitHub Actions 做定时,有几件事要心里有数:
- cron 按 UTC 算,而且负载高的时候会延迟开始,GitHub 文档里还说过负载特别高时排队的任务可能被丢掉。别把备份时间卡在业务窗口的边上,也别指望它准点。
- 公开仓库 60 天没有任何活动,定时 workflow 会被 GitHub 自动停用。 专门放备份脚本的仓库很容易两个月没人提交,用私有仓库能避开这个问题。
- 失败通知只发给最后一个改过 cron 那行的人。 那个人离职或者不看邮件,失败了也没人知道。可以在 workflow 最后加一个
if: failure()的步骤,往钉钉、飞书或企业微信的群机器人发消息。 - Supabase 用户注意连接串。 控制台里的 Direct connection 在项目没买 IPv4 附加项时只走 IPv6,在只有 IPv4 的网络里会直接连不上。备份用 Connect 面板里的 Session pooler 连接串。
演练也可以放在 Actions 上跑,GitHub 托管的 ubuntu runner 自带 Docker。复制一份 workflow,cron 改成每周一次(比如 "0 20 * * 0"),最后一步换成下面的 pg-drill.sh,再把桶的密钥换成一把只读的。
自己有服务器的话,crontab 一样能用,记得在 crontab 文件里写上 PATH,cron 默认的 PATH 很短,经常找不到装在 /usr/local/bin 下的 aws。
四、恢复演练:到底该核什么
#!/usr/bin/env bash
# pg-drill.sh:取桶里最新一份 dump,核 sha256,恢复进一次性容器,逐表对行数
# DRILL_IMAGE 默认 postgres:17;用了 pgvector 之类扩展的库要换成带这个扩展的镜像
# DRILL_ROLES="角色1 角色2":授权和 RLS 策略里写到的角色,dump 里没有,先建空壳
set -euo pipefail
: "${S3_BUCKET:?S3_BUCKET 没设}"
prefix="${S3_PREFIX:-pg}"
image="${DRILL_IMAGE:-postgres:17}"
name="pg-drill-$$"
work="$(mktemp -d "${TMPDIR:-/tmp}/pg-drill.XXXXXX")"
trap 'docker rm -f "$name" > /dev/null 2>&1 || true; rm -rf "$work"' EXIT
dump="$work/db.dump"
# 1. 最新一份。文件名是 UTC 时间戳,按字典序排就是按时间排
key="$(aws s3 ls "s3://$S3_BUCKET/$prefix/" | awk '{print $4}' | grep '\.dump$' | sort | tail -n 1)"
[ -n "$key" ] || { echo "FAIL: 桶里没有备份"; exit 1; }
aws s3 cp "s3://$S3_BUCKET/$prefix/$key" "$dump" --only-show-errors
want="$(aws s3api head-object --bucket "$S3_BUCKET" --key "$prefix/$key" --query Metadata.sha256 --output text)"
got="$(sha256sum "$dump" | cut -d' ' -f1)"
[ "$want" = "$got" ] || { echo "FAIL: sha256 不一致,元数据 ${want},下载下来 ${got}"; exit 1; }
echo "sha256 ok $key"
# 2. 从 dump 里数每张表的行数:COPY 文本格式一行就是一条记录,字段里的换行会被转义成 \n
pg_restore --data-only --file=- "$dump" | awk '
/^COPY / { table = $2; rows = 0; inside = 1; next }
inside && $0 == "\\." { print table, rows; inside = 0; next }
inside { rows++ }' | sort > "$work/expected.txt"
# 3. 一次性容器,端口随机,只听本机
docker run -d --name "$name" -e POSTGRES_PASSWORD=drill -p 127.0.0.1::5432 "$image" > /dev/null
port="$(docker port "$name" 5432/tcp | head -n 1 | cut -d: -f2)"
export PGHOST=127.0.0.1 PGPORT="$port" PGUSER=postgres PGPASSWORD=drill PGDATABASE=postgres
until psql -X -q -c 'select 1' > /dev/null 2>&1; do sleep 1; done
# 扩展:dump 里建过的,沙箱镜像里得有
pg_restore --list "$dump" | awk '$4 == "EXTENSION" {print $6}' | sort -u | while read -r ext; do
ok="$(psql -X -A -t -c "select count(*) from pg_available_extensions where name = '$ext'")"
[ "$ok" = 1 ] || echo "缺扩展 ${ext}(镜像 ${image} 里没有)"
done
for role in ${DRILL_ROLES:-}; do psql -X -q -c "create role \"$role\" nologin"; done
# 4. 恢复。不加 --exit-on-error,让它跑完,报错全部留下来按类归一下
start=$SECONDS
pg_restore --no-owner --dbname=postgres "$dump" 2> "$work/restore.err" || true
errors="$(grep -c 'ERROR:' "$work/restore.err" || true)"
echo "pg_restore 用时 $((SECONDS - start))s,报错 ${errors} 条"
grep -o 'ERROR: .*' "$work/restore.err" | sort | uniq -c || true
# 行数全对也不算过:建不出来的策略、授权不影响行数,却是真问题
failed=0
[ "$errors" -eq 0 ] || failed=1
# 5. 逐表对行数:dump 里数出来的 vs 恢复后 count(*)
while read -r table expected; do
if ! actual="$(psql -X -A -t -c "select count(*) from $table" 2> /dev/null)"; then
echo "缺表 ${table}(dump 里 ${expected} 行)"; failed=1; continue
fi
if [ "$actual" != "$expected" ]; then
echo "行数不对 $table dump ${expected},恢复后 ${actual}"; failed=1; continue
fi
printf 'ok %-20s %s 行\n' "$table" "$actual"
done < "$work/expected.txt"
[ "$failed" -eq 0 ] || { echo "FAIL: $key"; exit 1; }
echo "PASS: $key"
这个脚本核了五样东西,按顺序说。
1. sha256。 下载下来重新算,和上传时写进元数据的比。上一节说的「另一半」就在这里补上。
2. 行数拿 dump 里的数来比。 常见做法是备份时在源库上记一份 count(*) 或者 pg_stat_user_tables.n_live_tup。前者要在生产库上全表扫描,后者是估算值,而且和 dump 不是同一个时刻的数据,库一直有写入的话本来就对不上。
其实 dump 里就有准确答案。pg_dump 在一个快照里导出所有表,每张表的数据是一段 COPY ... FROM stdin;,一行一条记录,以 \. 结尾。把这些行数出来,就是导出那一刻每张表的确切行数,恢复后的 count(*) 必须一行不差。整个过程只读本地文件,不碰生产库。
有人会担心字段里带换行会把一条记录拆成两行。不会,COPY 文本格式会把换行转义掉。customers 里那条带换行的备注,在 dump 里是这样的:
100 customer 100 第一行\n第二行\t带制表符
脚本里的 awk 写得比较省事,表名里有空格或者引号时 $2 取不全,那种情况要换成正经的解析。另外分区表的父表、物化视图在 dump 里没有自己的 COPY 段,它们不会出现在这份清单里。
3. 扩展。 pg_restore --list 列出的目录里有 EXTENSION 条目,逐个去沙箱的 pg_available_extensions 查。沙箱用的镜像没装这个扩展,后面依赖它的表就建不出来。
4. 角色。 角色属于整个实例,pg_dump 只导一个库,不会把角色导出来。--no-owner 能跳过表的属主,跳不过 GRANT ... TO app_reader 和 CREATE POLICY ... TO app_reader。脚本用 DRILL_ROLES 先建空壳。
5. 报错按类型归并。 pg_restore 默认遇到错误记一条接着跑,几十条报错混在一起很难看。用 grep -o 'ERROR: .*' | sort | uniq -c 把相同的错误合并以后,先找缺扩展、缺角色这类,再看它们引起的后续报错。
跑一次看看
先用不带 pgvector 的 postgres:17.12 镜像当沙箱,也不建角色:
$ DRILL_IMAGE=postgres:17.12 ./pg-drill.sh
sha256 ok 2026-11-16T15-28-39Z.dump
缺扩展 vector(镜像 postgres:17.12 里没有)
pg_restore 用时 3s,报错 13 条
1 ERROR: extension "vector" does not exist
1 ERROR: extension "vector" is not available
5 ERROR: relation "public.documents" does not exist
1 ERROR: relation "public.documents_id_seq" does not exist
4 ERROR: role "app_reader" does not exist
1 ERROR: type "public.vector" does not exist
ok public.customers 1200 行
缺表 public.documents(dump 里 500 行)
ok public.events 300000 行
ok public.orders 8000 行
FAIL: 2026-11-16T15-28-39Z.dump
13 条报错,根因只有两个:沙箱里没有 vector,所以 documents 建不出来,后面的序列、数据、索引、策略全跟着失败;没有 app_reader,授权和策略失败。其他三张表行数全对。如果只看「恢复出来几张表、有没有数据」,很容易被这三个 ok 带过去。
换成 pgvector 镜像,但还是不建角色:
$ DRILL_IMAGE=pgvector/pgvector:pg17 ./pg-drill.sh
sha256 ok 2026-11-16T15-28-39Z.dump
pg_restore 用时 2s,报错 5 条
5 ERROR: role "app_reader" does not exist
ok public.customers 1200 行
ok public.documents 500 行
ok public.events 300000 行
ok public.orders 8000 行
FAIL: 2026-11-16T15-28-39Z.dump
四张表的行数和 dump 完全一致,但 5 条报错是 4 条 GRANT 和 documents 上的 1 条 CREATE POLICY,授权和策略都没恢复出来。只核行数会漏掉这类错误,所以脚本里只要有报错就判失败。
最后把角色也补上:
$ DRILL_IMAGE=pgvector/pgvector:pg17 DRILL_ROLES=app_reader ./pg-drill.sh
sha256 ok 2026-11-16T15-28-39Z.dump
pg_restore 用时 2s,报错 0 条
ok public.customers 1200 行
ok public.documents 500 行
ok public.events 300000 行
ok public.orders 8000 行
PASS: 2026-11-16T15-28-39Z.dump
这次通过了。演练里补过的东西(装哪个扩展、建哪些角色)要同步写进恢复手册,真出事的那天,新服务器上也得先做这几步。
五、这次碰到的几种失败
pg_dump 比服务端旧。 用 16 的客户端去导 17 的库,直接拒绝:
pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 17.12 (Debian 17.12-1.pgdg12+1); pg_dump version: 16.16 (Debian 16.16-1.pgdg13+1)
服务端升级了大版本,定时任务机器上的客户端没跟着升,就是这个报错。GitHub Actions 上装客户端那一步要写死主版本,原因也在这里。
pg_restore 比归档旧。 17 导出的 custom 格式归档,16 的 pg_restore 连目录都读不了:
pg_restore: error: unsupported version (1.16) in file header
演练机、恢复手册里写的那台机器,客户端版本都要不低于导出时用的版本。
沙箱的小版本比源库旧。 这个是我这次意外碰到的。本机缓存的 postgres:17 镜像还是 17.11,用它恢复 17.12 源库的 dump,四张表一张都没建出来,报错里有这么一条:
pg_restore: error: could not execute query: ERROR: unrecognized parameter "pg_attver"
Command was: CREATE TABLE public.customers (
id bigint NOT NULL,
name text NOT NULL,
note text
);
ALTER TABLE ONLY public.customers ALTER COLUMN id SET (pg_attver=26920488);
源库 17.12 上,新建表的每一列 attoptions 里都带着 pg_attver,17.11 不认这个参数。17.12 的发布说明里我没找到相关的条目,原因还没查清。能确定的是:沙箱镜像别只写 postgres:17,写到小版本,并且跟着源库一起升。用 postgres:17.12 恢复同一份 dump,这条报错就没有了。
缺扩展、缺角色。 上一节已经演示过。扩展那条最容易漏的是 pgvector、PostGIS 这类不在官方镜像里的。
ETag 误报。 第二节说过,大文件分片上传后 ETag 不是 MD5,拿它校验会天天报警,报到最后没人看。
定时任务悄悄停了。 公开仓库 60 天不活动被停用,或者失败通知发到了一个没人看的邮箱。这类问题最好用「多久没成功过」来告警,而不是等「失败了」再告警。
六、换成现成的工具
上面这些我们做进了 BackupDrill 的 CLI,开源,MIT 协议,GitHub 上搜 backupdrill/cli,npm 包名 backupdrill。backup 会在桶里写 dump、恢复手册和一个 manifest.json,里面记了表清单、扩展、dump 的字节数和 sha256;drill 从桶里取最新一份,在临时容器里恢复后逐项检查。2.11.0 起,行数核对也改成了从 dump 本身数,和上面脚本的思路一样,差一行就判失败。
拿同一个库跑了一次:
$ npx backupdrill@2.11.0 drill
→ Drilling snapshot 2026-11-16T15-34-20-063Z
→ Downloading dump…
→ Starting ephemeral pgvector/pgvector:pg17…
→ Restoring into ephemeral Postgres…
→ Tearing down ephemeral Postgres…
✓ archive integrity — sha256 matches (e6c1d74834da…)
✓ sandbox extensions — installed pg_trgm, vector
✓ pg_restore — completed in 2.3s (2 parallel jobs)
✗ post-data objects — 1 failed: pg_restore: error: could not execute query: ERROR: role "app_reader" does not exist Command was: CREATE POLICY documents_read ON public.documents FOR SELECT TO app_reader USING (true); ...
✓ table count — restored 4, manifest 4
✓ no missing tables — all manifest tables present
✓ populated tables came back — no populated table restored empty
✓ row counts match the backup — all 4 tables match the backup exactly (309,700 rows)
...
✗ Drill FAILED — snapshot 2026-11-16T15-34-20-063Z, 4 tables / 309,700 rows restored in 2.3s
它看到 vector 列,自动换成了 pgvector 镜像;行数全对,但 app_reader 没建,策略失败,整次演练判失败,结论和手写脚本一样。CLI 主要是给 Supabase 用的,沙箱里预建的是 anon、authenticated、service_role 这几个 Supabase 角色,自建的角色还得自己处理;auth.uid() 这类桩函数它会自动建好。如果你用的是前面的手写脚本去演练 Supabase 项目,沙箱里得自己先补这些函数和角色,补法我写在 验证 Supabase 备份能不能恢复 这篇里。
最后说一句:不管用脚本还是用工具,演练要定期跑。库结构、扩展、角色或者客户端版本变过之后,拿一份新备份再跑一次。
作者:Super Lede

浙公网安备 33010602011771号