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
Last activity: Mar 20, 2025, 05:05 PM