Supabase Migration Playbook¶

Step-by-step guide for migrating a Docker container's database from pigsty/self-hosted Postgres to Supabase. Based on real migration of njav app (13K+ rows, multiple tables).

Prerequisites¶

• Supabase running on same Docker host (unraid)
• Target schema created: CREATE SCHEMA IF NOT EXISTS {app_name};
• Komodo Variable SUPABASE_POSTGRES_PASSWORD accessible
• Container currently uses pigsty (reverse-proxy network)

Quick reference¶

# Supabase pooler (via Docker internal network — recommended)
host=supabase-pooler port=5432 user=postgres.homelab password=${SUPABASE_POSTGRES_PASSWORD} dbname=postgres

# NEVER use options= in connection string — PgBouncer strips it
# ALWAYS set search_path in code instead

Migration steps¶

1. Network check¶

Verify container can reach Supabase via Docker internal network (bypasses VPN/Tailscale):

# Check Supabase container networks
docker inspect supabase-pooler --format "{{range \$k, \$v := .NetworkSettings.Networks}}{{\$k}} {{end}}"
# Expected: supabase-internal

# Connect your container
docker network connect supabase-internal {container_name}

# Test connectivity
docker exec {container_name} python3 -c "
import psycopg
con = psycopg.connect('host=supabase-pooler port=5432 user=postgres.homelab password=YOUR_PASS dbname=postgres', connect_timeout=5)
print('OK:', con.execute('SELECT version()').fetchone()[0][:30])
con.close()
"

2. pg_dump from source¶

docker exec pigsty-postgres pg_dump -U postgres -d {db_name} \
  --no-owner --no-privileges --schema=public > /tmp/{app}_dump.sql

3. Transform for Supabase schema¶

# Transform: replace public. references with {schema_name}.
content = open("/tmp/{app}_dump.sql").read()
content = "SET search_path TO {schema_name};\n" + content
content = content.replace("public.", "{schema_name}.")
content = content.replace("CREATE TABLE ", "CREATE TABLE IF NOT EXISTS ")
content = content.replace("CREATE INDEX ", "CREATE INDEX IF NOT EXISTS ")
open("/tmp/{app}_dump_njav.sql", "w").write(content)

4. Restore to Supabase¶

docker exec -i supabase-db psql -U postgres -d postgres < /tmp/{app}_dump_transformed.sql

5. Verify data¶

docker exec {container} python3 -c "
import psycopg
con = psycopg.connect('host=supabase-pooler port=5432 user=postgres.homelab password=YOUR_PASS dbname=postgres')
con.execute('SET search_path TO {schema_name}')
print('Tables:', con.execute(\"SELECT count(*) FROM pg_tables WHERE schemaname='{schema_name}'\").fetchone()[0])
print('Rows:', con.execute('SELECT count(*) FROM {main_table}').fetchone()[0])
con.close()
"

6. Update application code¶

database.py — add search_path to session:

@contextmanager
def get_session():
    session = get_session_factory()()
    try:
        session.execute(text("SET statement_timeout = 10000"))
        session.execute(text("SET search_path TO {schema_name}"))  # ADD THIS
        yield session

db.py — add search_path to raw connection:

def connect(row_factory=None):
    con = psycopg.connect(os.environ["DATABASE_URL"], **kw)
    con.execute("SET statement_timeout = 10000")
    con.execute("SET search_path TO {schema_name}")  # ADD THIS
    con.commit()
    return con

7. Update DATABASE_URL¶

# Old (pigsty)
export PG_DSN="host=pigsty-postgres port=5432 dbname={db_name} user=postgres password=OLD_PASS"

# New (Supabase) — NO options= keyword!
export PG_DSN="host=supabase-pooler port=5432 dbname=postgres user=postgres.homelab password=SUPABASE_PASS"

Save to /mnt/user/appdata/{app}/db_supabase.env.

8. Update deploy.sh¶

DB_ENV="/mnt/user/appdata/{app}/db_supabase.env"

# In docker run section, add:
docker network connect supabase-internal {container} 2>/dev/null || true

9. Re-stamp Alembic (if applicable)¶

docker run --rm --network supabase-internal \
  -e DATABASE_URL="host=supabase-pooler port=5432 dbname=postgres user=postgres.homelab password=YOUR_PASS" \
  {image} sh -c "cd /app && alembic stamp head"

10. Deploy + verify¶

./deploy.sh

# Verify endpoints
curl -s http://localhost:{port}/health
curl -s http://localhost:{port}/api/stats | head -c 100

# Check startup logs for errors
docker logs {container} 2>&1 | grep -iE "error|warning" | tail -5

11. Verify indexes¶

-- Connect via Supabase Studio or psql
SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = '{schema_name}';

12. Drop old DB (after 24h confirm)¶

docker exec pigsty-postgres psql -U postgres -c "DROP DATABASE IF EXISTS {db_name};"

Known issues¶

options= in DATABASE_URL doesn't work¶

Problem: options=-c search_path=njav in psycopg keyword-format connection string → invalid connection option "search_path"

Cause: PgBouncer/Supabase pooler strips connection options.

Fix: Use SET search_path TO {schema} after connecting, in connect() and get_session().

pg_trgm extension¶

Problem: CREATE EXTENSION pg_trgm succeeds but gin_trgm_ops operator class doesn't exist.

Cause: Supabase manages extensions differently — must enable via Dashboard → Database → Extensions.

Workaround: Use application-level caching instead of trigram indexes for LIKE queries.

Connection pool limit¶

Problem: Supabase session pool = 20 connections.

Fix: Avoid opening connections in loops (N+1 pattern). Reuse connections. Use batch queries.

Network topology¶

graph LR
    CT[Container]
    CT -->|gluetun__vpn| VPN["internet (VPN)"]
    CT -->|reverse-proxy| PIG["pigsty (old, after drop)"]
    CT -->|supabase-internal| POOL["supabase-pooler:5432 (new)
Docker bridge, ~1ms, no VPN"]

Secrets reference¶

Lessons learned¶

1. Docker internal network > Tailscale > LAN — always check if containers share a Docker network first
2. search_path in code, not connection string — PgBouncer strips options
3. pg_dump transform is simple — just sed public. → schema_name.
4. Verify indexes migrated — they don't always survive transform
5. Keep old DB 24h — before dropping, confirm app works on Supabase
6. N+1 patterns are worse on Supabase — 20 connection limit means batch queries are mandatory