Hello,
I have installed a Percona Postgres cluster using the following supplemental values:
backup:
volumeSpec:
size: 20G # or whatever the maximum amount of storage space required
storageclass: longhorn
pgPrimary:
volumeSpec:
size: 20G # or whatever the maximum amount of storage space required
storageclass: longhorn
replicas:
volumeSpec:
size: 20G # or whatever the maximum amount of storage space required
storageclass: longhorn
pgBouncer:
size: 2
replicas:
size: 1
Next I installed using:
helm install postgres-test percona/pg-db --version 1.3.1 --namespace development-database -f percona-postgres-test.yml
I then grabbed the username and password using:
echo $(kubectl -n development-database get secrets test-pg-db-pguser-secret -o jsonpath="{.data.username}" | base64 --decode)
echo $(kubectl -n development-database get secrets postgres-test-pg-db-pguser-secret -o jsonpath="{.data.username}" | base64 --decode)
NOTE: as an aside I noticed that if you uninstall and reinstall the cluster the operator correctly picks up the storage volume that has been used before but DOES NOT reuse the password. This leads to a situation where the database contains one password and the secret contains another.
Unfortunately, when I try to connect to the database using:
kubectl run -n development-database -i --rm --tty percona-client --image=perconalab/percona-distribution-postgresql:15.1 --restart=Never -- psql "postgres://[my-password]@postgres-test-pg-db-pgbouncer.development-database.svc.cluster.local/pgdb"
(where [my-password] the the password that came from the secret)
I get:
If you don't see a command prompt, try pressing enter.
psql: error: connection to server at "postgres-test-pg-db-pgbouncer.development-database.svc.cluster.local" (10.43.151.177), port 5432 failed: FATAL: query_wait_timeout
pod "percona-client" deleted
pod development-database/percona-client terminated (Error)
and the pgbouncer logs contains:
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:03:04.445 UTC [34] WARNING DNS lookup failed: postgres-test-pg-db: result=0
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:03:04.445 UTC [34] LOG S-0x560348b3c820: pgdb/pgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)
I then tried to connect from the database server itself using:
kubectl exec -it postgres-test-pg-db-747cb8c964-g47cz bash
PGPASSWORD=[my-password] psql -h postgres-test-pg-db-pgbouncer.development-database.svc.cluster.local -U pgadmin postgres
I get:
psql: error: connection to server at "postgres-test-pg-db-pgbouncer.development-database.svc.cluster.local" (10.43.151.177), port 5432 failed: FATAL: query_wait_timeout
with the pgbouncer logs showing:
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.430 UTC [34] LOG C-0x560348b358c0: postgres/(nouser)@10.42.0.55:35402 closing because: server login has been failing, try again later (server_login_retry) (age=0s)
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.430 UTC [34] WARNING C-0x560348b358c0: postgres/(nouser)@10.42.0.55:35402 pooler error: server login has been failing, try again later (server_login_retry)
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.430 UTC [34] LOG C-0x560348b358c0: postgres/(nouser)@10.42.0.55:35402 closing because: server login has been failing, try again later (server_login_retry) (age=0s)
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.430 UTC [34] WARNING C-0x560348b358c0: postgres/(nouser)@10.42.0.55:35402 pooler error: server login has been failing, try again later (server_login_retry)
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.433 UTC [34] WARNING DNS lookup failed: postgres-test-pg-db: result=0
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.433 UTC [34] LOG S-0x560348b3c820: postgres/pgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.433 UTC [34] WARNING DNS lookup failed: postgres-test-pg-db: result=0
postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj pgbouncer 2023-01-23 12:14:23.433 UTC [34] LOG S-0x560348b3c820: postgres/pgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)
Bad login and DNS issue.
Strangely if I log into the pgbouncer pod and check to see if it can resolve the name I get:
kubectl exec -it postgres-test-pg-db-pgbouncer-645df74c5f-6pbpj bash -n development-database
nslookup postgres-test-pg-db-pgbouncer.development-database.svc.cluster.local
or
nslookup postgres-test-pg-db-pgbouncer
I get:
Server: 10.43.0.10
Address: 10.43.0.10#53
Name: postgres-test-pg-db-pgbouncer.development-database.svc.cluster.local
Address: 10.43.151.177
So… it CAN resolve the name… DNS is NOT an issue.
What’s up?? Everything is in the same zone, the same subnet and everything can see everything but I just can not connect through pgbouncer.
Also, a direct connection to the database (bypassing pgbouncer) using psql WORKS:
PGPASSWORD=[my-password] psql -h postgres-test-pg-db -U pguser postgres
So I know that the password is good, the username is good, it’s just the pgbouncer passthrough that’s wrong.
If useful I can include a copy of the pgbouncer configuration file, but I’ve not changed anything… it is how the operator made it.
Anyone have any idea what’s going on?
Next I am going to try to create a fresh cluster with a new persistent volume claim.
Thanks
Chris