How to migrate a PostgreSQL database between providers

The good news: PostgreSQL is PostgreSQL. Because it's the same open-source engine everywhere, moving between providers is a well-trodden path — not a rewrite. The care goes into three things: capturing everything (not just tables), verifying the copy, and keeping downtime short. This guide walks the whole path with the actual commands. Wherever you're coming from, the shape is the same.

1. Take stock of the source

Two minutes here saves hours later. Note the Postgres major version, the extensions in use, the database size, and any custom roles. A target on the same or a newer major version restores cleanly; a much older target can reject a newer dump.

-- run these against your current database
select version();
select * from pg_extension;                       -- extensions to recreate
select pg_size_pretty(pg_database_size(current_database()));
select rolname from pg_roles where rolcanlogin;   -- app roles

2. Provision the target and get the direct URL

Create the destination database and copy its direct connection string — DIRECT_URL on WattleDB. Migrations need a session connection, not a pooled one: pg_restore sets session state, creates objects and (optionally) opens long transactions that a transaction pooler will break. Always run a migration over the direct endpoint.

If the source uses extensions, make sure the target supports them before you start — most common ones (uuid-ossp, pgcrypto, postgis, pg_trgm) are widely available.

3. Dump the source

Use the custom format (-Fc): it's compressed, and it lets pg_restore parallelise and skip objects if needed. Run it from a machine with a fast, stable connection to the source.

# full logical dump of one database, custom format
pg_dump -Fc --no-owner --no-privileges \
  "postgresql://USER:PASS@SOURCE_HOST:5432/DBNAME?sslmode=require" \
  -f mydb.dump

--no-owner --no-privileges strips ownership and grant statements that reference the source's roles, so the restore doesn't fail on roles that don't exist on the target. You'll re-grant on the target in step 5.

4. Restore to the target

Point pg_restore at the target's direct URL. -j restores in parallel (tune to the target's CPU); --no-owner matches the dump.

# restore into the freshly provisioned target database
pg_restore --no-owner --no-privileges -j 4 \
  -d "postgresql://app:PASS@ID.db.wattledb.com.au:PORT/app?sslmode=require" \
  mydb.dump

Recreate any extensions the source used (if the dump didn't), then any application roles and their grants:

create extension if not exists "uuid-ossp";
create extension if not exists pgcrypto;
-- grant application privileges as your app expects

5. Verify before you trust it

A restore that "finished" is not a restore that's correct. Check the things that silently go wrong: row counts per table, that sequences advanced, and that extensions are present.

-- compare a few table counts on source vs target
select count(*) from your_biggest_table;
-- confirm sequences are past the max id (or inserts will collide)
select last_value from your_table_id_seq;
-- confirm extensions came across
select extname from pg_extension order by 1;

Run your app's test suite against the target if you have one. It's the cheapest insurance you'll buy all week.

6. Cut over with minimal downtime

For most apps, the simplest safe cut-over is a short maintenance window: pause writes, take a final incremental dump of anything that changed since the first dump (or re-dump if the database is small), restore it, verify, and repoint your app. Small databases are down for minutes.

If you can't take a window, PostgreSQL's logical replication can stream changes from source to target so they stay in sync, letting you switch over with near-zero downtime. It's more moving parts — worth it above a certain size, overkill below it. Start with the maintenance-window approach unless you have a hard reason not to.

7. Repoint your app

Swap your app's DATABASE_URL to the target's pooled connection string (not the direct one — the pooled endpoint is what handles production traffic), deploy, and watch your logs. Keep the old database running, read-only, for a few days as a rollback before you decommission it.

Migrating to WattleDB specifically

The steps above are identical for WattleDB — it's standard PostgreSQL, so pg_dump/pg_restore just work over the DIRECT_URL. If you're coming from Supabase, you're already on PostgreSQL and PostgREST, so most projects move by changing a few environment variables and running the dump/restore above. What changes on arrival is the jurisdiction: your data lands on 100% Australian-owned infrastructure in Sydney, backed up cross-state in Melbourne, encrypted at rest — with no US CLOUD Act exposure to carry forward.