Schedule icon
Query icon
Return icon
Switch icon
Log icon
SlackIncomingWebhook icon

Pgpool-II Backend Node Health and Replication Delay Sentinel

Audit Pgpool-II PostgreSQL backend node health and replication delay on a schedule with Kestra. Detect down nodes, loss of primary, and alert Slack.

Categories
Infrastructure

PostgreSQL high-availability architectures relying on Pgpool-II for connection pooling and read/write splitting can degrade silently when backend nodes disconnect, fail over improperly, or develop severe replication lag. This blueprint connects directly to Pgpool-II on a schedule, queries real-time node operational telemetry via the native SHOW POOL_NODES administrative command, verifies that exactly one primary node is active, evaluates standby replication delay against SLA thresholds, and alerts Slack on degraded or failed cluster conditions.

How it works

  1. probe_pool_nodes (io.kestra.plugin.jdbc.postgresql.Query) connects to Pgpool-II on port 9999 using PostgreSQL JDBC credentials and executes SHOW POOL_NODES;. It captures the full table of registered backend PostgreSQL nodes, including their role, status, and byte replication delay. allowFailure: true ensures that proxy connection timeouts or unreachable errors are caught and forwarded to classification rather than crashing the flow prematurely.
  2. classify_pool_status (io.kestra.plugin.core.debug.Return) evaluates the query result set using Pebble and jq filters. If the proxy was unreachable, it outputs UNREACHABLE. If any backend node is not in the up state, or if the number of active primaries is not exactly one, it assigns CRITICAL. If all nodes are up but any standby exceeds max_replication_delay_bytes, it assigns DEGRADED. When all nodes are nominal and synchronized, it outputs HEALTHY.
  3. route_by_pool_status (io.kestra.plugin.core.flow.Switch) branches on the evaluated status string: HEALTHY writes a confirmation log, DEGRADED sends a Slack notification with the specific lagging node IDs and delay metrics, CRITICAL dispatches an urgent Slack alert highlighting offline nodes or primary election issues, and UNREACHABLE alerts the team that the Pgpool-II proxy itself cannot be contacted.
  4. log_audit_status (io.kestra.plugin.core.log.Log) writes a persistent telemetry summary line on every execution regardless of the branch taken, ensuring complete audit traceability across executions.
  5. The errors block intercepts unhandled runtime exceptions and alerts Slack so monitoring never fails silently.
  6. The pgpool_audit_schedule trigger runs every 15 minutes, provided with disabled: true for safe onboarding.

What you get

  • Automated, agentless monitoring of PostgreSQL backend nodes managed by Pgpool-II without installing external exporters.
  • Early warning alerting when read-replicas fall behind primary write streams, preventing stale read queries in split-read topologies.
  • Immediate detection of split-brain scenarios (zero or multiple primary nodes reported by Pgpool-II).
  • Clear Slack incident messages containing node IDs, hostnames, ports, and observed replication lag values.
  • Continuous audit logging in Kestra execution history for compliance and reliability trends.

Who it's for

  • Platform Engineers and Database Reliability Engineers (DBREs) managing clustered PostgreSQL fleets with Pgpool-II.
  • Site Reliability Engineers (SREs) who need proactive alerting before database connection pools exhaust or replication delays impact users.
  • DevOps teams maintaining self-hosted or containerized PostgreSQL high-availability deployments.

Why orchestrate this with Kestra

Monitoring database proxies using disparate bash scripts or ad-hoc cron jobs frequently leads to silent monitoring failures, lack of execution history, and unmanaged database credentials. Kestra provides a declarative, version-controlled blueprint with encrypted secret injection, built-in retry capabilities, visual execution tracking, and seamless branching into automated remediation workflows (such as invoking PCP administrative commands to re-attach recovered nodes).

Prerequisites

  • A running Pgpool-II instance accessible over the network (or locally via Docker container bitnami/pgpool:latest or pgpool/pgpool:latest).
  • A database user permitted to connect to Pgpool-II and run administrative utility queries (SHOW POOL_NODES).
  • An incoming Slack webhook URL for dispatching incident notifications.

Secrets

  • PGPOOL_USERNAME: Database username used to connect to the Pgpool-II proxy (required).
  • PGPOOL_PASSWORD: Database password for the connecting user (required).
  • SLACK_WEBHOOK_URL: Slack Incoming Webhook URL used for degraded, critical, unreachable, and flow failure notifications (required).
  • OSS note: For open-source Kestra instances configuring secrets via environment variables, supply base64-encoded values as SECRET_PGPOOL_USERNAME, SECRET_PGPOOL_PASSWORD, and SECRET_SLACK_WEBHOOK_URL.

Quick start

  1. Configure the PGPOOL_USERNAME, PGPOOL_PASSWORD, and SLACK_WEBHOOK_URL secrets in your Kestra namespace.
  2. Import this blueprint and set pgpool_host and pgpool_port to point to your Pgpool-II proxy instance.
  3. Click Execute to run a manual verification check against your pool nodes.
  4. Review the task logs and verify that status outputs HEALTHY.
  5. Enable pgpool_audit_schedule to turn on automated recurring 15-minute sentinels.

How to extend

  • Add automated recovery tasks: In the CRITICAL branch, integrate a task invoking pcp_attach_node via CLI or REST to automatically attempt node re-attachment after transient network interruptions.
  • Add multi-channel alerting: Extend notifications to PagerDuty or Opsgenie in the CRITICAL branch for high-priority on-call paging.
  • Export metrics to cold storage: Append a DuckDB or PostgreSQL insertion task to persist historical replication delay trends for long-term capacity planning.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.