
I recently wanted to try out [Supabase](https://www.supabase.com) 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](https://www.heroku.com), using [Heroku Data Postgres](https://devcenter.heroku.com/articles/heroku-postgresql) 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:

```shell
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
```

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.


> zinc:"TIP" Don't forget to change your `DB_NAME, DB_HOST, etc` variables after you paste.  I find it easiest to paste everthing into a blank VSCode window, fix my variables, and then copy and paste into my terminal.


### 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:

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

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! 😎


I hit a few snags trying to migrate a production Heroku PostGres instance to Supabase.  Here are the commands I used to overcome them.