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.
All pages
- Launch the Supabase template in the project you move into, and apply it.
- Install
pg_dump,pg_restoreandpsqlfrom PostgreSQL 17 or newer, the CLI, and rclone 1.70 or newer. - Open a tunnel to the template's database, and leave it open:
ghaym cloud connect supabase-db --env production- In a second terminal, keep the source's URL, as its
postgresuser, and the URL the tunnel prints, asghaym. Run every step below in this terminal:
export SRC='postgres://postgres:<password>@<source host>:5432/postgres'
export DST='<the URL the tunnel prints>'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:
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"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:
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)))"Restore the rows, then the constraints, foreign keys and policies, from the same list with the same rewrite:
pg_restore -L move.list --section=data --section=post-data -f - move.dump | rewrite | psql -q -v ON_ERROR_STOP=1 "$DST"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:
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.sqlStep 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:
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.sqlCopy 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:
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.
Set the source's JWT_SECRET, ANON_KEY and SERVICE_ROLE_KEY on supabase, so the keys your apps already hold keep working:
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 --applyEdge 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.