Migrating Heroku PostGres to Supabase

I recently wanted to try out Supabase on a large-ish production app that's been in the wild and accumulating data for some time.

The original project is hosted on Heroku, using Heroku Data Postgres as it's data store.

It took me a while to get the dump and restore command correct, so I'm logging them here to save me headaches in the future 😅.

PG Dump

Here is the command I used to do a clean dump of the existing Heroku database:

pg_dump -Fc --clean --if-exists --quote-all-identifiers --dbname=DB_NAME --host=DB_HOST --port=5432 --username=DB_USERNAME --password --no-owner --no-privileges > heroku_dump.dump
Copy

These options helped with speed (-Fc), and the others helped with a clean import. The --no-owner and --no-privileges options also seemed to be neccessary for Supabase.

PG Restore

The Supabase docs recommend using psql on a .sql import file. This did not work for me.

pg_restore, however, did.

Here is the command I used:

pg_restore -U postgres --no-owner --role=postgres -d 'postgres://postgres:[SUPABASE_PASSWORD]@db.[SUPABASE_REF].supabase.co:5432/postgres' heroku_dump.dump
Copy

Again, the --no-owner option here was critical, as I encountered a bunch of errors due to Heroku using a random string as the owner, and Supabase using the postgres role to create and alter tables, constraints, etc.

Hope this helps someone, and happy coding! 😎