We are implementing the leader-follower HA method. Other things such as backup, recovery, multi-leader HA, and other experiments will be written into the documentation.
Steps to deploy High availability PostgreSQL, In this consideration, we are using PostgreSQL version 13. Ensure you have at least two machines with public IP, and It should be discoverable.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-13
Ensure the above installation steps are in your both machine, Now we are giving the names primary, and replica1,
Configuration file -/etc/postgresql/13/main/postgresql.conf
Host based authentication - /etc/postgresql/13/main/pg_hba.conf
Log - /var/log/postgresql/postgresql-13-main.log
Data Directory - /var/lib/postgresql/13/main
In the leader-follower method, We can write/read into the leader, and replicas are read-only. Leader propagates changes to replicas sync/async.
SSH into the leader
Change postgresql.conf file according to below mentioned.
listen_addresses = '*'
port = 5432
wal_level = replica
synchronous_commit = on
max_wal_senders = 10 # Max number of replicas, 0 means disable replication (Hot standby)
synchronous_standby_names = 'replica1' # We can specify multiple names
# Below are optional, If your setup then well and good.
# If file not exist, then copy into specified location.
# Future you can backup, and restore it from.
# Make sure you created directory
archive_mode = on
archive_command = 'test ! -f /home/bhautik/pg_archive/%f && cp %p /home/bhautik/pg_archive/%f'
We need to create role with REPLICATION
CREATE ROLE replica1 REPLICATION PASSWORD 'replica1';