1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Supabase を GitHub Actions で毎日 pg_dump して R2 に置き、週1で復元して行数を照合する

1
Posted at

Supabase のデータベースを GitHub Actions で毎日 pg_dump して Cloudflare R2 に置き、週に 1 回そのダンプを使い捨ての Postgres に戻して、テーブルごとの行数がダンプの中身と合うかを確かめる。この記事はその構成のメモで、ワークフローの YAML と補助スクリプトを載せる。復元チェックのスクリプトはローカルの Postgres 17 で動作を確かめたが、ワークフロー自体は GitHub Actions 上ではまだ実行していないので、使う前に一度手動実行(workflow_dispatch)で試してほしい。

私たちはこの一連の処理をまとめた Supabase バックアップ用の CLI を MIT ライセンスで公開しているが、この記事ではそれを使わず、GitHub Actions と PostgreSQL 標準のツールだけで組む。中で何をしているかが分かれば、道具は何でもいい。

前提は次のとおり。

  • Supabase はホスト版。バックアップ対象は public スキーマ
  • 置き場所は R2。他の S3 互換ストレージを使う場合は、エンドポイントとリージョンの設定を合わせる
  • バックアップ用と復元チェック用でワークフローを 2 つに分ける

Storage のファイルは対象外。データベースの復元では戻らないので、必要なら別に取ること。

準備するシークレット

リポジトリの Settings → Secrets and variables → Actions に入れておく。

名前 中身
SUPABASE_DB_URL Supabase の Connect → Session pooler の接続文字列
R2_ENDPOINT https://<account-id>.r2.cloudflarestorage.com
R2_BUCKET バケット名
R2_ACCESS_KEY_ID / R2_SECRET_ACCESS_KEY R2 の API トークン(対象バケットの読み書き)

接続文字列は Session pooler を使う。Direct connection のホスト db.<ref>.supabase.co は IPv6 のアドレスしか返さないことがあり(IPv4 アドオンなしの場合)、GitHub のホストランナーは IPv6 に対応していないので、そのままだとつながらない。Session pooler はポート 5432 で、pg_dump のように 1 本の接続を使い続けるツールと相性がいい。

落とし穴: ランナーの pg_dump が古い

最初にはまったのがここ。ubuntu-24.04 のランナーには最初から PostgreSQL が入っているが、バージョンは 16 系(執筆時点で 16.15)。いまの Supabase の新規プロジェクトは Postgres 17 なので、そのまま pg_dump を叩くとこう言われて止まる。

pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 17.6; pg_dump version: 16.15

pg_dump はサーバーと同じか新しいメジャーバージョンでないと動かない。Ubuntu の標準リポジトリは新しいメジャーが出るのが遅いので、PostgreSQL 公式の apt リポジトリ(PGDG)から postgresql-client-17 を入れる。

もう 1 つ注意がある。Ubuntu の /usr/bin/pg_dump は pg_wrapper というラッパーで、インストール済みのクラスタに合わせてバージョンを選ぶ。ランナーには 16 のクラスタがあるので、17 のクライアントを入れても 16 が呼ばれることがある。確実にするため、17 の bin を $GITHUB_PATH で PATH の先頭に足し、ジョブのログに pg_dump --version を出しておく。

バックアップ用ワークフロー

.github/workflows/supabase-backup.yml

name: supabase-backup

on:
  schedule:
    - cron: "0 18 * * *" # 毎日 03:00 JST
  workflow_dispatch: {}

permissions:
  contents: read

# 前の実行が長引いても重ならないようにする(egress が二重にかかるのを避ける)
concurrency:
  group: supabase-backup
  cancel-in-progress: false

jobs:
  backup:
    runs-on: ubuntu-24.04
    timeout-minutes: 60
    env:
      PG_MAJOR: "17"
      AWS_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }}
      AWS_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }}
      AWS_DEFAULT_REGION: auto
      S3_ENDPOINT: ${{ secrets.R2_ENDPOINT }}
      S3_BUCKET: ${{ secrets.R2_BUCKET }}
    steps:
      - name: Install PostgreSQL client from PGDG
        run: |
          sudo install -d /usr/share/postgresql-common/pgdg
          curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
            | sudo gpg --dearmor -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
          echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
            | sudo tee /etc/apt/sources.list.d/pgdg.list
          sudo apt-get update
          sudo apt-get install -y "postgresql-client-${PG_MAJOR}"
          # pg_wrapper が 16 を選ばないよう、17 の bin を PATH の先頭に足す
          echo "/usr/lib/postgresql/${PG_MAJOR}/bin" >> "$GITHUB_PATH"

      - name: Dump
        env:
          DATABASE_URL: ${{ secrets.SUPABASE_DB_URL }}
        run: |
          pg_dump --version
          pg_dump --schema=public --format=custom --file=dump.pgcustom "$DATABASE_URL"
          ls -l dump.pgcustom

      - name: Upload to R2
        run: |
          key="supabase/$(date -u +%Y-%m-%dT%H-%M-%SZ)/dump.pgcustom"
          aws s3 cp dump.pgcustom "s3://${S3_BUCKET}/${key}" --endpoint-url "$S3_ENDPOINT"
          echo "uploaded: ${key}"

AWS CLI はランナーに最初から入っている(執筆時点で 2.40.0)ので、R2 へのアップロードはエンドポイントを指定するだけで済む。キーは日時のフォルダに分けておくと、復元側で「一番新しいもの」を文字列ソートで選べる。

--format=custom にしておくと圧縮され、pg_restore でテーブル単位の操作もできる。古いダンプの削除はワークフローではやらず、R2 のライフサイクルルール(例: 30 日で削除)に任せている。削除処理を書かなければ、スクリプトの書き間違いでバックアップを消すことはない。ただし R2 の読み書き用トークン自体には削除の権限も含まれるので、トークンが漏れたときの備えにはならない。

復元チェック用ワークフロー

ここからが本題。GitHub Actions のサービスコンテナで空の Postgres 17 を立て、最新のダンプを戻して確かめる。

チェックしたいのは 2 つ。

  1. pg_restore のエラーが想定内のものだけか
  2. テーブルごとの行数が、ダンプに入っている行数と一致するか

pg_restore は途中でエラーが出ても最後まで走るので、終了コードだけ見ても「どのテーブルが消えたか」は分からない。逆に、Supabase のダンプを素の Postgres に戻すと必ず出るエラーもあるので、全部を失敗扱いにもできない。そこでログを残して、想定内のものを除いた残りで判定する。

先にスタブを作る

Supabase のテーブルは、列のデフォルト値や RLS ポリシーで auth.uid() を使っていることが多い。素の Postgres には auth スキーマがないので、そのまま戻すと該当テーブルの CREATE TABLE が失敗し、テーブルごとデータが消える。これを避けるために、関数とロールの空の実装を先に作っておく。

.github/restore-check/stubs.sql

create role anon nologin;
create role authenticated nologin;
create role service_role nologin;
create schema auth;
create function auth.uid()   returns uuid  language sql stable as $$ select null::uuid $$;
create function auth.role()  returns text  language sql stable as $$ select null::text $$;
create function auth.jwt()   returns jsonb language sql stable as $$ select null::jsonb $$;
create function auth.email() returns text  language sql stable as $$ select null::text $$;

行数の照合スクリプト

本番に count(*) を投げて比べると、ダンプの後に増えた行の分だけずれる。なので、ダンプそのものから数える。custom 形式のダンプは pg_restore -f - で SQL に戻せて、テーブルのデータは COPY ... FROM stdin; から \. までの 1 行が 1 レコードになる(値の中の改行は \n にエスケープされる)。

.github/restore-check/compare-counts.sh

#!/usr/bin/env bash
set -euo pipefail
dump="$1"

# ダンプ側: COPY ブロックの行数をテーブルごとに数える
pg_restore --data-only -f - "$dump" \
  | awk '/^COPY /{t=$2; n=0; next} /^\\\.$/{print t, n; t=""; next} t!=""{n++}' \
  | sort > dump_counts.txt

# 復元側: public の通常テーブルを count(*) する(マテビューは COPY されないので除外)
psql "$TARGET_URL" -Atc "
  select format('%I.%I', n.nspname, c.relname)
  from pg_class c join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'public' and c.relkind = 'r'
  order by 1" \
  | while read -r t; do
      echo "$t $(psql "$TARGET_URL" -Atc "select count(*) from $t")"
    done \
  | sort > restored_counts.txt

if diff dump_counts.txt restored_counts.txt; then
  echo "row counts match ($(wc -l < dump_counts.txt) tables)"
else
  echo "::error::row counts differ"
  exit 1
fi

ローカルの Postgres 17 で、空のテーブル、大文字を含むテーブル名、改行を含む値、マテリアライズドビューがある状態で試して、意図どおりに一致した。パーティションテーブルは親ではなく各パーティションに COPY されるので、親は relkind = 'p' で自然に外れる。

ワークフロー本体

.github/workflows/supabase-restore-check.yml

name: supabase-restore-check

on:
  schedule:
    - cron: "0 19 * * 0" # 毎週月曜 04:00 JST(バックアップの 1 時間後)
  workflow_dispatch: {}

permissions:
  contents: read

jobs:
  restore-check:
    runs-on: ubuntu-24.04
    timeout-minutes: 120
    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s
          --health-timeout 5s
          --health-retries 10
    env:
      PG_MAJOR: "17"
      TARGET_URL: postgresql://postgres:postgres@localhost:5432/postgres
      AWS_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }}
      AWS_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }}
      AWS_DEFAULT_REGION: auto
      S3_ENDPOINT: ${{ secrets.R2_ENDPOINT }}
      S3_BUCKET: ${{ secrets.R2_BUCKET }}
    steps:
      - uses: actions/checkout@v4

      - name: Install PostgreSQL client from PGDG
        run: |
          sudo install -d /usr/share/postgresql-common/pgdg
          curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
            | sudo gpg --dearmor -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
          echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
            | sudo tee /etc/apt/sources.list.d/pgdg.list
          sudo apt-get update
          sudo apt-get install -y "postgresql-client-${PG_MAJOR}"
          echo "/usr/lib/postgresql/${PG_MAJOR}/bin" >> "$GITHUB_PATH"

      - name: Download latest dump
        run: |
          latest=$(aws s3 ls "s3://${S3_BUCKET}/supabase/" --endpoint-url "$S3_ENDPOINT" \
            | awk '/PRE/{print $2}' | sort | tail -n 1)
          test -n "$latest" || { echo "::error::no snapshot found"; exit 1; }
          echo "snapshot: ${latest}"
          aws s3 cp "s3://${S3_BUCKET}/supabase/${latest}dump.pgcustom" dump.pgcustom \
            --endpoint-url "$S3_ENDPOINT"

      - name: Restore
        run: |
          psql "$TARGET_URL" -v ON_ERROR_STOP=1 -f .github/restore-check/stubs.sql
          # エラーがあっても最後まで走らせ、ログで判定する
          pg_restore --no-owner --no-privileges -d "$TARGET_URL" dump.pgcustom 2> restore.log || true
          cat restore.log
          # 想定内: 空の DB にも public がある / このサンドボックスには auth.users がない
          if grep 'ERROR' restore.log \
               | grep -v 'schema "public" already exists' \
               | grep -v 'auth.users'; then
            echo "::error::unexpected restore errors"
            exit 1
          fi

      - name: Compare row counts
        run: bash .github/restore-check/compare-counts.sh dump.pgcustom

ランナーに入っている PostgreSQL 16 のサービスは既定で止まっているので、サービスコンテナにポート 5432 をそのまま渡せる。

auth.users のエラーを除外しているのは、profiles.id → auth.users(id) のような外部キーがこのサンドボックスでは作れないから。ここで通っても、本番で新しい Supabase プロジェクトに戻すときは、空の auth.users を参照して外部キーの作成が失敗する。本番の復元手順には「先に auth.users と auth.identities を戻す」を入れておくこと。

もう 1 つ、この復元チェックは GRANT を見ていない。--no-privileges で戻しているので、anon や authenticated への権限は戻らない。Supabase は 2026 年 10 月 30 日から既存プロジェクトでも public の新しいテーブルに自動で権限を付けなくなったので、本番の復元では anon や authenticated に必要な GRANT も戻すこと。権限のないテーブルに Data API 経由でアクセスすると 42501 permission denied になる。ダンプに GRANT が入っているかは pg_restore -l dump.pgcustom | grep ACL で確認できる。

egress 料金の見積もり

毎日のバックアップで一番効いてくるのは、Supabase 側の egress(外向き転送量)。pg_dump で取り出したデータは Supabase の請求では uncached egress になる。執筆時点の料金は次のとおり。

  • 含まれる量: Free 5 GB、Pro 250 GB、Team 250 GB(いずれも月あたり)
  • 超過分: $0.09/GB

転送量の目安は、インデックスを除いたテーブルのサイズになる。インデックスはダンプに入らず、復元時に作り直されるから。

select pg_size_pretty(sum(pg_table_size(c.oid)))
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r';

Pro で、egress がバックアップだけだとすると、だいたいこうなる。

テーブルのサイズ 毎日(月 30 回) 毎週(月 4〜5 回)
10 GB 300 GB → 超過 50 GB で約 $4.50/月 枠内
50 GB 1,500 GB → 超過 1,250 GB で約 $112/月 約 215 GB で枠内

アプリ自体の egress も同じ枠を使うので、実際はこれより早く枠を超える。大きいデータベースなら毎日ではなく毎週にするか、対象テーブルを絞るほうがいい。

Pro には Spend Cap があり、既定でオンになっている。オンのまま枠を超えると課金はされない代わりに、猶予期間の後で組織のプロジェクトが制限される。バックアップのせいでアプリ側が止まるのは避けたいので、毎日回すなら Spend Cap の設定も確認しておく。

復元チェックの側は R2 からダウンロードするだけで、Supabase の egress は発生しない。R2 はダウンロードの転送料金がかからないので、週 1 回なら費用は保存容量と操作回数の分だけで済む。置き場所を AWS S3 にした場合は、ランナーへのダウンロードがインターネット向けの転送料金になる。

定期実行する前に確認すること

  • 復元チェックが失敗したときに気づけるよう、リポジトリの Actions の通知を有効にしておく。公開リポジトリの場合、60 日間リポジトリに動きがないとスケジュール実行のワークフローは GitHub に自動で無効にされるので、それも気にしておく
  • 行数の一致は「データが戻った」ことの確認で、アプリが動くことの確認ではない。スタブの auth.uid() は null を返し、チェックはスーパーユーザーで走るので、RLS の挙動はここでは分からない
  • 戻したデータベースに対して、アプリ側の簡単なクエリ(注文が必ず存在するユーザーを指している、など)を足すと、チェックの意味が一段増す

冒頭で触れた backupdrill は、この構成を CLI にまとめたものだ。backupdrill backup はダンプに加えて、接続ロールに読み取り権限があれば Auth ユーザーを、プロジェクトの Storage 用 S3 キーを設定すれば Storage ファイルも保存する。backupdrill drill は保存したバックアップをスタブ入りの使い捨て Postgres に戻し、行数を照合する。GRANT も記録しておき、復元時に戻す。リポジトリには GitHub Actions のワークフロー例も置いてある。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?