Skip to main content

Configuration Reference

Environment variables for configuring PgPool-II.

Required​

VariableDescriptionExample
PGPOOL_BACKENDSComma-separated list of PostgreSQL backends"postgres-1:5432,postgres-2:5432,postgres-3:5432"

Backend Configuration​

VariableDefaultDescription
PGPOOL_BACKEND_WEIGHTS"1,1,1,..."Comma-separated weights for load balancing
PGPOOL_BACKEND_FLAGS"ALLOW_TO_FAILOVER,..."Flags for each backend: ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
PGPOOL_BACKEND_USERpostgresUser for backend connections
PGPOOL_BACKEND_PASSWORD(required)Password for backend connections

Backend Flags​

  • ALLOW_TO_FAILOVER - Allow automatic failover to this backend
  • DISALLOW_TO_FAILOVER - Never failover to this backend (useful for readonly replicas)

Example:

environment:
  PGPOOL_BACKEND_FLAGS: "ALLOW_TO_FAILOVER,ALLOW_TO_FAILOVER,DISALLOW_TO_FAILOVER"

Connection Settings​

VariableDefaultDescription
PGPOOL_PORT5432PgPool-II listening port
PGPOOL_PCP_PORT9898PCP (PgPool Control Protocol) port
PGPOOL_LISTEN_ADDRESSES*Listen addresses

Pool Configuration​

VariableDefaultDescription
PGPOOL_NUM_INIT_CHILDREN32Number of pre-forked child processes
PGPOOL_MAX_POOL4Maximum connections per child process
PGPOOL_CHILD_LIFE_TIME300Child process lifetime in seconds
PGPOOL_CONNECTION_LIFE_TIME0Connection lifetime in seconds (0 = unlimited)
PGPOOL_CHILD_MAX_CONNECTIONS0Maximum connections per child (0 = unlimited)

Load Balancing​

VariableDefaultDescription
PGPOOL_LOAD_BALANCE_MODEonEnable/disable load balancing
PGPOOL_IGNORE_LEADING_WHITE_SPACEonIgnore leading whitespace in SQL

Load Balance Weights​

environment:
  # primary gets 3x more traffic than each replica
  PGPOOL_BACKEND_WEIGHTS: "3,1,1"

Health Check​

VariableDefaultDescription
PGPOOL_HEALTH_CHECK_TIMEOUT5Health check timeout in seconds
PGPOOL_HEALTH_CHECK_PERIOD10Health check interval in seconds
PGPOOL_HEALTH_CHECK_USERpostgresUser for health checks
PGPOOL_HEALTH_CHECK_PASSWORD(from PGPOOL_BACKEND_PASSWORD)Password for health checks
PGPOOL_HEALTH_CHECK_DATABASEpostgresDatabase for health checks
PGPOOL_HEALTH_CHECK_MAX_RETRIES0Max retry attempts on failure
PGPOOL_HEALTH_CHECK_RETRY_DELAY1Seconds to wait before retry

Streaming Replication Check​

VariableDefaultDescription
PGPOOL_SR_CHECK_PERIOD10Streaming replication check interval in seconds
PGPOOL_SR_CHECK_USER(from PGPOOL_BACKEND_USER)User for replication check
PGPOOL_SR_CHECK_PASSWORD(from PGPOOL_BACKEND_PASSWORD)Password for replication check
PGPOOL_SR_CHECK_DATABASEpostgresDatabase for replication check
PGPOOL_DELAY_THRESHOLD0Max replication delay in bytes (0 = disabled)
PGPOOL_DELAY_THRESHOLD_BY_TIME0Max replication delay in seconds
PGPOOL_PREFER_LOWER_DELAY_STANDBYoffPrefer standby with lowest delay

Delay Threshold Example​

environment:
  # Only send reads to replicas if lag is less than 1MB
  PGPOOL_DELAY_THRESHOLD: "1048576"
  PGPOOL_PREFER_LOWER_DELAY_STANDBY: "on"

Primary Node Detection​

VariableDefaultDescription
PGPOOL_SEARCH_PRIMARY_NODE_TIMEOUT10Timeout in seconds to find primary node

Failover Configuration​

VariableDefaultDescription
PGPOOL_FAILOVER_COMMAND""Command to execute on failover

Failover Command Example​

environment:
  PGPOOL_FAILOVER_COMMAND: |
    /usr/local/bin/failover.sh %d %h %P %m %M "%H" %n

See PgPool-II documentation for parameter details.

Master/Slave Mode​

VariableDefaultDescription
PGPOOL_MASTER_SLAVE_MODEonEnable master/slave mode
PGPOOL_MASTER_SLAVE_SUB_MODEstreamSub mode: stream or slony
environment:
  PGPOOL_MASTER_SLAVE_MODE: "on"
  PGPOOL_MASTER_SLAVE_SUB_MODE: "stream"

Query Routing & Validation​

VariableDefaultDescription
PGPOOL_ENABLE_HINT_QUERYoffEnable hint-based query routing
PGPOOL_VALIDATE_QUERYoffValidate SQL before sending to backends
PGPOOL_REPLICATION_CHECKoffEnable replication check
PGPOOL_AUTO_DB_REMOVEoffAuto remove temporary DB connections
PGPOOL_AUTOICACHE_REMOVEoffAuto remove idle cache connections

Using Hints​

Enable load balancing with per-query hints:

environment:
  PGPOOL_ENABLE_HINT_QUERY: "on"

Then use SQL comments to direct queries:

-- Direct to primary
/*INSERT*/ INSERT INTO users VALUES (1, 'test');

-- Direct to replica
/*READONLY*/ SELECT * FROM users;

Authentication​

VariableDefaultDescription
PGPOOL_ENABLE_POOL_HBAoffEnable pool_hba.conf authentication
PGPOOL_POOL_PASSWDpool_passwdPassword file name

Logging​

VariableDefaultDescription
PGPOOL_LOG_MIN_MESSAGESwarningMinimum log level
PGPOOL_LOG_HOSTNAMEonLog hostname in connections
PGPOOL_DEBUGfalseEnable debug logging

Log Levels​

  • debug5, debug4, debug3, debug2, debug1
  • info, notice, warning, error, log
  • fatal, panic

PCP (PgPool Control Protocol)​

VariableDefaultDescription
PGPOOL_PCP_TIMEOUT10PCP operation timeout in seconds
PGPOOL_PCP_NUM_CHILDREN32Number of PCP child processes

Watchdog​

VariableDefaultDescription
PGPOOL_ENABLE_WATCHDOGoffEnable watchdog for HA
PGPOOL_WD_HOSTNAME(none)Partner PgPool-II hostname
PGPOOL_WD_PORT9000Watchdog port
PGPOOL_WD_AUTHENTICATION(none)Watchdog authentication method
PGPOOL_DELegate_IP(none)Virtual IP for failover
PGPOOL_WD_LIFECHECK_METHODheartbeatLifecheck method: heartbeat or query
PGPOOL_WD_INTERVAL10Lifecheck interval in seconds
PGPOOL_WD_PRIORITY1Watchdog priority (higher = preferred)
PGPOOL_WD_FAILOVER_TIMEOUT15Failover timeout in seconds

Watchdog Example​

environment:
  PGPOOL_ENABLE_WATCHDOG: "on"
  PGPOOL_WD_HOSTNAME: "pgpool-2.erp-db-postgresql.svc.cluster.local"
  PGPOOL_WD_PORT: "9000"
  PGPOOL_DELegate_IP: "10.0.0.100"
  PGPOOL_WD_LIFECHECK_METHOD: "heartbeat"
  PGPOOL_WD_INTERVAL: "10"

Patroni Integration​

VariableDefaultDescription
PGPOOL_PATRONI_ENDPOINTS""Comma-separated Patroni REST API endpoints
PGPOOL_PATRONI_TIMEOUT10Timeout for Patroni API queries in seconds

Patroni Example​

environment:
  PGPOOL_BACKENDS: "postgresql1:5432,postgresql2:5432,postgresql3:5432"
  PGPOOL_PATRONI_ENDPOINTS: "http://postgresql1:8008,http://postgresql2:8008,http://postgresql3:8008"

See Patroni Integration for detailed setup.

SSL/TLS Configuration​

VariableDefaultDescription
PGPOOL_SSLoffEnable SSL
PGPOOL_SSL_KEY(none)Path to SSL key
PGPOOL_SSL_CERT(none)Path to SSL certificate
PGPOOL_SSL_SECURITY_MODEpostgresqlSecurity mode: postgresql or transfer

SSL Example​

environment:
  PGPOOL_SSL: "on"
  PGPOOL_SSL_KEY: "/etc/ssl/private/server.key"
  PGPOOL_SSL_CERT: "/etc/ssl/certs/server.crt"

Memory Cache​

VariableDefaultDescription
PGPOOL_MEMORY_CACHE_MODEoffEnable memory cache mode
PGPOOL_MEMQCACHE_CACHE_BLOCKS8192Number of cache blocks
PGPOOL_MEMQCACHE_BLOCK_SIZE1048576Block size in bytes
PGPOOL_MEMQCACHE_MAXCACHE50Maximum cached queries
PGPOOL_MEMQCACHE_EXPIRE_TIME10000Cache expiration time in seconds
PGPOOL_MEMQCACHE_TOTAL_SIZE1024Total cache size in MB

Memory Cache Example​

environment:
  PGPOOL_MEMORY_CACHE_MODE: "on"
  PGPOOL_MEMQCACHE_TOTAL_SIZE: "1024"
  PGPOOL_MEMQCACHE_CACHE_BLOCKS: "8192"

Advanced Settings​

VariableDefaultDescription
PGPOOL_CONFIG_DIR/usr/local/pgpool/etcConfiguration directory
PGPOOL_LOG_DIR/var/log/pgpoolLog directory
PGPOOL_RUN_DIR/var/run/pgpoolRuntime directory
PGPOOL_USERpostgresUser to run PgPool-II as

Complete Example​

services:
  pgpool:
    image: supanadit/pgpool-ii:4.6.3
    environment:
      # Backends
      PGPOOL_BACKENDS: "postgres-1:5432,postgres-2:5432,postgres-3:5432"
      PGPOOL_BACKEND_PASSWORD: "secret"
      PGPOOL_BACKEND_WEIGHTS: "1,1,1"
      PGPOOL_BACKEND_FLAGS: "ALLOW_TO_FAILOVER,ALLOW_TO_FAILOVER,DISALLOW_TO_FAILOVER"
      
      # Pool configuration
      PGPOOL_NUM_INIT_CHILDREN: "32"
      PGPOOL_MAX_POOL: "4"
      PGPOOL_CHILD_LIFE_TIME: "300"
      
      # Load balancing
      PGPOOL_LOAD_BALANCE_MODE: "on"
      PGPOOL_IGNORE_LEADING_WHITE_SPACE: "on"
      
      # Master/slave mode
      PGPOOL_MASTER_SLAVE_MODE: "on"
      PGPOOL_MASTER_SLAVE_SUB_MODE: "stream"
      
      # Health check
      PGPOOL_HEALTH_CHECK_TIMEOUT: "20"
      PGPOOL_HEALTH_CHECK_PERIOD: "10"
      
      # Replication delay check
      PGPOOL_SR_CHECK_PERIOD: "10"
      PGPOOL_DELAY_THRESHOLD: "1048576"
      PGPOOL_PREFER_LOWER_DELAY_STANDBY: "off"
      
      # Query routing (optional)
      PGPOOL_ENABLE_HINT_QUERY: "off"
      PGPOOL_VALIDATE_QUERY: "off"
      
      # Memory cache (optional)
      PGPOOL_MEMORY_CACHE_MODE: "off"
      PGPOOL_MEMQCACHE_TOTAL_SIZE: "1024"
      
      # SSL/TLS (optional)
      # PGPOOL_SSL: "on"
      # PGPOOL_SSL_KEY: "/etc/ssl/private/server.key"
      # PGPOOL_SSL_CERT: "/etc/ssl/certs/server.crt"
      
      # Authentication
      PGPOOL_ENABLE_POOL_HBA: "off"
    ports:
      - "5432:5432"
      - "9898:9898"