Pgpool-II

1. Overview

Pgpool-II is middleware placed between database clients and IvorySQL servers. It provides persistent backend connection pools, health and streaming-replication checks, read-query load balancing, and building blocks for automated failover.

This guide was verified with IvorySQL 5.4 (PostgreSQL 18.4) and Pgpool-II 4.7.2 on Ubuntu 22.04 x86_64. The validation used one IvorySQL primary and one streaming-replication standby.

2. Verified compatibility

Capability Status Verification

Build against IvorySQL client libraries

Supported

Pgpool-II was configured with the IvorySQL installation prefix and built successfully

Backend connection pooling

Supported

Repeated client sessions reused Pgpool-II child-process backend pools

Streaming-replication discovery

Supported

SHOW POOL_NODES identified the primary and standby correctly

Read-query load balancing

Supported

Both nodes received SELECT queries with equal configured weights

Oracle-compatible sessions

Supported with notes

A session switched to ivorysql.compatible_mode = oracle and queried through Pgpool-II successfully

3. Prerequisites

  • IvorySQL 5.4 is installed on every database node.

  • IvorySQL streaming replication is already working and the standby accepts read-only queries.

  • The build host has a C compiler, GNU make, Flex, Bison, and OpenSSL development files.

  • Pgpool-II can reach the PostgreSQL-compatible port of every IvorySQL node.

Pgpool-II does not create or repair IvorySQL streaming replication. Verify replication independently before enabling Pgpool-II.

4. Build and install Pgpool-II

curl -LO https://www.pgpool.net/source/pgpool-II-4.7.2.tar.gz
tar -xzf pgpool-II-4.7.2.tar.gz
cd pgpool-II-4.7.2

./configure \
  --prefix=/usr/local/pgpool-II-4.7.2 \
  --with-pgsql=/usr/local/ivorysql/ivorysql-5 \
  --with-openssl
make -j"$(nproc)"
sudo make install

Replace the IvorySQL prefix with the directory that contains bin/pg_config, include/postgresql, and lib in your installation.

Confirm that Pgpool-II uses the expected version:

/usr/local/pgpool-II-4.7.2/bin/pgpool --version

5. Prepare a monitoring role

Create the same login on the primary and make sure its definition reaches the standby. Membership in pg_monitor lets Pgpool-II inspect streaming-replication state without using a superuser account.

CREATE ROLE pgpoolcheck LOGIN PASSWORD 'replace-with-a-strong-password';
GRANT pg_monitor TO pgpoolcheck;

Use pool_passwd or an operating-system password file instead of putting a production password directly in pgpool.conf. Pgpool-II supports SCRAM authentication; configure matching rules in pool_hba.conf and the IvorySQL pg_hba.conf files.

6. Configure Pgpool-II

Start from the installed pgpool.conf.sample. The following excerpt shows the settings essential to a two-node validation deployment:

backend_clustering_mode = 'streaming_replication'

listen_addresses = 'localhost'
port = 9999
unix_socket_directories = '/tmp'

backend_hostname0 = '10.0.0.11'
backend_port0 = 5333
backend_weight0 = 1
backend_data_directory0 = '/data/ivorysql/primary'
backend_flag0 = 'DISALLOW_TO_FAILOVER'
backend_application_name0 = 'ivory_primary'

backend_hostname1 = '10.0.0.12'
backend_port1 = 5333
backend_weight1 = 1
backend_data_directory1 = '/data/ivorysql/standby'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_application_name1 = 'ivory_standby'

load_balance_mode = on
sr_check_period = 10
sr_check_user = 'pgpoolcheck'
sr_check_database = 'postgres'

health_check_period = 10
health_check_user = 'pgpoolcheck'
health_check_database = 'postgres'

DISALLOW_TO_FAILOVER is intentional in this minimal configuration: it prevents an incomplete example from promoting or detaching nodes automatically. Before using ALLOW_TO_FAILOVER, configure and test failover_command, standby promotion, follow-primary handling, fencing, and optionally Watchdog.

Start Pgpool-II in the foreground while validating the configuration:

/usr/local/pgpool-II-4.7.2/bin/pgpool \
  -n -f /etc/pgpool-II/pgpool.conf

7. Verify the integration

Connect to the Pgpool-II port rather than directly to a backend:

psql -h pgpool-host -p 9999 -U application_user -d application_db

Check node discovery and query distribution:

SHOW POOL_NODES;
SELECT count(*) FROM application_table;
SHOW POOL_NODES;
SHOW POOL_PROCESSES;

In the IvorySQL 5.4 validation, Pgpool-II reported both nodes as up, assigned roles primary and standby, and showed a replication delay of zero. With equal weights, 21 initial read queries were distributed 9 to the primary and 12 to the standby.

8. Oracle-compatible mode

Oracle compatibility can be enabled through the pooled PostgreSQL-compatible connection:

SET ivorysql.compatible_mode = oracle;
SELECT 'connected through Pgpool-II' AS status FROM dual;

The validation succeeded through Pgpool-II and continued to use the replicated backend pair.

9. Operational considerations

  • This guide validates Pgpool-II on IvorySQL’s PostgreSQL-compatible endpoint. It does not validate proxying an Oracle client protocol through ivorysql.port.

  • Pgpool-II parses SQL to decide where to route it. Test application-specific Oracle syntax and route statements that must see the newest data to the primary.

  • Asynchronous streaming replication can return stale data from a standby. Configure delay thresholds or synchronous replication according to the application’s consistency requirements.

  • Do not expose Pgpool-II with trust authentication. Use TLS, SCRAM, restricted listen addresses, and least-privilege monitoring accounts in production.

  • Automatic failover is a separate high-availability design. Test promotion, fencing, client retry, and split-brain prevention before enabling it.

For production options, see the Pgpool-II 4.7 documentation.