# Migrate from supabase.com to Self-Hosted (Datafy)

How to move your **hosted Supabase project** (supabase.com) to the **self-hosted** stack on AWS (RDS + RDS Proxy + EC2 Docker Compose).

> **Prerequisites:** Target environment is set up per `docs/supabase-aws-rds-proxy-setup.md`. For shell access to the private EC2 instance, see `docs/connect-private-ec2-ssm.md`.

## Overview

| Component | What moves | How |
|-----------|------------|-----|
| Database (tables, `auth.users`, RLS, functions) | Postgres | `supabase db dump` or `pg_dump` → restore via RDS Proxy |
| Storage **metadata** (`storage.objects` rows) | Postgres | Included in DB dump |
| Storage **files** (actual uploads) | Object storage | Separate copy to `datafy-prod-supabase-storage` |
| Edge Functions | Supabase project | Redeploy to self-hosted separately |
| API keys / JWT | Project config | New keys in `.env` — users must re-login |

**Critical:** A database dump does **not** copy uploaded files. If you skip Storage migration, downloads return 404 even when `storage.objects` rows exist.

---

## Production values (Datafy target)

| Setting | Value |
|---------|-------|
| RDS Proxy host | `datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com` |
| Port | `5432` |
| Database | `postgres` |
| Secrets Manager secret | `rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c` |
| Region | `eu-west-1` |
| S3 bucket (target) | `datafy-prod-supabase-storage` |
| Public API URL (after cutover) | `https://supabase.datafy.co.za` |
| EC2 instance | `i-03d70f4349cdb8b93` (private — use SSM) |

---

## Before you start

### Target database ready

Confirm on RDS (via `~/connect-db.sh` or `psql` through the proxy):

- Extensions: `uuid-ossp`, `pgcrypto`, `pg_stat_statements` in `extensions` schema
- `jwt` schema installed (`2-pgjwt.sql`)
- Supabase roles exist (or will be created from `roles.sql` dump)

### Take a snapshot (rollback safety)

```bash
aws rds create-db-snapshot \
  --db-instance-identifier datafy-prod-postgres \
  --db-snapshot-identifier datafy-pre-migration-$(date +%Y%m%d) \
  --region eu-west-1
```

Wait until snapshot status is **available** before restoring.

### Plan a maintenance window

- Stop writes to the **source** hosted project during the final dump (or accept some drift).
- Existing user **sessions** will break after cutover (new `JWT_SECRET` / API keys).

---

## Step 1 — Get connection strings

### Source (hosted supabase.com)

Dashboard → **Project Settings → Database → Connection string** → **URI** (direct connection, not pooler):

```
postgresql://postgres.[REF]:[PASSWORD]@aws-0-[region].pooler.supabase.com:5432/postgres
```

For dumps, prefer the **direct** hostname (`db.[REF].supabase.co:5432`) if shown in the dashboard — pooler can interfere with `pg_dump`.

```bash
export OLD_DB_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"
```

### Target (self-hosted via RDS Proxy)

From the Supabase EC2 instance (SSM shell). **Copy and run this whole block** — it sets connection variables, tests access, and creates the `auth` / `storage` schemas Supabase containers need on PostgreSQL 15+.

> Use single quotes around the secret id (`'rds!db-...'`). Bash treats `!` specially in double quotes (`event not found`).
>
> For **admin / migration** work, connect as master `postgres` through the **RDS Proxy** (only role registered on the proxy).
> Supabase **Docker containers** use their own roles and a fixed URL-safe `POSTGRES_PASSWORD` — see `docs/supabase-aws-rds-proxy-setup.md`.

> Use libpq `PG*` environment variables and call `psql` with **no connection-string argument**.
> Do **not** build a `PSQL="psql host=... port=..."` variable — when expanded unquoted it splits on
> spaces, dropping `sslmode` (→ "requires TLS connections") and mangling user/db (→ "no credentials for the role").

```bash
# 1) Connection variables (master postgres — for psql / restore)
export PGHOST=datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com
export PGPORT=5432
export PGDATABASE=postgres
export PGUSER=postgres
export PGSSLMODE=require   # RDS Proxy requires TLS
export PGPASSWORD=$(aws secretsmanager get-secret-value \
  --secret-id 'rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c' \
  --region eu-west-1 \
  --query SecretString --output text | jq -r '.password')

# 2) Test connectivity (plain psql — reads PG* env vars)
psql -c "SELECT 1 AS connected;"

# 3) URL form for tools that require it (password is URL-encoded — required when it contains : ] ? ! etc.)
export NEW_DB_URL="postgresql://${PGUSER}:$(python3 -c "import urllib.parse, os; print(urllib.parse.quote(os.environ['PGPASSWORD'], safe=''))")@${PGHOST}:${PGPORT}/${PGDATABASE}?sslmode=require"

# 4) Create auth + storage schemas (run before starting auth/storage containers, or if you see
#    "permission denied for schema public" in docker compose logs auth)
psql <<'EOSQL'
GRANT supabase_auth_admin, supabase_storage_admin TO CURRENT_USER;
CREATE SCHEMA IF NOT EXISTS auth    AUTHORIZATION supabase_auth_admin;
CREATE SCHEMA IF NOT EXISTS storage AUTHORIZATION supabase_storage_admin;
GRANT USAGE, CREATE ON SCHEMA auth    TO supabase_auth_admin;
GRANT USAGE, CREATE ON SCHEMA storage TO supabase_storage_admin;
EOSQL

# 5) Optional — set fixed URL-safe password on Supabase roles (containers use POSTGRES_PASSWORD in .env)
#    Replace with your saved value, or generate a new one:
# export NEW_SUPA_PW=$(openssl rand -hex 24)
export NEW_SUPA_PW='PASTE_YOUR_SAVED_HEX_PASSWORD_HERE'
echo "length=${#NEW_SUPA_PW}"   # must be 48 for openssl rand -hex 24

psql <<EOSQL
ALTER ROLE authenticator              WITH PASSWORD '${NEW_SUPA_PW}';
ALTER ROLE supabase_auth_admin        WITH PASSWORD '${NEW_SUPA_PW}';
ALTER ROLE supabase_storage_admin     WITH PASSWORD '${NEW_SUPA_PW}';
ALTER ROLE supabase_admin             WITH PASSWORD '${NEW_SUPA_PW}';
ALTER ROLE supabase_read_only_user    WITH PASSWORD '${NEW_SUPA_PW}';
ALTER ROLE supabase_replication_admin WITH PASSWORD '${NEW_SUPA_PW}';
ALTER ROLE pgbouncer                  WITH PASSWORD '${NEW_SUPA_PW}';
EOSQL
```

After step 5, ensure `POSTGRES_PASSWORD` in `~/supabase-project/.env` (or `/var/snap/amazon-ssm-agent/13009/supabase-project/.env`) matches `NEW_SUPA_PW`, then restart:

```bash
cd ~/supabase-project   # or your actual supabase-project path
grep '^POSTGRES_PASSWORD=' .env
sudo docker compose up -d
sudo docker compose logs -f auth
```

---

## Step 2 — Install tools on EC2

Run via SSM interactive session (`sudo su - ubuntu` if needed):

```bash
# PostgreSQL client (if not installed)
sudo apt update && sudo apt install -y postgresql-client jq

# Supabase CLI (recommended for dumps)
curl -fsSL https://github.com/supabase/cli/releases/latest/download/supabase_linux_amd64.tar.gz -o supabase.tar.gz
tar -xzf supabase.tar.gz
sudo mv supabase /usr/local/bin/
supabase --version
```

Create a working directory:

```bash
mkdir -p ~/migration && cd ~/migration
```

---

## Step 3 — Dump from hosted Supabase

### Option A — Supabase CLI (recommended)

```bash
cd ~/migration

supabase db dump --db-url "$OLD_DB_URL" -f roles.sql --role-only
supabase db dump --db-url "$OLD_DB_URL" -f schema.sql
supabase db dump --db-url "$OLD_DB_URL" -f data.sql --use-copy --data-only
```

### Option B — Plain pg_dump

```bash
pg_dump "$OLD_DB_URL" \
  --clean --if-exists --quote-all-identifiers \
  --schema public --schema auth --schema storage --schema extensions \
  --no-owner --no-privileges \
  -f supabase_full_dump.sql
```

---

## Step 4 — Prepare dumps for RDS

RDS does not support all hosted Supabase role attributes. Before restore, edit `roles.sql` (if using Option A):

- Remove or comment lines with `SUPERUSER`, `REPLICATION` (use `rds_superuser` / `rds_replication` only where you create roles manually)
- Comment out roles that already exist and match your target (`authenticator`, `anon`, etc.) if restore errors on `already exists`

If the target already has Supabase schema from a partial setup, you may need a **fresh** RDS database or drop conflicting objects first — test on a snapshot clone if unsure.

---

## Step 5 — Restore to self-hosted RDS

### Option A — Three-file restore (CLI dumps)

```bash
cd ~/migration

psql "$NEW_DB_URL" \
  --single-transaction \
  --variable ON_ERROR_STOP=1 \
  --file roles.sql \
  --file schema.sql \
  --command 'SET session_replication_role = replica' \
  --file data.sql
```

`session_replication_role = replica` disables triggers and FK checks during data load.

### Option B — Single-file restore (pg_dump)

```bash
psql "$NEW_DB_URL" \
  --single-transaction \
  --variable ON_ERROR_STOP=1 \
  -f supabase_full_dump.sql
```

### If restore fails partway

- Read the first error — often `already exists` on roles/extensions
- Fix `roles.sql` or drop the conflicting object on target
- Restore from RDS snapshot if needed, fix dumps, retry

---

## Step 6 — Migrate Storage files

Database rows in `storage.objects` point at file paths. Copy the actual objects to your bucket.

### Option A — Storage API (works without source S3 credentials)

From a machine with network access to both projects:

1. List buckets/objects from **source** using the hosted `service_role` key.
2. Download each object.
3. Upload to **self-hosted** Storage API at `https://supabase.datafy.co.za/storage/v1/` using your new `service_role` key.

Preserve bucket names and paths so `storage.objects` rows stay valid.

### Option B — S3 sync (if you have source bucket access)

Only if Supabase provided S3 credentials or you know the backing bucket:

```bash
aws s3 sync \
  s3://<source-supabase-storage-bucket> \
  s3://datafy-prod-supabase-storage \
  --source-region <source-region> \
  --region eu-west-1
```

### Verify Storage

```bash
# Count metadata rows
psql "$NEW_DB_URL" -c "SELECT bucket_id, count(*) FROM storage.objects GROUP BY 1;"

# Test download via API
curl -I "https://supabase.datafy.co.za/storage/v1/object/public/<bucket>/<path>" \
  -H "apikey: <SERVICE_ROLE_KEY>"
```

### Remap S3 keys for `STORAGE_TENANT_ID=stub` (required)

If Studio **lists** objects but public URLs return **404/400**, rclone left files at `<bucket>/<path>` while Storage **1.60+** reads `stub/<bucket>/<name>/<version>` (not `…-$v-…`).

Do **not** rewrite `storage.objects`. After rclone (or S3 sync), run:

```bash
# Script: docs/scripts/remap_storage_s3_keys.sh — create on EC2 then:
~/migration/remap_storage_s3_keys.sh
nohup ~/migration/remap_storage_s3_keys.sh --execute --jobs 32 \
  > ~/migration/remap-execute.out 2>&1 &
```

Details: `docs/migrate-supabase-delta-sync.md` §3.2b. Re-run after every migrate/delta rclone.

---

## Step 7 — Restart Supabase and verify

```bash
cd ~/supabase-project
docker compose restart
docker compose ps
```

### Database checks

```bash
psql "$NEW_DB_URL" -c "SELECT count(*) AS users FROM auth.users;"
psql "$NEW_DB_URL" -c "\dt public.*"
psql "$NEW_DB_URL" -c "SELECT count(*) FROM storage.buckets;"
```

### API checks

```bash
curl -s https://supabase.datafy.co.za/rest/v1/ \
  -H "apikey: <ANON_KEY>"

curl -s https://supabase.datafy.co.za/auth/v1/settings \
  -H "apikey: <ANON_KEY>"
```

Open Studio: `https://supabase.datafy.co.za/` — confirm tables, auth users, and a sample file upload/download.

---

## Step 8 — Cutover application config

Update anything that pointed at the old hosted project:

### This repo (Lambda SSM parameters)

```bash
aws ssm put-parameter --name /supabase/url --value "https://supabase.datafy.co.za" --type String --overwrite --region eu-west-1
aws ssm put-parameter --name /supabase/anon --value "<NEW_ANON_KEY>" --type SecureString --overwrite --region eu-west-1
aws ssm put-parameter --name /supabase/service_role --value "<NEW_SERVICE_ROLE_KEY>" --type SecureString --overwrite --region eu-west-1
```

### Frontend / mobile apps

- `SUPABASE_URL` → `https://supabase.datafy.co.za`
- `SUPABASE_ANON_KEY` → new anon key from `~/supabase-project/.env`

### Auth redirect URLs

In `~/supabase-project/.env`, ensure:

```env
SITE_URL=https://supabase.datafy.co.za
API_EXTERNAL_URL=https://supabase.datafy.co.za
ADDITIONAL_REDIRECT_URLS=https://your-app.datafy.co.za
```

Restart: `docker compose up -d`

---

## What does not migrate automatically

| Item | Action |
|------|--------|
| Edge Functions | Redeploy from your repo to self-hosted |
| Realtime channel names / config | Usually unchanged if schema migrated |
| Hosted dashboard settings | Reconfigure in Studio / `.env` |
| User sessions (JWT) | Users sign in again |
| Supabase branching / previews | N/A on self-hosted |
| Logs / metrics history | Not in Postgres dump |

---

## Troubleshooting

| Issue | Fix |
|-------|-----|
| `pg_dump: connection refused` on source | Use direct DB host, not pooler; check IP allowlist on hosted project |
| `role "..." already exists` | Edit `roles.sql` or skip role restore if target roles are correct |
| `extension "..." does not exist` on target | Run `1-extensions.sql` / `2-pgjwt.sql` first (see main setup guide) |
| `permission denied for schema auth` | Restore as `postgres` user through proxy |
| `permission denied for schema public` in `auth` logs | Run step 4 in **Target** block above (`CREATE SCHEMA auth ...`) |
| `RDS proxy has no credentials for the role supabase_auth_admin` | Point `POSTGRES_HOST` in `.env` to the **RDS instance endpoint** (not proxy), or register each Supabase role on the proxy |
| `invalid port` in `auth` logs | `POSTGRES_PASSWORD` contains URL-unsafe characters — use fixed hex password (step 5 above) |
| `psql: extra command-line argument ... ignored` / `requires TLS connections` | Don't build a `PSQL="psql host=..."` variable; export `PG*` vars (incl. `PGSSLMODE=require`) and call plain `psql` |
| Storage 404 after migration | Files not copied — complete Step 6; if listed in Studio but 404, run `remap_storage_s3_keys.sh` (tenant `stub` + version keys) |
| `rest` or `auth` unhealthy after restore | Check `POSTGRES_PASSWORD` matches all role passwords; `docker compose logs auth` |
| Restore very slow | Normal for large DB; run from EC2 in same VPC as RDS |

---

## Related

- `docs/migrate-supabase-delta-sync.md` — sync **only new/changed** data and images after the initial migration (go-live delta)
- `docs/supabase-aws-rds-proxy-setup.md` — full self-hosted setup
- `docs/connect-private-ec2-ssm.md` — access private EC2 for migration commands
- [Supabase: migrating within Supabase](https://supabase.com/docs/guides/platform/migrating-within-supabase)
- [Supabase: restore a downloaded backup](https://supabase.com/docs/guides/platform/migrating-within-supabase/backup-restore)