Move from self-hosted Supabase

Move a self-hosted Supabase project's database, files and keys into a Supabase template on Ghaym, running every step from your own machine.

Before you start

  • Launch the Supabase template in the project you move into, and apply it.
  • Install pg_dump, pg_restore and psql from PostgreSQL 17 or newer, the CLI, and rclone 1.70 or newer.
  • Open a tunnel to the template's database, and leave it open:
Terminal
  ghaym cloud connect supabase-db --env production
  • In a second terminal, keep the source's URL, as its postgres user, and the URL the tunnel prints, as ghaym. Run every step below in this terminal:
Terminal
  export SRC='postgres://postgres:<password>@<source host>:5432/postgres'
  export DST='<the URL the tunnel prints>'

1. Copy the schema

Dump the source, leave out the schemas Supabase's own services create, and restore the rest before its rows, with every postgres owner and grant rewritten to ghaym:

Terminal
M='auth|storage|realtime|_realtime|extensions|graphql|graphql_public|vault|pgbouncer|cron|net|supabase_functions|pgsodium|pgsodium_masks|pgtle|topology'
pg_dump -Fc -Z0 "$SRC" > move.dump
pg_restore -l move.dump | grep -v -E "^[0-9]+; [0-9]+ [0-9]+ [A-Z ]+ ($M) |EVENT TRIGGER|^[0-9]+; [0-9]+ [0-9]+ SCHEMA - ($M) |COMMENT - EXTENSION|PUBLICATION - supabase_realtime |ACL pg_catalog |ACL - SCHEMA ($M) |DEFAULT ACL .* supabase_admin$" > move.list
rewrite() { sed -E 's/(OWNER TO|TO|FROM|FOR ROLE) postgres([;, ])/\1 ghaym\2/g'; }
pg_restore -L move.list --section=pre-data -f - move.dump | rewrite | psql -q -v ON_ERROR_STOP=1 "$DST"

2. Copy the users and the file records

Copy the sign-in and storage rows table by table, users first, with only the source's columns that the target doesn't generate. Then fill the token columns GoTrue reads as text, and move the refresh token counter past both sides' rows:

Terminal
T='auth.users auth.identities auth.sessions auth.refresh_tokens auth.mfa_factors auth.mfa_challenges auth.mfa_amr_claims auth.one_time_tokens auth.audit_log_entries storage.buckets storage.objects storage.s3_multipart_uploads storage.s3_multipart_uploads_parts'
for t in $T; do
  s=${t%.*}; n=${t#*.}
  sc=$(psql -tA "$SRC" -c "select string_agg(column_name, ',') from information_schema.columns where table_schema='$s' and table_name='$n'")
  [ -z "$sc" ] && continue
  cols=$(psql -tA "$DST" -c "select string_agg(quote_ident(column_name), ',' order by ordinal_position) from information_schema.columns where table_schema='$s' and table_name='$n' and is_generated='NEVER' and column_name = any(string_to_array('$sc', ','))")
  [ -z "$cols" ] && continue
  psql "$SRC" -c "\copy (select $cols from $t) to stdout" | psql "$DST" -c "\copy $t($cols) from stdin"
done
psql "$DST" -c "update auth.users set confirmation_token = coalesce(confirmation_token, ''), recovery_token = coalesce(recovery_token, ''), email_change_token_new = coalesce(email_change_token_new, ''), email_change = coalesce(email_change, ''), email_change_token_current = coalesce(email_change_token_current, ''), phone_change = coalesce(phone_change, ''), phone_change_token = coalesce(phone_change_token, ''), reauthentication_token = coalesce(reauthentication_token, '')"
sv=$(psql -tA "$SRC" -c "select last_value from auth.refresh_tokens_id_seq")
psql "$DST" -c "select setval('auth.refresh_tokens_id_seq', greatest($sv, (select coalesce(max(id), 1) from auth.refresh_tokens)))"

3. Copy your rows

Restore the rows, then the constraints, foreign keys and policies, from the same list with the same rewrite:

Terminal
pg_restore -L move.list --section=data --section=post-data -f - move.dump | rewrite | psql -q -v ON_ERROR_STOP=1 "$DST"

4. Copy your triggers on auth and storage

The dump lists a trigger such as on_auth_user_created under its table's schema, which step 1 left out. Find your own triggers on auth and storage tables by the schema their function lives in, and create them again:

Terminal
psql -tA "$SRC" -c "select pg_get_triggerdef(t.oid) || ';' from pg_trigger t join pg_class c on c.oid = t.tgrelid join pg_proc p on p.oid = t.tgfoid where not t.tgisinternal and c.relnamespace::regnamespace::text in ('auth', 'storage') and p.pronamespace::regnamespace::text !~ '^($M)\$'" > triggers.sql
psql -v ON_ERROR_STOP=1 "$DST" -f triggers.sql

5. Copy your policies on auth and storage

Step 1 also left out the policies on auth and storage tables, such as one on storage.objects that lets a user read their own files. Read the source's policies from pg_policies, and create again each one the template doesn't already have:

Terminal
psql -tA "$SRC" -c "select format('do \$p\$ begin if not exists (select from pg_policies where schemaname = %L and tablename = %L and policyname = %L) then create policy %I on %I.%I as %s for %s to %s%s%s; end if; end \$p\$;', schemaname, tablename, policyname, policyname, schemaname, tablename, permissive, cmd, array_to_string(roles, ', '), ' using (' || qual || ')', ' with check (' || with_check || ')') from pg_policies where schemaname in ('auth', 'storage')" > policies.sql
psql -v ON_ERROR_STOP=1 "$DST" -f policies.sql

Copy the files

Copy the files from the source's storage folder into the template's bucket with rclone, using the bucket's keys from ghaym cloud buckets credentials get <bucket> --show:

Terminal
export RCLONE_CONFIG_GHAYM_TYPE=s3
export RCLONE_CONFIG_GHAYM_PROVIDER=Other
export RCLONE_CONFIG_GHAYM_ENDPOINT=https://s3.jo.cloud.ghaym.me
export RCLONE_CONFIG_GHAYM_REGION=jo
export RCLONE_CONFIG_GHAYM_FORCE_PATH_STYLE=false
export RCLONE_CONFIG_GHAYM_SIGN_ACCEPT_ENCODING=false
export RCLONE_CONFIG_GHAYM_ACCESS_KEY_ID='<access key ID>'
export RCLONE_CONFIG_GHAYM_SECRET_ACCESS_KEY='<secret access key>'
rclone copy ./volumes/storage/stub ghaym:<bucket>

stub is the source's GLOBAL_S3_BUCKET, the folder its storage writes files under; put yours in its place if you changed it.

For a bucket outside Jordan, put its own code in place of jo, as the bucket's Signing region shows on its page.

Keep your keys

Set the source's JWT_SECRET, ANON_KEY and SERVICE_ROLE_KEY on supabase, so the keys your apps already hold keep working:

Terminal
ghaym cloud vars set JWT_SECRET '<the source JWT_SECRET>' --project <project> --env production --service supabase --secret
ghaym cloud vars set ANON_KEY '<the source ANON_KEY>' --project <project> --env production --service supabase
ghaym cloud vars set SERVICE_ROLE_KEY '<the source SERVICE_ROLE_KEY>' --project <project> --env production --service supabase --secret --apply

What doesn't move

Edge functions don't move, since Ghaym doesn't run them.

An object that needs a superuser, such as a C-language function or an extension the template lacks, stops the restore. Take its line out of move.list and run the step again.

A service image newer than the template's, Realtime among them, may fail its migrations.

Vault secrets don't move. Create them again in Studio after the restore.