Copying Postgres 12 database to another server with smallest downtime

Question

I'm going to migrate a production PostgreSQL 12 database (~200GB) to another more powerful server. There are a bunch of partitioned tables, some without primary key (theoretically, it won't be a problem to temporarily add them if needed).

Is there any solution to migrate without or at least with the smallest downtime?

Answer 1

As @FrankHeikens wrote in the comment to the question, we need to configure master-standby replication.

Here are steps on master (Postgres 12):

NOTE: All commands run on behalf of a user with sudo permissions.

1. Create Postgres user for replication

psql -U postgres
CREATE ROLE replication WITH REPLICATION PASSWORD 'REPLICATION_USER_PASSWORD' LOGIN;

2. Add replication user to pg_hba.conf

sudo nano /etc/postgresql/12/main/pg_hba.conf
# Add the following line
> To Existing Server
host    replication     replication     STANDBY_SERVER_IP/32       md5

3. Enable replication.

NOTE: wal_keep_segments should be as much enough to keep wal files while base backup is in progress.

sudo nano /etc/postgresql/12/main/postgres.conf
# Uncomment and edit values
listen_addresses = '*' 
wal_level = replica 
wal_keep_segments = 256   # (each segment is 16 MB by default, so ~5GB of free space is need  **for our case**)
max_wal_senders = 2 # (for case of single standby)
max_replication_slots = 2 # (for case of single standby)
hot_standby = on
hot_standby_feedback = on

4. Open Postgres network port (5432 by default) in a system firewall.

5. Restart Posgtres

sudo service postgresql restart

Here are steps on standby (Postgres 12):

NOTE: All commands run on behalf of a user with sudo permissions.

1. . Stop Posgtres

sudo service postgresql stop

2. Clean up data and logs directories.

sudo rm /var/log/postgresql/*
sudo rm -rf /var/lib/postgresql/12/main/*
# Ensure that cleaning up is none
sudo du -sh /var/lib/postgresql/12/main/

2. Enable replication.

sudo nano /etc/postgresql/12/main/postgres.conf
# Uncomment and edit values
hot_standby = on
primary_conninfo = 'user=replication password=<REPLICATION_USER_PASSWORD> host=MASTER_SERVER_IP port=5432'

3. Enable Postgres standby mode.

cd /etc/postgresql/12/main/
sudo touch standby.signal
sudo chown postgres:postgres standby.signal

4. Create base backup.

# Enter 2 passwords: for local 'postgres' user and then for remote 'replication' user
su - postgres -c "pg_basebackup --host=MASTER_SERVER_IP  --username=replication --pgdata=/var/lib/postgresql/12/main --wal-method=stream --write-recovery-conf"

5. After base backup is done start standby server.

sudo service postgresql start

To check the progress of replication:

psql -U postgres

# On master
select * from pg_stat_replication;

# On standby
select * from pg_stat_wal_receiver;

To promote standby server to master mode:

psql -U postgres
select pg_promote();