A. Infrastructure

FQDN	IP	Purpose 1	Purpose 2
IT-RDBMS01.itenlight.com	10.1.10.150	Primary PostgreSQL instance	Active pgpool-II instance
IT-RDBMS02.itenlight.com	10.1.10.155	Standby PostgreSQL instance	Standby pgpool-II instance

Virtual IP that will be used is 10.1.10.159.

B. Install and Setup PostgreSQL on All Nodes

apt update
apt install postgresql-server

C. Configure Replication

Enter psql console

su - postgres
psql

Enter SQL command:

CREATE ROLE replication WITH REPLICATION PASSWORD 'reppassword' LOGIN;

Save replication password on /var/lib/postgresql/.pgpass file:

*:*:*:replication:reppassword

Then execute these command:

chown postgres:postgres /var/lib/postgresql/.pgpass
chmod 0600 /var/lib/postgresql/.pgpass

Next change the following entries in /etc/postgresql/postgresql.conf file:

listen_addresses = '*'
port = 5433

and Add/change the following entries in /etc/postgresql/pg_hba.conf file:

host  replication     replication     10.1.10.150/32          md5
host  replication     replication     10.1.10.155/32          md5
host  all             postgres        10.1.0.0/16             md5

C. Configuring Primary Server

Execute SQL command from psql :

SELECT * FROM pg_create_physical_replication_slot('it_rdbms02');

Edit /etc/postgresql/postgresql.conf on Primary Server:

wal_level = hot_standby
max_replication_slots = 3
max_wal_senders = 3

Restart Primary postgresql server and see the status

systemctl restart postgresql
systemctl status postgresql

D. Configuring Standby Server

Stop PostgreSQL server:

systemctl stop postgresql

Execute these command to fetch the base data from Primary Server:

sudo -i -u postgres
cd /var/lib/postgresql/12
rm -rf main
pg_basebackup -v -D main -R -P -h <primary_server_ip> -p 5432 -U replication
logout

Edit /etc/postgresql/postgresql.conf on Standby Server

hot_standby = on
hot_standby_feedback = on

Create or edit /var/lib/postgresql/12/main/recovery.signal for Replication Slots:

standby_mode = 'on'
primary_slot_name = 'it_rdbms02'
primary_conninfo = 'host=<primary_server_ip> port=5431 user=replication password=reppassword'
trigger_file = '/var/lib/postgresql/12/main/im_the_master'

Restart standby postgresql server and see the status

systemctl restart postgresql
systemctl status postgresql

E. Testing Replication

Enter psql console and create a sample DB:

sudo -u postgres psql
CREATE DATABASE replicationtest;
CREATE DATABASE
\l

Now you should get the same list of databases on the Standby server, by executing:

sudo -u postgres psql
\l

You can also try to delete the newly created database on the standby server by executing:

DROP DATABASE replicationtest;
ERROR:  cannot execute DROP DATABASE in a read-only transaction

Let's try the same command on the Primary server:

DROP DATABASE replicationtest;
DROP DATABASE

You can recheck database list on both servers to confirm that test database is absent.

F. Installing PGPOOL2

On Ubuntu, run:

apt update
apt install pgpool2

akhdaniel/Setup PGPool + PostgreSQL 12.md