id: pgpool-ii-node-health-and-replication-delay-sentinel
namespace: company.team
description: |
Inspect Pgpool-II backend PostgreSQL nodes on a schedule, verify
single primary availability, detect lagging read-replicas exceeding
byte thresholds, log clean status lines, and alert Slack on degraded
or failing cluster states.
inputs:
- id: pgpool_host
type: STRING
defaults: "localhost"
displayName: "Pgpool-II Host"
description: "Hostname or IP address of the Pgpool-II proxy instance."
- id: pgpool_port
type: INT
defaults: 9999
displayName: "Pgpool-II Port"
description: "Port where Pgpool-II accepts client connections (default 9999)."
- id: database_name
type: STRING
defaults: "postgres"
displayName: "Database Name"
description: "PostgreSQL maintenance database name to connect to."
- id: max_replication_delay_bytes
type: INT
defaults: 10485760
displayName: "Max Replication Delay (Bytes)"
description: "Maximum tolerated replication lag for standbys before alerting
(default 10 MB = 10485760 bytes)."
triggers:
- id: pgpool_audit_schedule
type: io.kestra.plugin.core.trigger.Schedule
description: "Audit Pgpool-II backend node health and replication delay periodically."
cron: "*/15 * * * *"
disabled: true
tasks:
- id: probe_pool_nodes
type: io.kestra.plugin.jdbc.postgresql.Query
description: "Execute SHOW POOL_NODES administrative command against Pgpool-II
to retrieve real-time state for each backend."
url: "jdbc:postgresql://{{ inputs.pgpool_host }}:{{ inputs.pgpool_port }}/{{
inputs.database_name }}"
username: "{{ secret('PGPOOL_USERNAME') }}"
password: "{{ secret('PGPOOL_PASSWORD') }}"
fetchType: FETCH
sql: "SHOW POOL_NODES;"
allowFailure: true
- id: classify_pool_status
type: io.kestra.plugin.core.debug.Return
description: "Classify UNREACHABLE if the query failed, CRITICAL if any backend
is down or primary quorum is broken, DEGRADED if any standby replication
lag exceeds threshold, otherwise HEALTHY."
format: >-
{{ (outputs.probe_pool_nodes.rows is defined and
outputs.probe_pool_nodes.rows is not null) ?
((((outputs.probe_pool_nodes.rows | jq('[.[] | select(.role == "primary" and .status == "up")] | length') | first | default(0)) != 1) or
(((outputs.probe_pool_nodes.rows | jq('[.[] | select(.status != "up")] | length') | first | default(0)) > 0)) ? 'CRITICAL' :
(((outputs.probe_pool_nodes.rows | jq('[.[] | select(.role == "standby" and ((.replication_delay // 0) | tonumber) > ' ~ inputs.max_replication_delay_bytes ~ ')] | length') | first | default(0)) > 0 ? 'DEGRADED' : 'HEALTHY')) :
'UNREACHABLE' }}
- id: route_by_pool_status
type: io.kestra.plugin.core.flow.Switch
description: "Route execution based on health classification: log confirmations,
alert on replication degradation, or dispatch emergency pages on node
failure."
value: "{{ outputs.classify_pool_status.value }}"
cases:
"HEALTHY":
- id: log_healthy
type: io.kestra.plugin.core.log.Log
message: "Pgpool-II backend cluster is HEALTHY on {{ inputs.pgpool_host }}:{{
inputs.pgpool_port }}. Total nodes: {{ outputs.probe_pool_nodes.rows
| length }}, 1 active primary, all standbys synchronized within {{
inputs.max_replication_delay_bytes }} bytes."
"DEGRADED":
- id: alert_degraded
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"text": "Pgpool-II Sentinel DEGRADED: PostgreSQL standby replica(s) exceed maximum replication lag threshold ({{ inputs.max_replication_delay_bytes }} bytes). Lagging node(s): {{ outputs.probe_pool_nodes.rows | jq('[.[] | select(.role == \"standby\" and ((.replication_delay // 0) | tonumber) > ' ~ inputs.max_replication_delay_bytes ~ ') | \"Node \" + (.node_id | tostring) + \" (\" + .hostname + \":\" + (.port | tostring) + \", delay: \" + (.replication_delay | tostring) + \" bytes)\"] | join(\", \")') | first }}. Execution {{ execution.id }}."
}
"CRITICAL":
- id: alert_critical
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"text": "Pgpool-II Sentinel CRITICAL: High-availability backend pool failure detected on {{ inputs.pgpool_host }}:{{ inputs.pgpool_port }}. Active healthy primaries: {{ outputs.probe_pool_nodes.rows | jq('[.[] | select(.role == \"primary\" and .status == \"up\")] | length') | first | default(0) }} (expected 1). Degraded/Down node(s): {{ outputs.probe_pool_nodes.rows | jq('[.[] | select(.status != \"up\") | \"Node \" + (.node_id | tostring) + \" (\" + .hostname + \", status: \" + .status + \")\"] | join(\", \")') | first | default('None down, primary missing or duplicated') }}. Execution {{ execution.id }}."
}
"UNREACHABLE":
- id: alert_unreachable
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"text": "Pgpool-II Sentinel UNREACHABLE: Connection to Pgpool-II proxy failed at {{ inputs.pgpool_host }}:{{ inputs.pgpool_port }} (timeout, bad credentials, or service down). Backend node health cannot be determined. Execution {{ execution.id }}."
}
defaults:
- id: log_unclassified
type: io.kestra.plugin.core.log.Log
message: "Completed Pgpool-II health evaluation with status: {{
outputs.classify_pool_status.value }}"
- id: log_audit_status
type: io.kestra.plugin.core.log.Log
description: "Post-audit status line recorded on every run regardless of branch,
for continuous cluster health monitoring in the execution history."
message: "Pgpool-II audit: host={{ inputs.pgpool_host }}:{{ inputs.pgpool_port
}}, db={{ inputs.database_name }}, max_lag_bytes={{
inputs.max_replication_delay_bytes }}, status={{
outputs.classify_pool_status.value }}, execution={{ execution.id }}."
errors:
- id: alert_flow_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: "Alert Slack when the Pgpool-II sentinel flow itself fails,
preventing silent monitoring blackouts."
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"text": "Pgpool-II sentinel flow {{ flow.id }} FAILED unexpectedly during execution {{ execution.id }}. Cluster health monitoring is interrupted; inspect task logs and verify Pgpool-II proxy availability."
}
outputs:
- id: status
type: STRING
value: "{{ outputs.classify_pool_status.value }}"
- id: total_nodes
type: INT
value: "{{ (outputs.probe_pool_nodes.rows is defined and
outputs.probe_pool_nodes.rows is not null) ?
(outputs.probe_pool_nodes.rows | length) : 0 }}"