airflow 2.1.3 using pgbouncer for postgresql issue

Question

back ground info: recently we upgraded the airflow from 2.10.14 to 2.1.3, the pgbouncer was using customised container built from azure microsoft image (mcr.microsoft.com/azure-oss-db-tools/pgbouncer-sidecar:latest).

the customised pgbouncer stopped working, it instead connects to the main postgresql server now.

so I now try to use pgbouncer deployed by airflow 2.1.3 (helm chart 8.5.2) instead (https://artifacthub.io/packages/helm/airflow-helm/airflow/8.5.0#how-to-use-an-external-database), and have problems.

Below is the key info

in my values.yaml file, key info is like below

pgbouncer:
  enabled: true
  # listen_port does not seem to take effect into pgbouncer.ini file
#  listen_port: 5432

externalDatabase:
  type: postgres
  host: psql-hostname.postgres.database.azure.com
  port: 5432
  database: airflow
  user: username@psql-hostname
  passwordSecret: "airflow-postgres-redis-name"
  passwordSecretKey: "postgresql-password-key-name"
  properties: ""
  # properties: "?sslmode=disable"
externalRedis:
  host: redis-hostname.redis.cache.windows.net
  port: 6379
  databaseNumber: 1
  passwordSecret: "airflow-postgres-redis-name"
  passwordSecretKey: "redis-password-key-name"
  properties: ""

in my script, create below in the kubernetes cluster

kubectl create secret generic "airflow-postgres-redis-name" \
   -n ${_namespace_airflow} \
    --from-literal=postgresql-password="${my-airflow2-postgre}" \
    --from-literal=redis-password="${my-airflow2-redis}"

when i applied the values.yaml using helm upgrade, i noticed the pgbouncer.ini has below info. Notice the listen_port is 6543

$ kubectl exec -n airflow -ti airflow-pgbouncer-6f88889bf5-xtdvp -- /bin/sh
~ $ ls /home/pgbouncer/

certs          config         pgbouncer.ini  users.txt
 
~ $ cat /home/pgbouncer/pgbouncer.ini

[databases]
* = host=127.0.0.1 port=5432
[pgbouncer]
pool_mode = session
listen_port = 6543
listen_addr = *
 
~ $ cat /home/pgbouncer/users.txt
 
"username@psql-hostname" "HIDE FOR THIS NOTE"

I suspect the cause is the port being 6543 is not working, but i could not find a way to overwrite this. Please help.

or if my suspicion is wrong, below log/event might also let you help me on ideas to try

The output of kubectl describe the pod

Events:
  Type     Reason     Age                 From               Message
 
  ----     ------     ----                ----               -------
 
  Normal   Scheduled  15m                 default-scheduler  Successfully assigned airflow/airflow-pgbouncer-6f59cf4769-bx5hf to aks-nodepool1-16099970-vmss00000a
 
  Normal   Pulling    28m                 kubelet            Pulling image "ghcr.io/airflow-helm/pgbouncer:1.15.0-patch.0"
 
  Normal   Pulled     28m                 kubelet            Successfully pulled image "ghcr.io/airflow-helm/pgbouncer:1.15.0-patch.0" in 3.7505019s
 
  Normal   Created    23m (x4 over 28m)   kubelet            Created container pgbouncer
 
  Normal   Started    23m (x4 over 28m)   kubelet            Started container pgbouncer
 
  Normal   Killing    23m (x3 over 26m)   kubelet            Container pgbouncer failed liveness probe, will be restarted
 
  Normal   Pulled     23m (x3 over 26m)   kubelet            Container image "ghcr.io/airflow-helm/pgbouncer:1.15.0-patch.0" already present on machine
 
  Warning  Unhealthy  18m (x16 over 27m)  kubelet            Liveness probe failed: psql: error: ERROR:  pgbouncer cannot connect to server
 
ERROR:  pgbouncer cannot connect to server
 
  Warning  BackOff  13m (x14 over 15m)  kubelet  Back-off restarting failed container

The output of kubectl logs of the pod

$ go.kube.logs airflow-pgbouncer-6f59cf4769-bx5hf
Successfully generated auth_file: /home/pgbouncer/users.txt
 
2021-10-27 09:09:43.157 UTC [6] LOG kernel file descriptor limit: 1048576 (hard: 1048576); max_client_conn: 100, max expected fd use: 112
2021-10-27 09:09:43.157 UTC [6] LOG listening on 0.0.0.0:6432
2021-10-27 09:09:43.157 UTC [6] LOG listening on [::]:6432
2021-10-27 09:09:43.157 UTC [6] LOG listening on unix:/tmp/.s.PGSQL.6432
2021-10-27 09:09:43.157 UTC [6] LOG process up: PgBouncer 1.15.0, libevent 2.1.12-stable (epoll), adns: c-ares 1.17.1, tls: OpenSSL 1.1.1k  25 Mar 2021
2021-10-27 09:10:00.602 UTC [6] LOG C-0x7f16390c91b0: (nodb)/(nouser)@10.244.0.1:41595 registered new auto-database: db=airflow
2021-10-27 09:10:00.610 UTC [6] WARNING TLS handshake error: handshake failed: error:27069065:OCSP routines:OCSP_basic_verify:certificate verify error
2021-10-27 09:10:15.834 UTC [6] WARNING TLS handshake error: handshake failed: error:27069065:OCSP routines:OCSP_basic_verify:certificate verify error
2021-10-27 09:10:31.164 UTC [6] WARNING TLS handshake error: handshake failed: error:27069065:OCSP routines:OCSP_basic_verify:certificate verify error
2021-10-27 09:10:43.156 UTC [6] 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
2021-10-27 09:10:46.165 UTC [6] WARNING TLS handshake error: handshake failed: error:27069065:OCSP routines:OCSP_basic_verify:certificate verify error
2021-10-27 09:11:00.824 UTC [6] WARNING C-0x7f16390c91b0: airflow/username@psql-hostname@10.244.0.1:41595 pooler error: client_login_timeout (server down)
2021-10-27 09:11:00.824 UTC [6] WARNING C-0x7f16390c91b0: airflow/username@psql-hostname@10.244.0.1:17395 pooler error: pgbouncer cannot connect to server
2021-10-27 09:11:00.965 UTC [6] WARNING C-0x7f16390c91b0: airflow/username@psql-hostname@10.244.0.1:6755 pooler error: pgbouncer cannot connect to server
2021-10-27 09:11:00.966 UTC [6] WARNING C-0x7f16390c91b0: airflow/username@psql-hostname@10.244.0.1:24068 pooler error: pgbouncer cannot connect to server
2021-10-27 09:11:01.116 UTC [6] WARNING C-0x7f16390c91b0: airflow/username@psql-hostname@10.244.0.1:1107 pooler error: pgbouncer cannot connect to server
2021-10-27 09:11:01.117 UTC [6] WARNING C-0x7f16390c91b0: airflow/username@psql-hostname@10.244.0.1:43273 pooler error: pgbouncer cannot connect to server
 2021-10-27 09:11:30.617 UTC [6] WARNING TLS handshake error: handshake failed: error:27069065:OCSP routines:OCSP_basic_verify:certificate verify error
2021-10-27 09:11:30.620 UTC [6] LOG got SIGINT, shutting down
2021-10-27 09:11:30.823 UTC [6] LOG server connections dropped, exiting

note: i replaced the real username with "username@psql-hostname"

user_4582240 · Answer

We had 2 options to the problem (note, our airflow chart is the community chart version 8.5.2), and we chose the 1st option. When looking back, option 2 would have been easier, and would have required almost no change, once the next release has it fixed properly.

Given the fact that community airflow chart version 8.5.2 built-in pgbouncer defaults the auth type to a fixed value, which if the pgbouncer connects to azure postgresql single server, it will fail, one can choose to not use pgbouncer provided by the 8.5.2 version chart, i.e. pgbouncer=false, and then deploy their own pgbouncer (use helm and kubecetl etc), and in the airflow values.yaml file point externalDatabase host to the pgbouncer service. we chose this approach:

$ helm repo add cradlepoint https://raw.githubusercontent.com/cradlepoint/kubernetes-helm-chart-pgbouncer/master/repos/stable --force-update
$ helm upgrade --install pgbouncer cradlepoint/pgbouncer -n ${_namespace_airflow} -f ${some_path}/values.pgbouncer.yaml

$ service_pgbouncer=$(kubectl get services -n airflow |grep pgbouncer |awk '{print $1}')
$ echo "use this name: '${service_pgbouncer}' in values.yaml for airflow externalDatabase"

where you can have the values.pgbouncer.yaml to work for azure postgresql auth type. e.g. trust (which was the value when we use the azure side car image pgbouncer). For why we could not use azure side-car pgbouncer, see: https://github.com/airflow-helm/charts/issues/464#issuecomment-973811581

still use the airflow community chart 8.5.2 version built in pgbouncer , but use different approach for the deployment of the chart. (basically fix the chart pgbouncer hard-coded auth_type issue locally, and deploy the chart from the local fixed copy). Please see below 2 conversations:

The "974957815" comment above is what I realised I could have done.

airflow 2.1.3 using pgbouncer for postgresql issue

1 Answers