Sample Header Ad - 728x90

Can pgpool handle read queries while the primary is down?

2 votes
2 answers
132 views
I am trying to configure pgpool to distribute the traffic over two Postgres clusters. One cluster has the primary node, and the other is a replica cluster with a designated primary node that follows the primary (streaming replication). The two clusters are managed by CloudNativePG. In my setup, when the primary cluster goes down, promoting the replica cluster is possible only through manual intervention (by modifying some Kubernetes resources). And accordingly, I do not want or expect pgpool to attempt promoting a replica. What I am trying to get pgpool to do is to keep handling the read-only traffic while the primary is down, and I am not sure if that is supported. This is my pgpool configuration.
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgpool
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgpool
  template:
    metadata:
      labels:
        app: pgpool
    spec:
      containers:
      - name: pgpool
        image: pgpool/pgpool
        env:
        - name: PGPOOL_PARAMS_FAILOVER_COMMAND
          value: ""

        - name: PGPOOL_PARAMS_REPLICATE_SELECT
          value: "on"

        - name: PGPOOL_PARAMS_BACKEND_CLUSTERING_MODE
          value: streaming_replication

        - name: PGPOOL_PARAMS_LOAD_BALANCE_MODE
          value: "on"

        - name: PGPOOL_PARAMS_FAILOVER_ON_BACKEND_ERROR
          value: "off"

        - name: PGPOOL_PARAMS_FAILOVER_ON_BACKEND_SHUTDOWN
          value: "off"

        - name: PGPOOL_PARAMS_BACKEND_HOSTNAME0
          value: "postgres-site1-rw"
        - name: PGPOOL_PARAMS_BACKEND_PORT0
          value: "5432"
        - name: PGPOOL_PARAMS_BACKEND_WEIGHT0
          value: "0.5"
        - name: PGPOOL_PARAMS_BACKEND_FLAG0
          value: "DISALLOW_TO_FAILOVER|ALWAYS_PRIMARY"

        - name: PGPOOL_PARAMS_BACKEND_HOSTNAME1
          value: "postgres-site2-ro"
        - name: PGPOOL_PARAMS_BACKEND_PORT1
          value: "5432"
        - name: PGPOOL_PARAMS_BACKEND_WEIGHT1
          value: "0.5"
        - name: PGPOOL_PARAMS_BACKEND_FLAG1
          value: "DISALLOW_TO_FAILOVER"

        - name: PGPOOL_PARAMS_LOG_CLIENT_MESSAGES
          value: "on"

        - name: PGPOOL_PARAMS_CLIENT_MIN_MESSAGES
          value: "DEBUG5"

        - name: PGPOOL_PARAMS_LOG_MIN_MESSAGES
          value: "DEBUG5"

        - name: PGPOOL_PARAMS_LOG_PER_NODE_STATEMENT
          value: "on"

        - name: PGPOOL_PARAMS_LOG_CONNECTIONS
          value: "on"

        - name: PGPOOL_PARAMS_LOG_DISCONNECTIONS
          value: "on"

        - name: PGPOOL_PARAMS_LOG_ERROR_VERBOSITY
          value: "VERBOSE"

        - name: PGPOOL_PARAMS_ENABLE_POOL_HBA
          value: "on"

        - name: PGPOOL_PARAMS_SSL
          value: "off"

        - name: POSTGRES_USERNAME
          valueFrom:
            secretKeyRef:
              name: postgres-superuser-secret
              key: username

        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-superuser-secret
              key: password

        - name: PGPOOL_PASSWORD_ENCRYPTION_METHOD
          value: "scram-sha-256"

        - name: PGPOOL_ENABLE_POOL_PASSWD
          value: "true"

        - name: PGPOOL_SKIP_PASSWORD_ENCRYPTION
          value: "false"
Now when I bring the primary down and try to send a simple read request, I get this error.
$ kubectl exec -it  -- psql -p 9999 -h localhost -U postgres -c "SELECT username FROM test;"
Password for user postgres:
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL:  failed to create a backend 0 connection
DETAIL:  not executing failover because failover_on_backend_error is off
command terminated with exit code 2
This makes me wonder, **can pgpool handle read queries while no primary is available?**
Asked by helmy (41 rep)
Mar 19, 2025, 02:36 PM
Last activity: Mar 20, 2025, 05:05 PM