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

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

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

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

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

[まとめだけ見たい方はこちら](#最終的にうまくいった方法)

<br>
  
### 📌 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
```
<br>

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



### ① 外部キー制約の警告
:::note alert
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の認証エラー
:::note alert
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 のエラー
:::note alert
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 スキーマのエラー
:::note alert
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実行後エラーは無かったが、中身を確認すると・・・
:::note alert
符号化方式"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;  # 変更が反映されたか確認

```
<br>

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

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

その結果、

キターーーーー！！！

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

<br>

### 最終的にうまくいった方法
### **🔥 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
    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;"

```
<br>

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

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