Introduction

Db Administration tasks can be a little intimidating to developers. Particularly, setting up database replication in this case. This article links two other articles which have been the only ones to work smoothly and easily for setting up replication for postgresql 16.

1. Static IP

Setting up static IPs on the primary and secondary servers can be done by following the steps in this video.

Note: If DNS switch is greyed out on your system, just set the DNS values separarted by a comma without switching it off, it should still work.

2. Replication Steps

The video [and article] by cherry servers was the one to work best with no issues whatsoever. It has the secondary server stream every single change from the primary server in realtime without issues at all (my addition was synchronous commits to ensure that standbby has the same data).

Also set these on the primary server

max_wal_senders = 5     # max number of walsender processes
                # (change requires restart)
max_replication_slots = 10  # max number of replication slots


synchronous_commit = on     # synchronization level;

synchronous_standby_names = '*'

Notes:

• When the secondary server is turned off, the primary server stops accepting changes because it loses the ability to [synchronously] replicate. Only use this when the secondary server is always present, or disable replication when done with it.
• Although the wal-level is set to logical, the replication type between primary and secondary is in fact streaming, since streaming < logical.

Disabling Replication

Primary server

To disable, restore the settings on the primary server to the initial ones:

• On /etc/postgresql/16/main/postgresql.conf
  • wal_level = logical --> wal_level = minimal
  • wal_log_hints = on --> #wal_log_hints = on
  • ensure #archive_mode = on
  • synchronous_commit = on --> #synchronous_commit = on
  • synchronous_standby_names = '*' --> #synchronous_standby_names = '*'
• On /etc/postgresql/16/main/pg_hba.conf Change this line

host    replication     replica_user        192.168.1.1/24          md5

192.168.1.1/24 is the IP address of the replica server.
Back to this line

host    replication     all         0.0.0.0/0           md5

Secondary server

Remove the file standby.signal from the data directory which is located at /var/lib/postgresql/16/main. (according to this. to be tested.)

Final Notes

This article is more of a diary as to how this process went so I can get back to it in the future if needed, and it can also be helpful to you.
Cover Image Credit:
https://cdn.prod.website-files.com/665de628e01a0041a62ecd14/665de628e01a0041a62ed338_logical_replication_1-p-1080.jpg