DEV Community

Arnob
Arnob

Posted on

PostgreSQL & Pgpool-II Deployment & Configuration Guide

Pgpool-II is a powerful, open-source middleware that sits between PostgreSQL servers and database clients

Architecture

captionless image

What is Pgpool-II?

Pgpool-II Official Website

Pgpool-II is a middleware between your application and PostgreSQL that provides:

  • Connection Pooling
  • Load Balancing
  • Failover & HA
  • Query Caching
  • Replication Support
  • Health Checking

Recommended Production Architecture 

                +-------------------+
                |   Application     |
                +---------+---------+
                          |
                    VIP / LB IP
                          |
                  +-------+-------+
                  |    Pgpool-II  |
                  +-------+-------+
                          |
        +----------------+----------------+
        |                                 |
+-------+--------+              +---------+-------+
| PostgreSQL     |              | PostgreSQL     |
| Primary        |<------------>| Replica        |
| Read/Write     | Streaming    | Read Only      |
+----------------+ Replication  +----------------+
Enter fullscreen mode Exit fullscreen mode

Install PostgreSQL

On Primary Server

Example OS: Ubuntu 26.04

sudo apt update
Enter fullscreen mode Exit fullscreen mode 
sudo apt install -y postgresql postgresql-contrib
Enter fullscreen mode Exit fullscreen mode

Check service:

sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

Configure PostgreSQL Primary

Edit PostgreSQL Config

File:

vi /etc/postgresql/18/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Update:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
archive_mode = on
archive_command = 'true'
Enter fullscreen mode Exit fullscreen mode

Configure pg_hba.conf 

vi /etc/postgresql/16/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Add:

host    replication     replicator      0.0.0.0/0       md5
host    all             all             0.0.0.0/0       md5
Enter fullscreen mode Exit fullscreen mode

Restart:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Create Replication User

Login:

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Create user:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'StrongPassword';
Enter fullscreen mode Exit fullscreen mode

Step 4: Configure Replica Server

Install PostgreSQL on the replica node.

Stop PostgreSQL: 

sudo systemctl stop postgresql
Enter fullscreen mode Exit fullscreen mode

Clear old data: 

sudo rm -rf /var/lib/postgresql/18/main/*
Enter fullscreen mode Exit fullscreen mode

Clone from primary: 

PGPASSWORD='StrongPassword' pg_basebackup \
-h PRIMARY_IP \
-U replicator \
-D /var/lib/postgresql/18/main \
-P -R
Enter fullscreen mode Exit fullscreen mode

Fix Ownership 

sudo chown -R postgres:postgres /var/lib/postgresql/18/main
sudo chmod 700 /var/lib/postgresql/18/main
Enter fullscreen mode Exit fullscreen mode

Verify Recovery Files 

ls -la /var/lib/postgresql/18/main/
Enter fullscreen mode Exit fullscreen mode

Must contain:

standby.signal
Enter fullscreen mode Exit fullscreen mode

Start replica: 

sudo pg_ctlcluster 18 main start
Enter fullscreen mode Exit fullscreen mode

Verify: 

pg_lsclusters
Enter fullscreen mode Exit fullscreen mode

Expected:

18 main 5432 online,recovery
Enter fullscreen mode Exit fullscreen mode

This is GOOD for the replica.

Test Replication 

sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"
Enter fullscreen mode Exit fullscreen mode 
# Expected:
t # true, if f then its false
Enter fullscreen mode Exit fullscreen mode

Important Check on PRIMARY

Primary must allow replication.

postgresql.conf 

wal_level = replica
max_wal_senders = 10
hot_standby = on
listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode

pg_hba.conf 

host replication replicator REPLICA_IP/32 md5
Enter fullscreen mode Exit fullscreen mode

or:

host replication replicator 0.0.0.0/0 md5
Enter fullscreen mode Exit fullscreen mode

Restart primary:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Send the output of:

cat /var/log/postgresql/postgresql-18-main.log
Enter fullscreen mode Exit fullscreen mode

If the replica still stays down,recovery.

Install Pgpool-II

On Pgpool Server 

sudo apt install -y pgpool2
Enter fullscreen mode Exit fullscreen mode

Step 6: Configure Pgpool-II

Main config:

vi /etc/pgpool2/pgpool.conf
Enter fullscreen mode Exit fullscreen mode

Basic Pgpool Configuration 

listen_addresses = '*'
port = 9999
Enter fullscreen mode Exit fullscreen mode 
backend_hostname0 = '10.0.0.10'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'backend_hostname1 = '10.0.0.11'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'enable_pool_hba = onmaster_slave_mode = on
master_slave_sub_mode = 'stream'load_balance_mode = onsr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'postgres_password'health_check_period = 10
health_check_user = 'postgres'
health_check_password = 'postgres_password'failover_on_backend_error = on
Enter fullscreen mode Exit fullscreen mode

Step 7: Configure Pool Authentication

Create password file:

sudo pg_md5 --md5auth --username=postgres postgres_password
Enter fullscreen mode Exit fullscreen mode

Creates:

/etc/pgpool2/pool_passwd
Enter fullscreen mode Exit fullscreen mode

Step 8: Configure pg_hba for Pgpool

File:

/etc/pgpool2/pool_hba.conf
Enter fullscreen mode Exit fullscreen mode

Example:

host all all 0.0.0.0/0 md5
Enter fullscreen mode Exit fullscreen mode

Step 9: Enable PCP Commands

Edit:

/etc/pgpool2/pcp.conf
Enter fullscreen mode Exit fullscreen mode

Add:

admin:md5hash
Enter fullscreen mode Exit fullscreen mode

Generate md5:

pg_md5 mypassword
Enter fullscreen mode Exit fullscreen mode

Step 10: Start Pgpool-II 

sudo systemctl restart pgpool2
sudo systemctl enable pgpool2
Enter fullscreen mode Exit fullscreen mode

Check:

sudo systemctl status pgpool2
Enter fullscreen mode Exit fullscreen mode

Step 11: Test Connection

Connect application:

psql -h PGPOOL_IP -p 9999 -U postgres
Enter fullscreen mode Exit fullscreen mode

Verify Replication

On Primary:

SELECT * FROM pg_stat_replication;
Enter fullscreen mode Exit fullscreen mode

Verify Load Balancing

On Pgpool:

SHOW pool_nodes;
Enter fullscreen mode Exit fullscreen mode

Pgpool-II Features

FeatureDescriptionConnection PoolingReuse DB connectionsLoad BalancingSend SELECT queries to replicasFailoverAuto switch primaryHealth CheckDetect node failureWatchdogVirtual IP HAQuery CacheCache repeated queries

Pgpool-II High Availability (Optional)

For production:

Use:

  • 2 Pgpool-II servers
  • Watchdog
  • VIP (Virtual IP)

Architecture:

App
 |
VIP
 |
+-------------------+
| Pgpool-II Active  |
| Pgpool-II Standby |
+-------------------+
 |
PostgreSQL Cluster
Enter fullscreen mode Exit fullscreen mode

Recommended Production Stack

LayerRecommendedPostgreSQL HAStreaming ReplicationPoolingPgpool-IIVIPKeepalivedMonitoringPrometheus + GrafanaBackuppgBackRestConnection LimitPgBouncer (optional)

Pgpool-II vs PgBouncer

FeaturePgpool-IIPgBouncerPoolingYesYesLoad BalancingYesNoFailoverYesNoReplication AwarenessYesNoLightweightMediumVery LightweightHA SupportYesLimited

Production Recommendation

Best setup:

App
  ↓
HAProxy / Keepalived VIP
  ↓
Pgpool-II Cluster
  ↓
PostgreSQL Primary + Replicas
Enter fullscreen mode Exit fullscreen mode

Useful Commands

Show Pool Nodes 

SHOW pool_nodes;
Enter fullscreen mode Exit fullscreen mode

Attach Node 

pcp_attach_node
Enter fullscreen mode Exit fullscreen mode

Detach Node 

pcp_detach_node
Enter fullscreen mode Exit fullscreen mode

Node Info 

pcp_node_info
Enter fullscreen mode Exit fullscreen mode

Common Problems

ProblemSolutionReplica not syncingCheck wal_level & pg_hbaPgpool login failConfigure pool_passwdFailover not workingEnable watchdogSELECT not balancedEnable load_balance_modeToo many connectionsTune num_init_children

Docker Compose Example 

version: '3.9'
Enter fullscreen mode Exit fullscreen mode 
services:  postgres-primary:
    image: postgres:16
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"  pgpool:
    image: bitnami/pgpool:latest
    ports:
      - "9999:5432"
    environment:
      PGPOOL_BACKEND_NODES: 0:postgres-primary:5432
      PGPOOL_SR_CHECK_USER: postgres
      PGPOOL_SR_CHECK_PASSWORD: postgres
      PGPOOL_POSTGRES_USERNAME: postgres
      PGPOOL_POSTGRES_PASSWORD: postgres
Enter fullscreen mode Exit fullscreen mode

Official Documentation

Top comments (0)