AIGC标识 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 两个扩展
  • 四张表:customers 1200 行(每 100 行有一条带换行和制表符的备注),orders 8000 行,events 30 万行(JSONB 加 GIN 索引),documents 500 行(有一个 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

posted @ 2026-11-17 12:13  superma11  阅读(5)  评论(0)    收藏  举报