pgpool_recovery

Overview

Version

Install

You can install pgpool directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pgpool;          # Install for current active PG version

pig ext install -y pgpool -v 18  # PG 18
pig ext install -y pgpool -v 17  # PG 17
pig ext install -y pgpool -v 16  # PG 16
pig ext install -y pgpool -v 15  # PG 15
pig ext install -y pgpool -v 14  # PG 14

dnf install -y pgpool-II-pg18-extensions       # PG 18
dnf install -y pgpool-II-pg17-extensions       # PG 17
dnf install -y pgpool-II-pg16-extensions       # PG 16
dnf install -y pgpool-II-pg15-extensions       # PG 15
dnf install -y pgpool-II-pg14-extensions       # PG 14

apt install -y postgresql-18-pgpool2   # PG 18
apt install -y postgresql-17-pgpool2   # PG 17
apt install -y postgresql-16-pgpool2   # PG 16
apt install -y postgresql-15-pgpool2   # PG 15
apt install -y postgresql-14-pgpool2   # PG 14

Create Extension:

CREATE EXTENSION pgpool_recovery;

Usage

pgpool_recovery: recovery functions for pgpool-II

The pgpool_recovery extension provides recovery-related functions used by Pgpool-II for online recovery of backend PostgreSQL nodes.

Functions

-- Trigger online recovery of a backend node
-- Executes the recovery script on the primary node
SELECT pgpool_recovery(
    'recovery_1st_stage_script',   -- script name in $PGDATA
    'target_hostname',             -- hostname of node to recover
    'target_pgdata',               -- data directory on target
    'target_port'                  -- port number on target
);

-- Second stage recovery (optional, for streaming replication)
SELECT pgpool_remote_start(
    'target_hostname',             -- hostname of recovered node
    'target_pgdata'                -- data directory on target
);

-- Check if the target node is ready
SELECT pgpool_pgctl('status', 'target_pgdata');

How It Works

1. Pgpool-II calls pgpool_recovery() on the primary node, which executes a user-defined shell script to perform base backup and setup
2. The recovery script copies data to the target node and configures replication
3. pgpool_remote_start() starts the recovered PostgreSQL instance
4. Pgpool-II attaches the recovered node back into the pool

Recovery Scripts

The recovery script (e.g., recovery_1st_stage) must be placed in the primary node’s $PGDATA directory. It typically performs:

• pg_basebackup to copy data to the target
• Configuration of primary_conninfo for streaming replication
• Creation of standby.signal on the target

The extension must be installed on all PostgreSQL backend nodes managed by Pgpool-II.