Moving a Big Database Without Breaking Things

The Problem with Big Migrations

Eventually you will need to move a database. You might be moving to a more powerful server or migrating between cloud providers. If your database is small you can afford some downtime. You run pg_dump on the old server and run pg_restore on the new one and point your app to the new address. This is simple and it works.

But when your database is large or your service needs to stay online this approach fails. A pg_dump and pg_restore on a few hundred gigabytes could take hours. Your application cannot be down for hours. This kind of migration feels like open heart surgery on a running system. It is scary and it is risky.

There is a better way. PostgreSQL has a powerful feature called logical replication that lets you stream changes from one database to another in real time. We can use it to perform a migration with only seconds of downtime.

The Strategy Logical Replication

Logical replication works by creating a 'publication' on your source database for the tables you want to copy. A 'subscription' on your new destination database then connects to the source and receives a continuous stream of changes. It is like the new database is listening to everything the old one does and copying it immediately.

This lets us get the new database in sync with the old one while the old one is still serving live traffic. Once they are synchronized the switch or cutover is as simple as updating a connection string in your application config.

This guide shows you the step by step process. It assumes you are using PostgreSQL 10 or newer.

Step 1 Configure the Source Database

First we need to tell your current primary database that it should prepare to send replication data. This requires a few changes to your postgresql.conf file.

# postgresql.conf on the source server

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10

The wal_level needs to be logical. This adds enough information to the database’s write ahead log to decode the changes. max_wal_senders and max_replication_slots control how many concurrent replication clients can connect. A value of 10 is a safe starting point.

You will need to restart your PostgreSQL server for these changes to take effect. This is the only restart required on the source server.

Next create a dedicated user for replication. It is better to use a specific user rather than your superuser for the connection.

-- Connect to your source database as a superuser
CREATE ROLE migration_user WITH REPLICATION LOGIN PASSWORD 'a-very-strong-password';
GRANT CONNECT ON DATABASE your_db TO migration_user;

Step 2 Create a Publication

Now you tell the source database what you want to replicate. A publication is just a named group of tables. The simplest way is to publish all tables.

-- Run this on the source database
CREATE PUBLICATION all_tables_pub FOR ALL TABLES;

This tells PostgreSQL to make all changes to all tables available for subscribers. You can be more selective if needed but for a full server migration FOR ALL TABLES is what you want.

Step 3 Get an Initial Copy

The new database needs a starting point. Replication only sends changes so we must first load a complete snapshot of the data. We will use pg_dump for this but in two parts to avoid issues with data changing during the dump.

First dump the schema only from the source and load it onto the new destination server.

# Dump schema from the source server
pg_dump -h source.db.host -U postgres -d your_db --schema-only > schema.sql

# Restore schema on the destination server
psql -h destination.db.host -U postgres -d your_db < schema.sql

Your new database now has all the tables but they are empty. Now we can create the subscription. This is the magic part. When the subscription starts it will automatically copy the existing data for us.

Step 4 Create the Subscription

On your new destination server connect to the database and run the CREATE SUBSCRIPTION command. This tells the new database to connect to the source and start syncing.

-- Run this on the destination database
CREATE SUBSCRIPTION all_tables_sub
CONNECTION 'host=source.db.host port=5432 user=migration_user password=a-very-strong-password dbname=your_db'
PUBLICATION all_tables_pub;

As soon as you run this command the destination server connects to the source. It begins a process where it copies all existing data from every table in the publication. Once that initial copy is complete it will start receiving and applying all new changes in real time.

Step 5 Verify and Monitor Sync

This initial data copy can take a while for a large database. You need to know when it is finished and caught up. You can monitor the progress by querying the pg_stat_subscription view on the destination server.

-- Run on the destination server to check status
SELECT received_lsn, latest_end_lsn FROM pg_stat_subscription;

When the received_lsn is the same as the latest_end_lsn the replica is up to date with the source. You can also verify by checking row counts on a few key tables on both databases. If they match you are in sync.

Give it time to stabilize. You want to see that the lag is consistently zero or near zero for a few minutes before you proceed.

Step 6 The Cutover

This is the critical step. Your source database is live and your destination database is now a perfect real time mirror. To switch you just need to point your application at the new server.

The plan is simple.

1. Put your application into a brief maintenance mode or stop the application servers.
2. Update the database connection string in your application’s configuration to point to the new destination server’s address.
3. Restart your application.

The actual downtime is only the time it takes for your application to stop and restart. For most services this is less than a minute. All the hard work of moving data happened online with no user impact.

Step 7 Cleanup

After the cutover your application is running on the new database. You should monitor it carefully to ensure everything is working as expected. Once you are confident you can clean up the replication.

On the new server you no longer need the subscription.

-- Run on the destination server
DROP SUBSCRIPTION all_tables_sub;

Now the new server is a standalone primary database. You can then go back to the old server and remove the publication and the replication user. Then you are free to shut down the old server completely.

This process turns a high risk multi hour downtime event into a controlled low risk procedure. It requires planning and careful execution but it is a reliable way to migrate large active PostgreSQL databases.

— Rishi Banerjee
September 2025