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

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

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

💡 追加のポイント

リストア後にデータを確認
```
    
psql -U postgres -d local_db
```
```
sql

SELECT * FROM questions LIMIT 5;
```
→ データが正しく復元されているか確認！

もし 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は無料で使えてオススメなので、同じトラブルにあった方、ぜひ参考にしてみてください