Migrating a PostgreSQL Database Using Replication

Today we will talk about how to migrate a production PostgreSQL database without any downtime for our application. To do this, we will use replication.

First, we need our second server, to which we will migrate the database. Please note that to avoid most issues, you should use servers with identical software and PostgreSQL versions.

For simplicity, I will refer to the source server as master, and the server to which we will migrate the database as replica.

1. BACK UP THE DATABASE AND CONFIGURATION ON THE MASTER SERVER
2. Enable replication on the master server by adding the following lines to the postgresql.conf configuration file:

   wal_level = replica
   max_wal_senders = 3
   wal_keep_size = 1024
   

3. Allow replication access by adding the following lines to the pg_hba.conf configuration file:

   host    replication all <replica IP>    scram-sha-256
   

4. Perform the initial replication, during which all data from the master server is copied to the replica:

   pg_basebackup -h <master IP> -D /var/lib/postgresql/data -U <replication_user> -Fp -Xs -P
   

5. After this, we can start our replica.
6. Prepare our applications for migration by modifying their configurations to use the new server.
7. Stop data writing on the master server:

   SELECT pg_switch_wal();
   

8. Migrate applications to the new server.
9. Promote the replica to master:

   pg_ctl promote -D /var/lib/postgresql/data
   

If Something Goes Wrong

The database was initialized with LC_COLLATE "en_US.UTF-8", which is not recognized by setlocale().

sudo locale-gen en_US.UTF-8
localedef -f UTF-8 -i en_US en_US.UTF-8
sudo update-locale

database "postgres" has a collation version mismatch

ALTER DATABASE guard REFRESH COLLATION VERSION;