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

winget install postgresql

Or download directly from postgresql.org.

macOS

brew install postgresql supabase/tap/supabase

Linux (Debian/Ubuntu)

sudo apt install postgresql-client supabase

Verify Installation

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:

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:

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

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:

    psql ... > restore.log 2>&1
  

• 🔁 Automate restore later via GitHub Actions (carefully!)