How-To Guides

Mastering PostgreSQL Database Migration: A Comprehensive Guide

Oct 13, 2026 · 2 views
devopspostgresqldatabase migrationdata migrationpg_dump

Database migration is one of the most critical and high-risk tasks in software engineering. Whether you are upgrading from version 9.6 to 15, moving from on-premise servers to cloud infrastructure like AWS RDS, or switching between hosting providers, a failed migration can lead to data loss and significant downtime. This guide explores the most reliable methods for migrating PostgreSQL databases, balancing safety, speed, and minimal disruption.

Choosing the Right Strategy

Before diving into commands, you must select the appropriate strategy based on your downtime tolerance and data volume. The two primary approaches are Logical Backup and Restore and Physical/Logical Replication.

  • Logical Backup (pg_dump): Best for small to medium databases or when minimal downtime is acceptable. It exports data as SQL or custom format files.
  • Logical Replication: Ideal for large databases requiring near-zero downtime. It streams changes in real-time until the final switch.

Method 1: Using pg_dump and pg_restore

The pg_dump utility is the industry standard for logical backups. It is schema-aware and can handle complex data types, functions, and triggers. For production environments, always use the custom format (-Fc) to allow for parallel restoration and selective table recovery.

Step 1: Create the Dump

Connect to your source database and generate a compressed, custom-format archive.

# Dump all tables, schemas, and data
pg_dump -h old-db-host -p 5432 -U postgres -Fc -f production_backup.dump mydb

# For large databases, use parallel workers to speed up the process
pg_dumpall -h old-db-host -p 5432 -U postgres -Fc -f full_cluster_backup.dump

Step 2: Transfer and Restore

Securely transfer the dump file to the new server using scp or cloud storage. Then, restore the data into the new instance. Parallel restoration is crucial for performance.

# Create an empty database on the new instance
createdb -h new-db-host -U postgres mydb_new

# Restore with parallel workers
pg_restore -h new-db-host -p 5432 -U postgres -d mydb_new -j 4 --no-owner production_backup.dump

Note: The --no-owner flag ensures that the current user owns all objects, preventing permission issues if the original user doesn't exist on the new server.

Method 2: Zero-Downtime via Logical Replication

For massive databases where downtime is unacceptable, logical replication allows the new instance to stay in sync with the old one. This requires PostgreSQL 10 or later.

Configuration Steps

  1. Source Database: Enable WAL archiving or ensure sufficient max_wal_senders.
  2. Target Database: Create a replication slot.
  3. Initialize: Use pg_basebackup for physical setup or logical replication slots for pure logical sync.

On the target server, create a replication slot to retain WAL records:

CREATE REPLICATION SLOT my_rep_slot LOGICAL "pgoutput";

On the source server, publish all tables for replication:

CREATE PUBLICATION my_pub FOR ALL TABLES;

On the target, create a subscription to start streaming data:

SUBSCRIBE my_sub TO my_pub
CONNECTION 'host=old-db-host port=5432 user=replicator password=secret'
WITH (copy_data = true);

Once the subscription is caught up (verify by checking pg_stat_subscription), you can stop writes to the old database, perform the final cutover, and drop the subscription.

Verification and Best Practices

Never trust a migration without verification. After restoring, run checksum queries to compare row counts and critical data samples between source and target.

-- Check row count for a critical table
SELECT COUNT(*) FROM orders;

-- Check for missing foreign key integrity
SELECT COUNT(*) FROM child_table WHERE id NOT IN (SELECT id FROM parent_table);

Key best practices include:

  • Test in Staging: Always rehearse the migration in a production-like environment first.
  • Backup Before Migration: Ensure you have a full backup of the source before starting.
  • Monitor Disk I/O: Large dumps can saturate disk bandwidth; schedule during off-peak hours if using pg_dump.
  • Schema Compatibility: If crossing major versions (e.g., 9.6 to 12), check for deprecated features using pg_upgrade check mode.

Conclusion

Choosing the right migration path depends on your specific constraints. For most applications, pg_dump with custom formatting offers a robust, straightforward solution. However, for mission-critical systems with high availability requirements, logical replication provides the safety net of real-time synchronization. By following these steps and rigorously verifying data integrity, you can migrate PostgreSQL databases with confidence and minimal risk.

Share:
Related Posts