# Restore a Supabase Database from SQL Backups (Step-by-Step Guide)

So you’ve got your Supabase database backups (`roles.sql`, `schema.sql`, `data.sql`) safely stored in GitHub — nice 👏  
Now comes the slightly nerve-wracking part: **restoring everything into a fresh Supabase project without breaking things**.

This guide walks through a **clean, repeatable, and production-safe way** to restore a Supabase database using `psql`, with a few extra tips I wish someone had told me earlier.

---

## Step 1: Create a New Supabase Project

Head over to the **Supabase Dashboard** and create a brand-new project.

**Important tips here:**

* ✅ **Use the same region** as the original project (helps with latency + future migrations)
    
* ⏱️ Wait until the database status shows *healthy*
    

### Get the Direct Database Connection String

1. Go to **Settings → Database**
    
2. Copy the **Direct connection string** (port **5432**)
    
3. If the password was recently changed:
    
    * Reset it once more
        
    * Wait ~1 minute so it propagates everywhere
        

📌 You’ll use this connection string later as `[NEW_DB_URL]`.

---

## Step 2: Install Required Tools Locally

You’ll need two things:

* PostgreSQL client (`psql`)
    
* Supabase CLI (optional, but useful)
    

### Install PostgreSQL Client

**Windows**

```plaintext
winget install postgresql
```

Or download directly from [postgresql.org](http://postgresql.org).

**macOS**

```plaintext
brew install postgresql supabase/tap/supabase
```

**Linux (Debian/Ubuntu)**

```plaintext
sudo apt install postgresql-client supabase
```

### Verify Installation

```plaintext
psql --version
```

If this works, you’re good to go 🚀

---

## Step 3: Download Backup Files from GitHub

From your repository, download these files into **one local folder**:

```plaintext
roles.sql
schema.sql
data.sql
```

⚠️ **Do not rename them**  
⚠️ **Order matters** — this exact sequence is critical for a successful restore.

---

## Step 4: Restore the Database (The Safe Way)

Now comes the main event.

Run this single command from the folder containing your SQL files:

```plaintext
psql \
  --single-transaction \
  --variable ON_ERROR_STOP=1 \
  --file roles.sql \
  --file schema.sql \
  --set session_replication_role=replica \
  --file data.sql \
  --dbname "[NEW_DB_URL]"
```

### Why this works so well

* `--single-transaction`  
    → Either everything restores, or nothing does (no half-broken DBs)
    
* `ON_ERROR_STOP=1`  
    → Stops immediately if something goes wrong
    
* `session_replication_role=replica`  
    → Temporarily disables triggers & foreign-key checks  
    (super important for bulk `COPY` inserts)
    

---

## Step 5: Understand the Restore Order (Don’t Skip This)

Here’s **why** the order matters:

1. `roles.sql`
    
    * Database users
        
    * Permissions & grants  
        Without this → schema permissions can fail
        
2. `schema.sql`
    
    * Tables, views, functions, indexes
        
    * Constraints & relationships
        
3. `data.sql`
    
    * Actual records
        
    * Usually uses `COPY` for speed
        

Mess this order up and you’ll meet cryptic Postgres errors 😅

---

## Step 6: Verify the Restoration

### Quick sanity check via terminal

```plaintext
psql "[NEW_DB_URL]" -c "SELECT COUNT(*) FROM your_table;"
```

### Visual check in Supabase

* Open **Table Editor**
    
* Browse a few tables
    
* Confirm row counts look right
    

If both checks pass — congrats 🎉 your database is officially back.

---

## Step 7: Things That Are *Not* Restored Automatically

SQL backups **do not include**:

* 🔐 Auth providers (Google, GitHub, email, etc.)
    
* 🗄️ Storage buckets & policies
    
* 🔑 API keys & secrets
    
* ⚙️ Edge Functions config
    

You’ll need to reconfigure these manually in the Supabase dashboard.

👉 Pro tip: keep a **post-restore checklist** in your repo for this stuff.

---

## Optional Improvements (Highly Recommended)

* ✅ Test restore on a **staging project** first
    
* 🧪 Run a few app queries against the new DB
    
* 📄 Log restore output to a file for debugging:
    
    ```plaintext
    psql ... > restore.log 2>&1
    ```
    
* 🔁 Automate restore later via GitHub Actions (carefully!)