Technical Tutorials

Database migration is one of the most critical operations in a developer's or DBA's lifecycle. Whether you are moving from an on-premise server to a managed cloud service like Amazon RDS or Google Cloud SQL, upgrading to a newer major version, or simply reorganizing your schema across multiple clusters, the stakes are high. A failed migration can lead to data loss, prolonged downtime, and significant business impact.

This guide moves beyond basic dumps to explore robust, production-grade strategies for migrating PostgreSQL databases. We will cover single-command solutions for smaller datasets, continuous data synchronization for zero-downtime migrations, and best practices for validation and rollback.

Strategy 1: The Classic pg_dump Approach

For smaller databases (typically under 100GB) or during planned maintenance windows where downtime is acceptable, pg_dump remains the most straightforward and reliable method. It generates a SQL script or custom-format archive that can be restored on the destination server.

The key to using pg_dump effectively in a migration context is using the custom format (-Fc), which allows for parallel restoration and greater flexibility.

# From the source server
pg_dump -Fc -h source_host -U source_user -d my_database -f backup.dump

# On the destination server
pg_restore -h dest_host -U dest_user -d new_database backup.dump

While simple, this method requires a period of downtime where the application cannot write to the database. If you have a live application, you must ensure the application is taken offline or pointed to a read-only replica during the dump process to ensure data consistency.

Strategy 2: Zero-Downtime Migration with Logical Replication

For mission-critical applications that cannot afford downtime, logical replication is the gold standard. This method allows you to keep the source and destination databases in sync continuously, allowing you to switch traffic at a precise moment.

Step 1: Prepare the Destination

First, create an empty database on the target server with the same schema. Ensure the wal_level is set to logical on both the source and destination servers.

Step 2: Create Publication and Subscription

On the source server, create a publication for the tables you wish to migrate:

CREATE PUBLICATION my_migration_pub FOR TABLE users, orders, products;

On the destination server, create a subscription that connects to the source:

CREATE SUBSCRIPTION my_migration_sub
CONNECTION 'host=source_ip dbname=my_database user=replicator password=secret'
PUBLICATION my_migration_pub;

PostgreSQL will now begin synchronizing data from the source to the destination. You can monitor progress via the pg_stat_subscription system view. Once the initial sync is complete and caught up with live changes, you can proceed to the cutover.

Validation and Cutover

Before switching traffic, perform a final validation. Check row counts and checksums for critical tables on both instances. When you are ready to cut over, stop writes to the source database (or pause the subscription on the destination to ensure consistency).

-- Pause subscription to ensure final catch-up
SELECT pg_subscription_rel.*, pg_stat_replication.*
FROM pg_subscription_rel 
JOIN pg_stat_replication ON pg_subscription_rel.sql_localid = pg_stat_replication.pid;

Once the replication lag is zero, disable the subscription, switch your application's connection string to point to the new database, and enable writes. Monitor the application closely for the first few hours to ensure stability.

Conclusion

Choosing the right migration strategy depends on your data size, acceptable downtime, and infrastructure complexity. For small shifts, pg_dump is sufficient. For enterprise-grade requirements, logical replication provides the safety and continuity needed for seamless transitions. Always test your migration process in a staging environment before attempting it in production to mitigate risks and ensure a smooth operation.