Supabase の PostgreSQL データを pg_dump & pg_restore でローカルへ復元

私の環境ではこの方法でうまくいきましたが、他の環境では異なる可能性があります。

webアプリを作り、Renderにデプロイ、DBはSupabase。
何といってもRender、Supabaseの無料は嬉しい。

で、バックアップをローカルに落としてきたいということで今回、沼にはまり2日間格闘。

pg_dump & pg_restore を使えば簡単…のはずが、エラー連発＆文字化け！

まとめだけ見たい方はこちら

📌 1. はじめに

やりたいこと → Supabase のデータをローカルの PostgreSQL に移す
やったこと → pg_dump & pg_restore を試した
結果 → エラーの連続！ この記事では その試行錯誤と解決策を備忘録かねてまとめた

📌 2. [本来の手順]

✅ pg_dump でバックアップを取得

pg_dump -h southeast-1.pooler.supabase.com -p 5432 -U abcdefghijk -d postgres -Fc -f backup.dump

🔹 このコマンドの意味

• -h → Supabase のホスト名
• -p → ポート番号（デフォルトは 5432）

• -U → Supabase のユーザー名

• -d → データベース名（Supabase では通常 postgres）
• -Fc → カスタムフォーマットでダンプ（pg_restore で復元しやすい）
• -f backup.dump → backup.dump というファイルに出力

✅ pg_restore でローカルにリストア

pg_restore -U postgres -d local_db backup.dump

👉 ここでエラー発生！！ 😱

① 外部キー制約の警告

pg_dump: 警告: 次のテーブルの中で外部キー制約の循環があります

💡 原因

Supabase のデータは テーブル同士が外部キー（FOREIGN KEY）で結びついている
テーブルのデータをリストアする順番が合わないと、外部キー制約エラーが出る

🔹 解決策

pg_dump -h southeast-1.pooler.supabase.com -p 5432 -U abcdefghijk -d postgres -Fc --disable-triggers -f backup.dump

🛠 このオプションの意味

--disable-triggers → 外部キーの制約を一時的に無効化 する
→ これでリストアの順番が問題にならなくなる！

② ローカルDBの認証エラー

pg_restore: エラー: "localhost"(::1)、ポート5432のサーバーへの接続に失敗しました: FATAL: ユーザー"postgres"のパスワード認証に失敗しました

💡 原因

ローカルの PostgreSQL は パスワードなしでは接続できない設定になっていた

🔹 解決策

$env:PGPASSWORD = "your_local_db_password"
pg_restore -U postgres -d local_db backup.dump

🛠 $env:PGPASSWORD を設定すると、パスワードを毎回入力しなくてもOK！

③ Supabase 固有のスキーマ pgrst のエラー

pg_restore: エラー: could not execute query: ERROR: "pgrst_drop_watch" は存在しない

💡 原因

Supabase は PostgREST というAPI機能を持っている
pgrst_drop_watch は Supabase の内部関数 なので、ローカルの PostgreSQL では存在しない

🔹 解決策

pg_restore -h localhost -p 5432 -U postgres -d local_db --disable-triggers --exclude-schema=pgrst backup.dump

🛠 このオプションの意味
--exclude-schema=pgrst → pgrst スキーマをリストアから除外！
→ これで、ローカルに不要な Supabase の設定を無視できる

④ auth スキーマのエラー

pg_restore: エラー: could not execute query: ERROR: "issue_pg_net_access" は存在しない

💡 原因

auth スキーマは Supabase の認証関連（ユーザー管理）に使われている
ローカル環境には不要なので、リストア時にエラーが出る

🔹 解決策

pg_restore -h localhost -p 5432 -U postgres -d local_db --disable-triggers --exclude-schema=auth --exclude-schema=pgrst backup.dump

🛠 このオプションの意味
--exclude-schema=auth → auth スキーマを除外！
--exclude-schema=pgrst → pgrst も引き続き除外！

🚨 ⑤ anon ロールがないエラー

pg_restore: エラー: could not execute query: ERROR: "anon" は存在しない

💡 原因

Supabase では 「匿名ユーザー（anon）」のロールがデフォルトで作成される
ローカルにはこのロールがないため、リストア時にエラーが発生

🔹 解決策

psql -U postgres -d local_db -c "CREATE ROLE anon;"
psql -U postgres -d local_db -c "CREATE ROLE authenticated;"

🛠 CREATE ROLE で Supabase と同じロールを手動作成

⑥ pg_restore実行後エラーは無かったが、中身を確認すると・・・

符号化方式"UTF8"においてバイト列0xef 0xb8 0x8fである文字は符号化方式"SJIS"で等価な文字を持ちません

まず、現在のエンコーディングの設定をチェック：

SHOW server_encoding;
SHOW client_encoding;

結果、ローカルがSJISと判明。

今後もずっと UTF8 にしたいので、データベースの設定を変更：

ALTER DATABASE local_db SET client_encoding = 'UTF8';

これを実行した後、一度 psql を再起動してから試してみる！

\q  # psqlを終了
psql -U postgres -d local_db  # 再接続
SHOW client_encoding;  # 変更が反映されたか確認

が、しかし・・・何をやっても文字化け。
もともとはUTF8のはずなのに、どうして文字化けするのだろう？？？

その時、ふと気づいたのが、このエラー件数は途中で出てきたエラー件数と同じ。
そのエラー処理を忘れてリストアしたのであれば、それさえやっておけば、
文字化けしないのではないか？

その結果、

キターーーーー！！！

その後のtry&errorは長くなるので割愛するが、最終的にわかったのは、
PowerShell のエンコーディングに原因があるようだ。

最終的にうまくいった方法

🔥 PostgreSQL ローカルリストア手順

✅ 1. まずスキーマをリセット（データを完全削除）

psql -U postgres -d local_db -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

（※ これで既存のデータが完全に消えるので注意！）

---

✅ 2. anon ロールを手動で作成（必要な場合）

psql -U postgres -d local_db -c "CREATE ROLE anon;"
psql -U postgres -d local_db -c "CREATE ROLE authenticated;"

👆Supabase でエクスポートした backup.dump に anon ロールの権限設定が含まれている場合のみ必要！

（もしリストア時に anon に関するエラーが出なければ、この手順は不要）

---

✅ 3. pg_restore を -no-owner --no-privileges 付きで実行

pg_restore -U postgres -d local_db --schema=public --no-owner --no-privileges backup.dump

（👆 これで、オーナーや権限設定を無視してリストア）

---

💡 追加のポイント

1. リストア後にデータを確認

       
   psql -U postgres -d local_db
   
   

   sql
   
   SELECT * FROM questions LIMIT 5;
   
   

   → データが正しく復元されているか確認！

2. もし DROP SCHEMA を実行したくない場合

   
   psql -U postgres -d local_db -c "TRUNCATE TABLE questions, categories, difficulty_levels RESTART IDENTITY CASCADE;"
   
   

   （→ テーブルの中身だけ削除してリストア）

---

✅ まとめ

💡 anon ロールの作成は、エラーが出たら追加でやる

💡 まず DROP SCHEMA で完全リセット → それから pg_restore 実行

💡 データが正常に復元されているか SELECT で確認

psql -U postgres -d local_db -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"
pg_restore -U postgres -d local_db --schema=public --no-owner --no-privileges backup.dump
psql -U postgres -d local_db -c "SELECT * FROM questions LIMIT 5;"

👉 ついに成功！！！ 🎉🎉🎉

Supabaseは無料で使えてオススメなので、同じトラブルにあった方、ぜひ参考にしてみてください