[Healthchecks] NLB health check

[levkk]

Description

Add an HTTP endpoint that runs SELECT 1 or similar query on connection pools. This will tell NLBs that the PgDog instance is healthy. At least one target must succeed and health check fails iff all targets fail.

Implementation

Health checks should run async and the endpoint only:

• Responds with HTTP 200 if the health checks are recent and successful
• Doesn't actually run the health checks themselves. NLBs can be aggressive at calling this endpoint so it should be cheap to run.

We already run health checks regularly on all connection pools. This is controlled with idle_healthcheck_interval. I think we should just modify Pool to store last healthcheck timestamp and use that for the healthcheck endpoint.

[albert-toro]

Hi! I just bumped into this issue/feature request today. What were the odds? 😂

In my project, I'm using HAProxy 3.2 as my load balancer. It comes with an integrated PG-SQL check that sends a PostgreSQL StartupMessage and waits for either an Authentication request or an ErrorResponse message.

In my previous setup, I was using PgBouncer, and this check worked. I replaced PgBouncer with PgDog, and the same check is throwing the following error:

[WARNING] (1) : Health check for server pgdog_backend/pgdog-2 failed, reason: Layer7 invalid response, info: "FATAL", check duration: 1ms, status: 0/2 DOWN.

Could it be due to an incompatibility? Or is it because of the way PgDog is currently designed?

As a workaround, I'm using HTTP checks that work as expected, though ideally, PG-SQL checks would be preferable.

For further information, I'm using the built-in pgsql driver!

[levkk]

Could you either:

1. Enable trace logs in PgDog and attach their output around the time of the health check. It will show which messages PgDog sent to HAProxy. You can enable them with an env variable: RUST_LOG=trace. Don't enable in prod, this outputs a lot of logs.
2. Send over your HAProxy config so I can try to repro on my end.

Cheers!

[albert-toro]

Hi again! This is my current config-map for HAProxy (I'm running it on Kubernetes)

# HAProxy Configuration ConfigMap
resource "kubernetes_config_map_v1" "haproxy_config" {
  metadata {
    name      = "haproxy-config"
    namespace = var.pgdog_namespace
  }
  data = {
    "haproxy.cfg" = <<-EOT
      global
        log stdout format raw daemon warning

      # DNS resolver for Kubernetes cluster.local domain
      resolvers kubernetes
        nameserver kube-dns kube-dns.kube-system.svc.cluster.local:53
        # nameserver kube-dns-ipv6 kube-dns.kube-system.svc.cluster.local:53
        resolve_retries 3
        timeout retry 1s
        hold valid 30s
        hold obsolete 5s

      defaults
        log     global
        log     stderr format raw daemon err
        mode    tcp
        option  dontlognull
        option  tcplog
        option  log-health-checks
        # timeout connect 5000         # 5 seconds for health check connections - disabled due to high load on the TCP layer
        timeout client  3600000      # 1 hour
        timeout server  3600000      # 1 hour
        timeout check   2000         # 2 seconds for health check timeout
        timeout queue   5000         # 5 seconds for queue timeout
        retries 3

      listen stats
        bind *:8405
        mode http
        stats enable
        stats hide-version
        stats uri /stats
        stats refresh 10s

      frontend prometheus
        mode http
        bind *:8404
        http-request use-service prometheus-exporter if { path /metrics }

      frontend pgdog_frontend
        mode tcp
        bind *:${var.pgdog_port}
        default_backend pgdog_backend

      backend pgdog_backend
        balance leastconn
        # Basic TCP connectivity -> Disabled due to high load on the TCP layer
        # option tcp-check
        # tcp-check connect
        # PostgreSQL-specific health check for pgDog
        # option pgsql-check user ${var.username}
        option redispatch
        option log-health-checks
        option httpchk OPTIONS /metrics
        http-check expect status 200
        default-server inter 5s fall 3 rise 2 check port ${var.pgdog_metrics_port} weight 100
        # Use Kubernetes DNS resolution for StatefulSet pods - handles IP changes automatically
        server pgdog-0 pgdog-0.pgdog-headless.${var.pgdog_namespace}.svc.cluster.local:${var.pgdog_port} init-addr last,none check resolvers kubernetes
        server pgdog-1 pgdog-1.pgdog-headless.${var.pgdog_namespace}.svc.cluster.local:${var.pgdog_port} init-addr last,none check resolvers kubernetes
        server pgdog-2 pgdog-2.pgdog-headless.${var.pgdog_namespace}.svc.cluster.local:${var.pgdog_port} init-addr last,none check resolvers kubernetes
    EOT
  }
}

[levkk]

We added an HTTP health check here: https://docs.pgdog.dev/features/load-balancer/healthchecks/#load-balancer-health-check

Feel free to re-open if something else is needed. I haven't had the time to check why HAProxy pg-sql check isn't working. If you have a minute, could you paste the exact error it's receiving (it should be in pgdog logs, if running in debug mode).