Usefull Commands

  • sudo -u postgres psql postgres
  • \du ...display users
  • ALTER USER replica_user WITH PASSWORD '*******'; - change password of replica_user
  • CREATE ROLE replica_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD '*****'; - create replica user
  • dropuser -e;
  • 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 this sql...
    # select pg_drop_replication_slot('replica-slot-name');
  • Promote a failover instance to read-write
    On the replica
    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 up and cause your hard disk to run out of space, be sure to delete unused slots.

Connectivity

On the primary, it is critical that the postgres port and IP address only be available from the replica IP address.

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 required to postgresql.conf

sudo nano /etc/postgresql/16/main/postgresql.conf
listen_addresses = '*' - or 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 - bottom of file

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