Migrating from Heroku to a self-hosted Supabase instance using Coolify on Hetzner can save you significant costs and offer greater control. However, the migration process can involve multiple challenges.

This guide covers the exact steps, pitfalls, and solutions from my own experience to help you smoothly navigate the migration.

1. Prerequisites

• Local machine (this guide uses a Mac)

• A running Supabase instance hosted via Coolify on a Hetzner server (minimum 4GB RAM recommended) - watch this YouTube guide to get to set up straight away with that.

• Heroku CLI installed locally.

Recommendation

Before starting get both these things:

• Server IP address e.g. 71.345.12.134

• Supabase db container string from coolify e.g. supabase-db-abc123example456

Then open up a ChatGPT or another LLM chat window, feed it the URL of this article, plus those two variables, and it'll guide you through step by step.

Important: be careful security wise, not recommended to do this unless you're sure your conversations are encrypted from the model provider. The author of this post holds no responsibility for any data leaks.

2. Get Your Database Dump from Heroku

Run the following commands in your local terminal to capture and download your database backup from Heroku:

heroku pg:backups:capture
heroku pg:backups:download

Move the downloaded latest.dump file to a known location such as your ~/Downloads/ directory for convenience.

3. Upload the Dump File to Your Server

From your local terminal, upload the file to your Hetzner server using SCP:

scp ~/Downloads/latest.dump [email protected]:/root/latest.dump

Confirm the file is uploaded successfully:

ssh [email protected]
ls -lh /root/latest.dump

4. Identify the Supabase Container

In Coolify, navigate to Resources → Supabase → Postgres to locate your container’s identifier, which will look similar to:

supabase-db-abc123example456

Replace this placeholder in the following commands with your actual container ID.

5. Copy the Dump File into the Supabase Container

Now go to Coolify, click on Terminal in the left sidebar and connect to the first option in the list, the main server.

Execute the following command in the to transfer the dump into the Supabase container:

docker cp /root/latest.dump supabase-db-abc123example456:/tmp/latest.dump

6. Check Contents of Your Dump File (Optional)

Verify whether your essential tables are present in the dump file (example shown for a table named “events”):

docker exec supabase-db-abc123example456 \
  pg_restore -l /tmp/latest.dump | grep -i 'name_of_a_db_table'

7. Prepare the Supabase Container for Restore

Still in the same Terminal, create required Heroku extensions within your Supabase database container:

docker exec -it supabase-db-abc123example456 \
  psql -U postgres -d postgres -c "
    CREATE SCHEMA IF NOT EXISTS heroku_ext;
    CREATE EXTENSION IF NOT EXISTS pgcrypto;
    CREATE OR REPLACE FUNCTION heroku_ext.uuid_generate_v4() RETURNS uuid AS \$\$ SELECT gen_random_uuid(); \$\$ LANGUAGE SQL;"

If you skip this step, your migration will fail on any table that references heroku_ext.uuid_generate_v4(). This is a Heroku-specific quirk, and the workaround here allows your Supabase (or Postgres) container to understand Heroku commands in your exported dump file.

8. Perform the Database Restoration

You ready?! This is the step where all the magic happens.

Important: if you already have a database in the Supabase container and you want to do a full overwrite, delete that db first using this to have a clean slate:

docker exec -it supabase-db-abc123example456 \
  psql -U postgres -d postgres \
  -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

Use the following command to import your dump into the Supabase Postgres database:

docker exec -i supabase-db-abc123example456 \
  pg_restore -U postgres \
  --clean --if-exists \
  --no-owner --no-acl \
  -d postgres \
  /tmp/latest.dump

Common Errors:

During restore, you might encounter:

ERROR: function "uuid_generate_v4" already exists with same argument types
ERROR: extension "uuid-ossp" does not exist

To ensure that UUID generation is working make sure to install uuid-ossp extension using this:

docker exec -it supabase-db-abc123example456 \
  psql -U postgres -d postgres \
  -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'

This installs public.uuid_generate_v4().

9. Verify Database Restoration

Check the list of tables to confirm successful import:

docker exec -it supabase-db-abc123example456 \
  psql -U postgres -d postgres -c "\dt"

You should see a list of tables confirming your database schema and data have been restored successfully.

Test to see if your db generates a uuid with this:

docker exec -it supabase-db-abc123example456 \
  psql -U postgres -d postgres \
  -c "SELECT uuid_generate_v4();"

Expected output:

           uuid_generate_v4           
--------------------------------------
 9d99d3de-97fc-42cf-8abf-3f3e46fa2a26
(1 row)

Test an insert to make sure everything is working:

docker exec -it supabase-db-abc123example456 \
  psql -U postgres -d postgres -c "INSERT INTO events (title) VALUES ('Test') RETURNING uuid;"

10. Troubleshooting and Tips

• RAM Allocation: If restoration hangs or is slow, upgrade your server RAM (minimum 4GB recommended).

• Port Availability: Confirm Postgres is running on port 5432:

sudo lsof -i :5432

I had a problem whereby the 5432 port was blocked by another service.

• Container Status: List containers and container health:

docker ps

Coolify often routes PostgreSQL's 5432 port through a proxy container (NGINX/Traefik). Temporarily stopping this container is necessary during the restore but will briefly impact other services on your server.

Review the Docker list as see if the 5432 port is occupied. If so, use these commands:

docker stop <proxy-container-id>
docker rm <proxy-container-id>

Ensure you notify users of scheduled downtime if public-facing services are impacted.

Common Pitfalls to Avoid

• Ensure the dump file isn’t corrupted.

• Verify the schema and functions from Heroku (like uuid-ossp) are explicitly created on your Supabase instance before restoration.

• Carefully manage file paths during the transfer to avoid “file not found” errors.

By following these detailed steps, you can effectively migrate your Heroku database to a robust, self-managed Supabase solution on Coolify hosted by Hetzner, offering you greater control, flexibility, and potential cost savings.

Good luck!