Apps can't connect to database - DNS lookup issue

PostgreSQL Percona Operator for PostgreSQL
1

Description:

We had dns failure which was restored and now apps can’t connect to postgres dbs. These are the logs

2023-08-13 18:26:16.593 UTC [8] WARNING C-0x5646eb355bf0: (nodb)/(nouser)@10.42.125.222:59178 pooler error: SSL required
2023-08-13 18:26:18.222 UTC [8] LOG C-0x5646eb355bf0: netbox/(nouser)@10.42.115.196:35668 closing because: server login has been failing, try again later (server_login_retry) (age=0s)
2023-08-13 18:26:18.222 UTC [8] WARNING C-0x5646eb355bf0: netbox/(nouser)@10.42.115.196:35668 pooler error: server login has been failing, try again later (server_login_retry)
2023-08-13 18:26:26.588 UTC [8] LOG C-0x5646eb355bf0: (nodb)/(nouser)@10.42.125.222:49462 closing because: SSL required (age=0s)
2023-08-13 18:26:26.588 UTC [8] WARNING C-0x5646eb355bf0: (nodb)/(nouser)@10.42.125.222:49462 pooler error: SSL required
2023-08-13 18:26:32.994 UTC [8] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us

Can I restart the cluster somehow to reset the login certs? Everything worked fine, but now pgbouncer can’t authenticate logins.

Version:

2.2.0

Logs:

2023-08-13 18:26:16.593 UTC [8] WARNING C-0x5646eb355bf0: (nodb)/(nouser)@10.42.125.222:59178 pooler error: SSL required
2023-08-13 18:26:18.222 UTC [8] LOG C-0x5646eb355bf0: netbox/(nouser)@10.42.115.196:35668 closing because: server login has been failing, try again later (server_login_retry) (age=0s)
2023-08-13 18:26:18.222 UTC [8] WARNING C-0x5646eb355bf0: netbox/(nouser)@10.42.115.196:35668 pooler error: server login has been failing, try again later (server_login_retry)
2023-08-13 18:26:26.588 UTC [8] LOG C-0x5646eb355bf0: (nodb)/(nouser)@10.42.125.222:49462 closing because: SSL required (age=0s)
2023-08-13 18:26:26.588 UTC [8] WARNING C-0x5646eb355bf0: (nodb)/(nouser)@10.42.125.222:49462 pooler error: SSL required
2023-08-13 18:26:32.994 UTC [8] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us

There is also this in logs

2023-08-13 18:32:58.268 UTC [8] WARNING DNS lookup failed: pg-cluster-pg-db-primary: result=0
2023-08-13 18:32:58.268 UTC [8] LOG S-0x5646eb366d60: netbox/_crunchypgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)

but DNS works in the cluster for other pods and applications

2

This is the exact issue I have. FQDM in pgbouncer can be changed for postgres v1, but not sure how to change it in v2? Please help!

3

dns gives response only for FQDM

bash-4.4$ dig pg-cluster-pg-db-pgbouncer

; <<>> DiG 9.11.36-RedHat-9.11.36-8.el8 <<>> pg-cluster-pg-db-pgbouncer
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: SERVFAIL, id: 40499
;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
; COOKIE: a12e6964752a4752 (echoed)
;; QUESTION SECTION:
;pg-cluster-pg-db-pgbouncer.    IN      A

;; Query time: 61 msec
;; SERVER: 10.43.0.10#53(10.43.0.10)
;; WHEN: Mon Aug 14 07:34:05 UTC 2023
;; MSG SIZE  rcvd: 67

bash-4.4$ dig pg-cluster-pg-db-pgbouncer.percona.svc.cluster.local

; <<>> DiG 9.11.36-RedHat-9.11.36-8.el8 <<>> pg-cluster-pg-db-pgbouncer.percona.svc.cluster.local
;; global options: +cmd
;; Got answer:
;; WARNING: .local is reserved for Multicast DNS
;; You are currently testing what happens when an mDNS query is leaked to DNS
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 14358
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
; COOKIE: 7a3ec8287a638307 (echoed)
;; QUESTION SECTION:
;pg-cluster-pg-db-pgbouncer.percona.svc.cluster.local. IN A

;; ANSWER SECTION:
pg-cluster-pg-db-pgbouncer.percona.svc.cluster.local. 30 IN A 10.43.109.10

;; Query time: 1 msec
;; SERVER: 10.43.0.10#53(10.43.0.10)
;; WHEN: Mon Aug 14 07:34:21 UTC 2023
;; MSG SIZE  rcvd: 161

maybe has something to do with this code

(https://github.com/percona/percona-postgresql-operator/blob/main/internal/naming/dns.go)
4

If somebody needs this, I solved it with rewrite rule mapping in coredns, but it’s not the real solution for this issue

(https://coredns.io/2017/05/08/custom-dns-entries-for-kubernetes/)