Migrating PostgreSQL Between Servers

Migrating PostgreSQL without breaking the application

PostgreSQL is stricter than MySQL about versions and encodings, so a failed migration usually shows up not as an empty database but as restore errors partway through. The order below keeps that risk to a minimum.

1. Check the PostgreSQL version on the new server — it must be equal to or newer than the version on the old one.
2. Take a dump on the old server without stopping the application.
3. Restore the dump on the new server into an empty database and compare row counts in key tables.
4. Point a test instance of the application at the new database through environment variables, without touching production.
5. Switch production to the new database only after a successful check, accepting a short downtime window for the final sync.

pg_dump: exporting the database

# Dump one database in the custom format (compressed, parallel restore)
pg_dump -U postgres -Fc -d mydb > mydb.dump

# Dump in SQL format (human-readable)
pg_dump -U postgres -d mydb > mydb.sql

# Schema only (no data)
pg_dump -U postgres --schema-only -d mydb > schema.sql

Logical replication for minimal downtime

If even a few minutes of downtime is unacceptable, use logical replication instead of a one-off dump: it continuously streams changes to the new server, and pg_dump is only needed for the initial load.

-- On the old server: publish changes
CREATE PUBLICATION mydb_pub FOR ALL TABLES;

-- On the new server: subscribe to the publication
CREATE SUBSCRIPTION mydb_sub
  CONNECTION 'host=OLD_SERVER dbname=mydb user=repl_user password=secret'
  PUBLICATION mydb_pub;

pg_restore: importing on the new server

# Create the database on the new server
createdb -U postgres mydb

# Restore from the custom format
pg_restore -U postgres -d mydb -j 4 mydb.dump
# -j 4 — use 4 parallel processes

Fast transfer through a pipe

# No dump file saved to disk
pg_dump -U postgres -Fc mydb | ssh root@NEW_SERVER "pg_restore -U postgres -d mydb"

Comparing dump formats

Checking the new server before switching

Point a test instance of the application directly at the new server's IP, without changing DNS or touching production:

DATABASE_URL=postgresql://user:pass@NEW_SERVER_IP:5432/mydb ./run-app --dry-run

Walk through key application flows — creating a record, searching, an aggregate report — and compare the result to production on the old database.

Checking after the migration

-- Compare row counts
SELECT schemaname, tablename, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Also check that every sequence (SEQUENCE) continues from the right value — otherwise new records will start conflicting on the primary key:

SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));

Common restore errors

Rollback in case of problems

1. Do not shut down the old server until the new one has run under real load for at least a full day.
2. If the application behaves unstably after the switch, point DATABASE_URL back at the old server — its data is untouched.
3. Work out the cause on the new server at a calm pace and repeat the migration.

When this does not fit

Make sure the PostgreSQL version on the new server is >= the version on the old one. Rolling back to an older version is not supported. For databases over 100 GB or a zero-downtime requirement, a one-off pg_dump/pg_restore takes too long — use the logical replication from the section above, or physical replication with pg_basebackup.

The general post-migration check plan is described in post-migration check, and the safe rollback procedure is in migration rollback plan.