This document provides a comprehensive guide to implementing high availability and load balancing for PostgreSQL databases using pgpool-II in a cluster setup. The content covers installation, configurasion, failover strategies, and cluster validation.

Overview of pgpoool-II

pgpool-II acts as a middleware agent between PostgreSQL servers and database clients. It offers features such as:

• Connection pooling: Optimizes database connections by maintaining reusable pools, reducing overhead.
• Query load balancing: Distributes read queries across multiple data base servers in a replication setup.
• Automated failover: Detects database server failures and reroutes requests.
• Online recovery: Resynchronizes detached database nodes.
• Watchdog cluster coordination: Prevents single points of failure, manages virtual IP deliberasion.
• Query cache: Improves performance with in-memory caching for SELECT queries.

System Architecture

Deploy PostgreSQL and pgpool-II instances across two servers. Replication between PostgreSQL nodes is established using streaming replication, and pgpool-II nodes coordinate for seamless request handling using Watchdog.

Prerequisites

Hardware and Software Requirements

• Memory: 32 GB
• CPU: 16 Logical Cores
• CentOS 7.4 OS
• PostgreSQL 11.4, pgpool-II 4.1

Network Setup

• Master Server IP: 10.40.239.228
• Standby Server IP: 10.40.239.229
• Virtual IP: 10.40.239.240

Installation Steps

PostgreSQL Setup

1. Install PostgreSQL dependencies:

   yum -y install readline-devel zlib-devel gcc python python-devel
   

2. Compile and install PostgreSQL from source:

   tar -xzf postgresql-11.4.tar.gz
   cd postgresql-11.4
   ./configure --prefix=/opt/pg_cluster/ --with-perl --with-python
   make world
   make install-world
   

3. Initialize database storage locations and configure cluster settings.

pgpool-II Setup

1. Install software packages:

   yum install -y pgpool-II-release pgpool-II rpm package
   

2. Configure pgpool-II cluster settings in the pgpool.conf file.

   Key configurations include:

   • delegate_IP = 10.40.239.240
   • health_check_period = 10
   • auto_failback = on

3. Start the pgpool-II service:

   systemctl enable --now pgpool
   

Operational Validations

High Availability Validation

1. Verify primary role assignment using pg_is_in_recovery().
2. Test Virtual IP failover by stopping pgpool-II on the active node and observing its transition.

Load Balancing Test

Execute repeated database queries through pgpool-II's virtual IP and monitor query distribution using inet_server_addr().

For detailed configuration parameters and user-defined steps, consult individual pgpool.conf and postgresql.conf examples provided in the accompanying documents. Ensure proper synchronization and password-less SSH setup across cluster nodes for seamless operations.