How to Set Up Pgpool-II for PostgreSQL on Ubuntu

Pgpool-II is a powerful middleware that sits between PostgreSQL clients and database servers. It provides connection pooling, load balancing, health checks, and automatic failover, making it a popular choice for PostgreSQL high-availability architectures. In this blog we’ll learn how to set up Pgpool-II for PostgreSQL on Ubuntu.

Step 1: Install Pgpool-II

apt install pgpool2

Step 2: Configure PostgreSQL Client Authentication

Pgpool-II must be allowed to connect to PostgreSQL servers. Edit the PostgreSQL pg_hba.conf file:

vim /etc/postgresql/16/main/pg_hba.conf

Add the following entry:

host all all IP_ADDRESS/16 trust

Why this is required

• Allows Pgpool-II to connect to PostgreSQL nodes
• CIDR block covers both master and replica
• Uses trust internally (authentication will be enforced at Pgpool-II level)

In production, consider using md5 or scram-sha-256 instead of trust.

Step 3: Configure Pgpool-II (pgpool.conf)

Open the main Pgpool-II configuration file:

vim /etc/pgpool2/pgpool.conf

3.1 Listen on All Interfaces

listen_addresses = '*'
port = 9999

Pgpool-II will now accept connections from any interface on port 9999.

3.2 Register Backend Nodes

Master Node (backend 0)

backend_hostname0 = 'IP_ADDRESS'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/16/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server0'

Key Parameters Explained

Parameter	Description
backend_hostname	IP/hostname of PostgreSQL server
backend_port	PostgreSQL listening port
backend_weight	Traffic distribution ratio
backend_data_directory	PostgreSQL data directory
backend_flag	Enables failover
backend_application_name	Logical node identifier

You can add more nodes by continuing the numbering (backend_hostname1, backend_hostname2 etc.)

Step 4: Enable Pgpool-II Authentication

Since PostgreSQL itself is using trust, we enforce password authentication at Pgpool-II.

4.1 Enable Pool HBA and Password File

enable_pool_hba = on
pool_passwd = 'pool_passwd'

Step 5: Configure Health Checks

health_check_period = 10
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = ''
health_check_database = 'postgres'

Why health checks matter

• Periodically verifies node availability
• Automatically removes unhealthy nodes
• Restores nodes when they recover

Step 6: Create Pgpool-II Passwords (pool_passwd)

Generate a password entry for PostgreSQL users:

pg_md5 --md5auth -f /etc/pgpool2/pgpool.conf -u postgres -p

You’ll be prompted for a password.

This command:

• Encrypts the password using MD5
• Stores it in /etc/pgpool2/pool_passwd

Verify the file:

cat /etc/pgpool2/pool_passwd

Step 7: Configure pool_hba.conf

Open:

vim /etc/pgpool2/pool_hba.conf

Add:

host all all 0.0.0.0/0 md5

This allows:

• Connections from any client
• Password authentication via MD5

Step 8: Restart Pgpool-II

Apply all changes:

systemctl restart pgpool2.service

Step 9: Connect Through Pgpool-II

Use psql just like PostgreSQL, but with port 9999:

psql -p 9999 -U postgres -W

Once connected, verify backend status:

SHOW POOL_NODES;

This command displays:

• Node roles
• Load balancing status
• Health check results

Final Thoughts

Pgpool-II provides a flexible and production-ready solution for PostgreSQL load balancing and high availability. With proper authentication, logging, and health checks, it becomes a powerful control layer between your applications and databases.
Setting up and configuring high-availability database environments can be complex, especially when performance, failover handling, and production stability are critical.

If you need expert assistance to Set up Pgpool-II for PostgreSQL on Ubuntu, our experienced engineers are here to help. With comprehensive DevOps Support Services, we ensures seamless deployment, optimized configuration, load balancing, replication management, and ongoing monitoring tailored to your infrastructure.