PostgreSQL 16 + Pgpool-II 4.5.0 3-Node Streaming Replication setup on Ubuntu 22.04

• Modern applications demand databases that are highly available, fault-tolerant, and scalable.
• PostgreSQL delivers enterprise-grade reliability and data integrity for critical workloads.
• Pgpool-II acts as a smart middleware, ensuring uninterrupted service with failover, load balancing, and connection pooling.
• Together, they form a 3-node PostgreSQL cluster that’s production-ready, reliable, and always-on.

Cluster Design:

1. PostgreSQL Installation (All Nodes)

sudo apt update -y
sudo apt install curl ca-certificates -y
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgres.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update -y
sudo apt install postgresql-16 -y        

Check:

psql --version        

2. Primary Node Configuration (test-dba-1 / 10.0.0.1)

Edit postgresql.conf:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/16/main/archive/%f'
synchronous_standby_names = 'standby1'Create archive folder:        

Create archive folder:

sudo mkdir -p /var/lib/postgresql/16/main/archive
sudo chown postgres:postgres /var/lib/postgresql/16/main/archive        

Edit pg_hba.conf:

# Allow replication from Pgpool user
host    replication     pgpool      10.0.0.1/32   scram-sha-256
host    replication     pgpool      10.0.0.2/32   scram-sha-256
host    replication     pgpool      10.0.0.3/32   scram-sha-256

# Allow connections from Pgpool
host    all             all         10.0.0.1/32   scram-sha-256
host    all             all         10.0.0.2/32   scram-sha-256
host    all             all         10.0.0.3/32   scram-sha-256
# on all PostgreSQL nodes
host    all             pgpool      10.0.0.1/24   scram-sha-256
host    all             pgpool      10.0.0.2/24   scram-sha-256
host    all             pgpool      10.0.0.3/24   scram-sha-256        

Reload PostgreSQL:

sudo systemctl restart postgresql        

3. Users Setup (Primary Only)

Login as postgres: sudo -u postgres psql

#Create replication and Pgpool users and alter postgres user:
postgres=# SET password_encryption = 'scram-sha-256';
SET
postgres=# CREATE ROLE pgpool WITH LOGIN PASSWORD 'pgpoolpass';
CREATE ROLE
postgres=# GRANT pg_monitor TO pgpool;
GRANT ROLE
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'replpass';
CREATE ROLE
postgres=# ALTER USER postgres WITH PASSWORD 'Dba123';        

Explanation: LOGIN → allows login REPLICATION → required for streaming replication ENCRYPTED PASSWORD → secure password This role is only needed on the primary. Standbys use it to connect; you must not create it separately on standbys (replication does not replicate role definitions from standby → primary).

#Check encrypted passwords:

4. Standby Node Preparation (test-dba-2 / test-dba-3)

I. Stop PostgreSQL:

sudo systemctl stop postgresql        

II. Clean old data:

sudo rm -rf /var/lib/postgresql/16/main/*        

III. Take base backup from primary:

sudo -u postgres pg_basebackup -h 10.0.0.1 -D /var/lib/postgresql/16/main -U repl -P -R        

This creates:

standby.signal → marks server as standby

postgresql.auto.conf → contains connection details.

IV. Start PostgreSQL:

sudo systemctl start postgresql        

V. Set primary_conninfo for each standby:

# Node2 (Synchronous):
ALTER SYSTEM SET primary_conninfo TO 'user=repl password=replpass host=10.0.0.1 port=5432 application_name=standby1';

# Node3 (Synchronous):
ALTER SYSTEM SET primary_conninfo TO 'user=repl password=replpass host=10.0.0.1 port=5432 application_name=standby2';        

Reload configuration:

systemctl reload postgresql@16-main5.Replication Validation.        

5. Replication Validation:

# Primary
SELECT application_name, client_addr, state, sync_state FROM pg_stat_replication;
SHOW synchronous_standby_names;
 application_name | client_addr |   state   | sync_state 
------------------+-------------+-----------+------------
 standby3         | 10.0.0.3 | streaming | async
 standby2         | 10.0.0.2 | streaming | async
(2 rows)

 synchronous_standby_names 
---------------------------
 standby1
(1 row)

# On standbys:
SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)        

Pgpool-II Installation & configuration (All Nodes)

1. Install dependencies:

sudo apt update
sudo apt install -y \
  build-essential \
  libssl-dev \
  libpam0g-dev \
  libpq-dev \
  libreadline-dev \
  libkrb5-dev \
  wget \
  tar \
  libpgpool2 \
  pkg-config        

2. Compile Pgpool-II:

cd /home/Dbteam
wget https://www.pgpool.net/download.php?f=pgpool-II-4.5.0.tar.gz -O pgpool-II-4.5.0.tar.gz
tar -xvf pgpool-II-4.5.0.tar.gz
cd pgpool-II-4.5.0

./configure --with-pgsql=/usr --with-openssl CPPFLAGS="-I/usr/include/postgresql" LDFLAGS="-L/usr/lib/x86_64-linux-gnu"
make
sudo make install        

3. Create user and log directories:

sudo useradd -r -m -s /bin/false pgpool
sudo mkdir -p /etc/pgpool2 /var/log/pgpool
sudo chown pgpool:pgpool /etc/pgpool2 /var/log/pgpool
sudo chmod 750 /var/log/pgpool        

4. Pgpool-II Configuration files:

I. Node ID Generation:

echo 0 | sudo tee /etc/pgpool2/pgpool_node_id   # test-dba-1
echo 1 | sudo tee /etc/pgpool2/pgpool_node_id   # test-dba-2
echo 2 | sudo tee /etc/pgpool2/pgpool_node_id   # test-dba-3        

II. pgpool_hba.conf:

host    all         all         0.0.0.0/0       scram-sha-256
host    replication repl        10.0.0.0/24     scram-sha-256
host    all         postgres    10.0.0.0/24     scram-sha-256
host    all         pgpool      10.0.0.0/24     scram-sha-256        

III. pgpool.conf:

# Clustering mode
backend_clustering_mode = 'streaming_replication'

# Listen addresses
listen_addresses = '*'
pcp_listen_addresses = '*'

# Ports
port = 9999
pcp_port = 9898
# wd_port is per node (used in watchdog below)
wd_heartbeat_port = 9694

# Streaming replication check
sr_check_user = 'repl'
sr_check_password = 'replpass'

# Health check
health_check_period = 5
health_check_timeout = 30
health_check_user = 'repl'
health_check_password = 'replpass'
health_check_max_retries = 3

# Backend configuration
backend_hostname0 = '10.0.0.1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/16/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'

backend_hostname1 = '10.0.0.2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/16/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'

backend_hostname2 = '10.0.0.3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/16/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server3'

# Failover commands
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

# Online recovery
recovery_user = 'postgres'
recovery_password = 'Dba123'
recovery_1st_stage_command = 'recovery_1st_stage'

# Pool HBA
enable_pool_hba = on

# Watchdog (without VIP)
use_watchdog = on
wd_lifecheck_method = 'heartbeat'
wd_interval = 10

# Node 0
wd_node_id0 = 0
wd_hostname0 = '10.0.0.1'
wd_port0 = 9000
wd_priority0 = 1

# Node 1
wd_node_id1 = 1
wd_hostname1 = '10.0.0.2'
wd_port1 = 9000
wd_priority1 = 1

# Node 2
wd_node_id2 = 2
wd_hostname2 = '10.0.0.3'
wd_port2 = 9000
wd_priority2 = 1

# Heartbeat for all nodes
heartbeat_hostname0 = '10.0.0.1'
heartbeat_port0 = 9694
heartbeat_hostname1 = '10.0.0.2'
heartbeat_port1 = 9694
heartbeat_hostname2 = '10.0.0.3'
heartbeat_port2 = 9694

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool'
log_filename = 'pgpool-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB

# PCP user
pcp_user = 'pgpool'
pcp_password = 'pgpoolpass'        

IV. Pool Password (pool_passwd) Setup:

sudo -u postgres bash -c "echo 'someSecretKey' > /var/lib/postgresql/.pgpoolkey"
sudo chown postgres:postgres /var/lib/postgresql/.pgpoolkey
sudo chmod 600 /var/lib/postgresql/.pgpoolkey

sudo -u postgres pg_enc -m -k /var/lib/postgresql/.pgpoolkey -f /etc/pgpool2/pool_passwd -u pgpool -p
sudo chown postgres:postgres /etc/pgpool2/pool_passwd
sudo chmod 600 /etc/pgpool2/pool_passwd        

V. PCP User Setup:

sudo mkdir -p /usr/local/etc
echo 'pgpool:'`pg_md5 pgpoolpass` >> /etc/pgpool2/pcp.conf
sudo ln -s /etc/pgpool2/pcp.conf /usr/local/etc/pcp.conf
cat /etc/pgpool2/pcp.conf
cat /usr/local/etc/pcp.conf
sudo chown pgpool:pgpool /etc/pgpool2/pcp.conf
sudo chmod 600 /etc/pgpool2/pcp.conf        

VI. Failover Script (failover.sh):

#!/bin/bash
# Failover script for Pgpool-II
# Arguments: %d %h %p %D %m %H %M %P %r %R %N %S

FAILED_NODE_ID=$1
FAILED_NODE_HOST=$2

echo "`date`: Failover detected on node $FAILED_NODE_HOST (ID=$FAILED_NODE_ID)" >> /var/log/pgpool_failover.log
exit 0        

VII. Follow_primary script(follow_primary.sh)

#!/bin/bash
NEW_PRIMARY_HOST=$2
echo "`date`: New primary is $NEW_PRIMARY_HOST" >> /var/log/pgpool_follow_primary.log
exit 0        

VIII. recovery script (recovery_1st_stage)

#!/bin/bash
PRIMARY_HOST=$1
RECOVERY_TARGET=$2

echo "`date`: Starting recovery for node $RECOVERY_TARGET from $PRIMARY_HOST" >> /var/log/pgpool_recovery.log
# Example pg_basebackup command (customize paths/users)
# pg_basebackup -h $PRIMARY_HOST -U repl -D /var/lib/pgsql/16/data -Fp -Xs -P -R
exit 0        

Permissions:

chmod -R pgpool:pgpool /etc/pgpool2/
chmod 600 /etc/pgpool2/        

Sample scripts available default path : /usr/local/etc/

-rw-r--r-- 1 root root 1.9K Oct  6 15:33 replication_mode_recovery_2nd_stage.sample
-rw-r--r-- 1 root root 3.6K Oct  6 15:33 replication_mode_recovery_1st_stage.sample
-rw-r--r-- 1 root root 3.4K Oct  6 15:33 recovery_1st_stage.sample
-rw-r--r-- 1 root root 3.4K Oct  6 15:33 pool_hba.conf.sample
-rw-r--r-- 1 root root 1.2K Oct  6 15:33 pgpool_remote_start.sample
-rw-r--r-- 1 root root  52K Oct  6 15:33 pgpool.conf.sample
-rw-r--r-- 1 root root  858 Oct  6 15:33 pcp.conf.sample
-rw-r--r-- 1 root root 6.9K Oct  6 15:33 follow_primary.sh.sample
-rw-r--r-- 1 root root 2.8K Oct  6 15:33 failover.sh.sample
-rw-r--r-- 1 root root  790 Oct  6 15:33 escalation.sh.sample
-rw-r--r-- 1 root root 2.5K Oct  6 15:33 aws_rtb_if_cmd.sh.sample
-rw-r--r-- 1 root root 1.6K Oct  6 15:33 aws_eip_if_cmd.sh.sample        

5. Pgpool Service commands:

sudo systemctl restart pgpool2
sudo systemctl start pgpool2
sudo systemctl stop pgpool2
sudo systemctl status pgpool2        

#log path:

tail -f /var/log/pgpool/pgpool-*.log        

#service validation:

sudo -u postgres /usr/local/bin/pgpool -f /etc/pgpool2/pgpool.conf -a /etc/pgpool2/pgpool_hba.conf -n        

#Validate nodes:

psql -h 10.0.0.1 -p 9999 -U pgpool -d postgres -c "SHOW pool_nodes;"        

Output:

#Pgpool SCRAM connection:

psql "host=10.0.0.2 port=5432 dbname=postgres user=pgpool sslmode=disable"        

#pcp_node_info command:

pcp_node_info -h 10.0.0.2 -p 9898 -U pgpool -W
10.0.0.1 5432 2 0.333333 up up primary primary 0 none none 2025-10-07 12:53:30
10.0.0.2 5432 3 0.333333 down up standby standby 0 none none 2025-10-07 12:53:30
10.0.0.3 5432 3 0.333333 down up standby standby 0 none none 2025-10-07 12:53:30        

#pcp_recovery_node command:

pcp_recovery_node -h 10.0.0.2 -p 9898 -U pgpool -n 0 -W        

Conclusion & Takeaways

• A 3-node PostgreSQL 16 + Pgpool-II 4.5 cluster on Ubuntu 22.04 provides high availability (HA), load balancing, and automatic failover for mission-critical workloads.
• Streaming replication (with one primary and two standbys) ensures data redundancy and disaster recovery readiness.
• Pgpool-II acts as the intelligent middle layer.
• Connection pooling
• Read/Write query segregation
• Health checks and watchdog support

Common Issues & Troubleshooting Guidelines

1. Authentication Failures
2. Replication Lag / Delays
3. Failover Issues
4. Pgpool-II Health Check Failures

Best Practices

• Always use separate replication user with least privileges.
• Regularly monitor pg_stat_replication for standby health.
• Schedule periodic base backups using pg_basebackup or tools like barman.
• Automate Pgpool-II log rotation to prevent disk bloat.
• Keep Pgpool-II and PostgreSQL versions consistent across all nodes.

📚 Knowledge Base Contribution by N. Madhusudanarao.