Connectivity

On the primary, it is critical that the postgres port and IP address only be available from the replica IP address. Ideally the replication would be over a private IP.

Primary | ufw allow from [replica IP] to [primary IP] port 5432

Primary

Tune database for your server as per https://pgtune.leopard.in.ua/

After basic tuning, replication related changes are required to postgresql.conf

sudo nano /etc/postgresql/16/main/postgresql.conf
listen_addresses = '*' - private IP if supported.
wal_level = replica
full_page_writes = on
wal_log_hints = on
max_wal_senders = 10
sudo pg_ctlcluster 16 main restart

Create a replica user on the master:

sudo -u postgres psql postgres
CREATE ROLE replica_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD '*****';
\q

Changes required in pg_hba.conf

Set up authentication on the primary server to allow credentialed replication connections from trusted IP.

sudo nano /etc/postgresql/16/main/pg_hba.conf
host replication replica_user IPv4/32(replica vm IP) md5
host replication replica_user IPv6/64(replica vm IP) md5
sudo pg_ctlcluster 16 main restart

Standby

Install and tune postgres

sudo pg_ctlcluster 16 main stop
rm -rf /var/lib/postgresql/16/main
sudo pg_basebackup -h [primary IP] -p 5432 -U [replica_username] -R -P -v -C --slot=[descriptive-slot-name] -D /var/lib/postgresql/16/main/
Check and grant permission to the data folder
ls -la /var/lib/postgresql/16/main/
chmod -R 700 /var/lib/postgresql/16/main/
chgrp -R postgres /var/lib/postgresql/16/main/
chown -R postgres /var/lib/postgresql/16/main/
sudo nano /var/lib/postgresql/16/main/standby.signal
standby_mode = 'on'
sudo nano /etc/postgresql/16/main/postgresql.conf
listen_addresses = '*' (ideally private IP only)
max_connections = 300
primary_conninfo = 'host=[primary IP or host name] user=replica_username password=********'
primary_slot_name = 'descriptive-slot-name'
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
wal_receiver_create_temp_slot = on
wal_receiver_status_interval = 10s
hot_standby_feedback = on
wal_receiver_timeout = 60s
wal_retrieve_retry_interval = 5s
recovery_min_apply_delay = 0
sudo pg_ctlcluster 16 main restart

Misc. Commands

  • sudo -u postgres psql postgres
  • \du ...display users
  • Change password of replica_user | ALTER USER replica_user WITH PASSWORD '*******';
  • Create replica user | CREATE ROLE replica_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD '*****';
  • To check what are the existing slots are available
    # select * from pg_replication_slots;
  • And if you want to drop a slot, you can go to the master db, run these comands.
    # select pg_drop_replication_slot('replica-slot-name');

Promote a failover ( read-only replica ) instance to read-write

    sudo su - postgres
    /usr/lib/postgresql/16/bin/pg_ctl promote -D /var/lib/postgresql/16/main/
    after running above - you should see "waiting for server to promote.... done server promoted"...
    exit ( exit as pg user )
    sudo pg_ctlcluster 16 main restart

If you have unused replication slots on the primary the wal files accumulate and may cause your hard disk to run out of space - be sure to delete unused slots.