Skip to content
Guides
Pgpool-II

Configure Pgpool-II for PostgreSQL

Pgpool-II runs between your application and a linked PostgreSQL add-on. It provides local connection pooling and can distribute read queries when Clever Cloud has configured PostgreSQL streaming replication for your organisation.

Pgpool-II is available in every runtime except Docker, where processes and services are managed by the container image.

Enable Pgpool-II

Link a PostgreSQL add-on to the application, then enable Pgpool-II:

clever env set CC_ENABLE_PGPOOL true

The platform starts one Pgpool-II process on each application instance and injects these variables:

  • CC_PGPOOL_SOCKET_PATH is the local Unix socket directory your application connects to
  • PGHOST, PGDATABASE and PGUSER let PostgreSQL clients use Pgpool-II without additional connection arguments

Your application must keep using the linked add-on credentials from POSTGRESQL_ADDON_USER, POSTGRESQL_ADDON_PASSWORD and POSTGRESQL_ADDON_DB. Use port 5432 when a client requires an explicit port, because Pgpool-II listens on the standard PostgreSQL port through its local socket.

Connect your application

PHP with PDO

Use the socket directory as the host in a PDO PostgreSQL DSN:

<?php

$dsn = sprintf(
    'pgsql:host=%s;port=5432;dbname=%s',
    getenv('CC_PGPOOL_SOCKET_PATH'),
    getenv('POSTGRESQL_ADDON_DB')
);

$connection = new PDO(
    $dsn,
    getenv('POSTGRESQL_ADDON_USER'),
    getenv('POSTGRESQL_ADDON_PASSWORD'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

Node.js with node-postgres

Use the socket directory as host with node-postgres:

import pg from "pg";

const pool = new pg.Pool({
  host: process.env.CC_PGPOOL_SOCKET_PATH,
  port: 5432,
  user: process.env.POSTGRESQL_ADDON_USER,
  password: process.env.POSTGRESQL_ADDON_PASSWORD,
  database: process.env.POSTGRESQL_ADDON_DB,
});

Size the connection pool

CC_PGPOOL_NUM_INIT_CHILDREN controls the number of concurrent client sessions accepted by each Pgpool-II process and defaults to 16. CC_PGPOOL_MAX_POOL controls how many backend connections each child can cache for different user and database pairs and defaults to 1.

During a rolling deployment, old and new instances can run simultaneously. The maximum number of client sessions can therefore temporarily reach:

2 × maximum running instances × CC_PGPOOL_NUM_INIT_CHILDREN

Keep this result below the PostgreSQL plan’s connection limit and reserve capacity for administration or other clients. See the environment variables reference for connection lifetime, logging, health check and query cache settings.

Configure read replicas

Pgpool-II can distribute read queries only after Clever Cloud has configured PostgreSQL streaming replication. Contact Clever Cloud Support or Sales to discuss this setup.

Once replication is available, define CC_PGPOOL_FOLLOWERS as a JSON array containing each follower’s direct hostname, direct port and weight:

[
  {
    "hostname": "FOLLOWER_DIRECT_HOST",
    "port": "FOLLOWER_DIRECT_PORT",
    "weight": "1"
  }
]

The leader’s weight is configured with CC_PGPOOL_LEADER_WEIGHT. Higher follower weights direct a larger share of eligible read queries to followers. The Ruby Deployer uses the linked leader’s direct address when direct variables are available and otherwise uses its standard add-on address.

Inspect Pgpool-II

Open an SSH session to an application instance and start psql:

clever ssh
psql

Pgpool-II supports administrative SQL commands such as:

SHOW POOL_NODES;
SHOW POOL_PROCESSES;
SHOW POOL_POOLS;
SHOW POOL_BACKEND_STATS;

The local PCP commands are preconfigured through /home/bas/.pcppass. For example, use the local socket to inspect status or attach and detach a configured follower:

pcp_pool_status -h /tmp -U pcp -w
pcp_detach_node -h /tmp -U pcp -w -n 1
pcp_attach_node -h /tmp -U pcp -w -n 1

Detaching the leader or an unreplicated backend interrupts database access. Only manage nodes that are already part of a supported replication setup.

Learn more

Last updated on
OutlineProxySQL