Migrating PostgreSQL from Heroku, Supabase or Neon

Before you start

The move has three parts: copy the data with pg_dump and pg_restore, check that it all arrived, and switch your application over. The same commands work for any PostgreSQL host; the sections further down cover what is different on Heroku, Supabase and Neon.

Get the right client tools

pg_dump must be at least the major version of the database it reads, and pg_restore at least the version of the dump. The simplest rule: install the client tools of the version your new database will run, or newer. We offer PostgreSQL 17 (the default) and 18, so a client 17 or 18 reads any source from 9.2 onwards. If your source already runs 18, create the new database on 18 too.

pg_dump --version
pg_restore --version

Size up the source and create the database

Connect to the source and check its size and the extensions it uses:

SELECT pg_size_pretty(pg_database_size(current_database()));
\dx

Then create the database on a plan whose disk fits that size with room to grow. When a database reaches its plan's storage limit it becomes read-only (see Storage), so a restore that fills the disk stops halfway. Plan sizes are on the pricing page. Each of our databases holds one PostgreSQL database named app, so a source with several databases you want to keep apart needs one of ours for each.

Copy the connection string from the database page and add the password, which is shown once when the database is created (you can reset it on the same page). libpq tools also need sslrootcert=system to check our certificate against your system's certificate authorities:

export ELECTRADB_URL='postgresql://u_<id>:<password>@<id>.db.electradb.com:<port>/app?sslmode=verify-full&sslrootcert=system'

If you have set an IP allowlist, add the address of the machine that will run pg_restore.

Dump and restore

Dump in the custom format, leaving out ownership and grants. The role on our side, u_<id>, owns the app database but is not a superuser, so it cannot take on the roles of your old host. With --no-owner and --no-acl, everything you restore belongs to it. SOURCE_URL holds the old host's connection string.

pg_dump -Fc --no-owner --no-acl -d "$SOURCE_URL" -f app.dump
pg_restore --no-owner --no-acl -j 4 -d "$ELECTRADB_URL" app.dump

-j 4 loads four tables and builds their indexes in parallel. For a source of many gigabytes, the directory format dumps in parallel too: pg_dump -Fd -j 4 -f app.dir, then pg_restore the directory the same way. Sequences come with the dump, set to where they were on the source.

When the restore is done, refresh the planner's statistics. A restore does not collect them, and queries can pick poor plans until it has:

vacuumdb --analyze-only -j 4 -d "$ELECTRADB_URL"

Errors you can expect

pg_restore carries on past errors and lists them at the end. An error about COMMENT ON EXTENSION is harmless: only a superuser can comment on an extension, and the extension itself is there. Extensions that PostgreSQL marks as trusted, such as pgcrypto, citext, hstore, pg_trgm, uuid-ossp and btree_gin, are created by the restore as your role. An extension that needs a superuser fails with a permission error, and so does everything that depends on it: write to [email protected] before you go further, rather than working around the missing objects.

Heroku

Heroku Postgres makes custom-format backups, which pg_restore reads directly. Capture a fresh one and download it:

heroku pg:backups:capture -a APP
heroku pg:backups:download -a APP
pg_restore --no-owner --no-acl -j 4 -d "$ELECTRADB_URL" latest.dump

You can also run pg_dump yourself against the URL from heroku config:get DATABASE_URL -a APP. If your extensions were installed in Heroku's heroku_ext schema, create a schema of that name in app before the restore, so objects that refer to it find it.

For the final copy, heroku maintenance:on -a APP stops your web dynos from serving requests. It does not stop worker dynos: scale them to zero as well. Heroku manages DATABASE_URL while the Postgres add-on is attached, so put our connection string in a config var of your own and have your app read that one.

Supabase

Use the direct connection string, or the session pooler if your machine has no IPv6. The transaction pooler does not keep a session for the whole dump, so pg_dump cannot work through it.

A Supabase database holds your schemas and Supabase's own: auth, storage, realtime, graphql, extensions and others belong to its platform and only work with its services. Dump the schemas you created, usually just public:

pg_dump -Fc --no-owner --no-acl -n public -d "$SUPABASE_URL" -f app.dump

Supabase installs extensions in a schema named extensions, and column defaults often name it, as in extensions.uuid_generate_v4(). Create that schema and the extensions you use in it before the restore:

psql "$ELECTRADB_URL" -c 'CREATE SCHEMA extensions' -c 'CREATE EXTENSION "uuid-ossp" SCHEMA extensions'

Neon

Dump from the direct endpoint: the hostname without -pooler. The pooled endpoint runs PgBouncer in transaction mode, which pg_dump cannot use. If the compute is suspended, the first connection wakes it, which takes a few seconds.

Each Neon branch is a separate database with its own data. Dump the branch your application uses in production, and check the database name in its connection string: Neon's default is neondb, while ours is always app. Roles such as neon_superuser are left behind by --no-owner and --no-acl.

Check the result

Read the list of errors at the end of the restore first, then compare the two databases. Exact row counts of your most important tables, run on both sides, catch a table that did not load:

SELECT count(*) FROM orders;
SELECT max(id) FROM orders;

For tables where every value matters, compare a checksum of their contents. Order the rows the same way on both sides:

SELECT md5(string_agg(t::text, '|' ORDER BY id)) FROM customers t;

Then run your application, or its test suite, against the new database before you move real traffic. Missing extensions, functions and permissions show up there faster than in any count.

Cut over

A dump is a copy of one moment, and writes made on the source after it are not in the copy. We do not offer replication into an ElectraDB database (your role cannot create a subscription), so the final copy needs a short write freeze on the source:

1. Do a full rehearsal first, so you know how long the dump and the restore take. Restore with -j, and if that is too slow, dump with -Fd -j as well.
2. Stop every writer: put the app in maintenance mode and stop background workers and scheduled jobs.
3. Dump, restore and analyze, as above, then run your checks. Add --clean --if-exists to pg_restore so it drops what the rehearsal created first.
4. Note the time. Change your application's connection string, and set a connect timeout of 5 to 10 seconds (see Timeouts and failover).
5. Start the app and watch its logs.

Keep the old database for a while, read-only, as a fallback. On the source, ALTER DATABASE yourdb SET default_transaction_read_only = on stops new sessions from writing by mistake.

The time you noted is useful on our side too. Your database is backed up continuously, and you can restore it to any second of the last 7 days into a new database. If something writes bad data in the first hours, you can get back the copy as it was just before the switch.