Change #1111162 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] airflow: replace the scheduler liveness check by a tcpSocket probe

https://gerrit.wikimedia.org/r/1111162

Change #1111162 merged by Brouberol:

[operations/deployment-charts@master] airflow: replace the scheduler liveness check by a tcpSocket probe

https://gerrit.wikimedia.org/r/1111162

Change #1111259 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] airflow: ensure the pooler URI uses a terninated FQDN

https://gerrit.wikimedia.org/r/1111259

Change #1111259 merged by Brouberol:

[operations/deployment-charts@master] airflow: ensure the pooler URI uses a terninated FQDN

https://gerrit.wikimedia.org/r/1111259

We've tried to mitigate the DNS resolution issue by switching the PGBouncer DNS from postgresql-airflow-search-pooler-rw.airflow-search to postgresql-airflow-search-pooler-rw.airflow-search.svc.cluster.local, in order to bypass the DNS search domains. however we're still seeing crashes, with the following error message:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "postgresql-airflow-search-pooler-rw.airflow-search.svc.cluster.local" to address: Temporary failure in name resolution

The way this whole setup works is by:

1. resolving postgresql-airflow-search-pooler-rw.airflow-search.svc.cluster.local to its Service cluster IP
2. the cluster IP balances to the IPs of the ready pgbouncer pods using LVS
3. the client connects to a pgbouncer pod, port 5432
4. pgbouncer redirects the connection to postgresql-airflow-search-rw.airflow-search, which is a Service pointing to the IP of the master PG pod
5. that service resolves to a cluster IP, itself dynamically pointing to the primary pod IP, using LVS

brouberol@deploy2002:~$ k get service postgresql-airflow-search-rw -o json | jq .spec.selector
{
  "cnpg.io/cluster": "postgresql-airflow-search",
  "cnpg.io/instanceRole": "primary"
}

One idea would be change the PGBouncer URI from postgresql://app:REDACTED@postgresql-airflow-test-k8s-pooler-rw.airflow-test-k8s.svc.cluster.local:5432/app to postgresql://app:REDACTED@<pooler-rw cluster IP>:5432/app, to bypass the DNS resolution of the postgresql-airflow-search-pooler-rw.airflow-search.svc.cluster.local service.

@BTullis raised an interesting theory by finding the following messages

The issue could be located in libpqitself (which would explain why we're only seeing resolution failures related to postgresql, and not other service names) and IPv6 resolutions.

We are going to to implement the idea laid out in the previous message (replacing the hostname by an IPv4 cluster ip), to bypass DNS resolution,

We have manually redeployed the airflow-test-k8s scheduler with the following diff applied:

diff --git a/charts/airflow/templates/_configmap.yaml.tpl b/charts/airflow/templates/_configmap.yaml.tpl
index 8f56ceed..8653c895 100644
--- a/charts/airflow/templates/_configmap.yaml.tpl
+++ b/charts/airflow/templates/_configmap.yaml.tpl
@@ -160,10 +160,16 @@ metadata:
   {{- include "base.meta.labels" . | indent 2 }}
   namespace: {{ .Release.Namespace }}
 data:
-  {{/* This script outputs the URI used to connect to PGBouncer, using a service FQDN */}}
+  {{/*
+    This script outputs the URI used to connect to PGBouncer, using the RW PGBouncer cluster IP.
+    We do this to bypass any DNS resolution within psycopg2 (and in particular, libpq), that is
+    sometimes affected by spurious failures, causing the scheduler to crash.
+
+    See https://phabricator.wikimedia.org/T383651#10461629
+  */}}
   pg_pooler_uri: |
     #!/bin/sh
-    printf ${PG_URI} | sed "s/$PG_HOST/$POOLER_NAME/" | sed "s/{{ $.Release.Namespace }}:5432/{{ $.Release.Namespace }}.svc.cluster.local:5432/"
+    printf ${PG_URI} | sed "s/$PG_HOST.{{ $.Release.Namespace }}/$POSTGRESQL_AIRFLOW_{{ $.Values.config.airflow.instance_name | upper | replace "-" "_" }}_POOLER_RW_SERVICE_HOST/"

When deployed, this causes the PGBouncer uri to include the PGBouncer RW service cluster IP:

airflow@airflow-scheduler-86b74f8f8b-6kbdc:/opt/airflow$ ./usr/bin/pg_pooler_uri
postgresql://app:XXX@10.67.39.4:5432/app

brouberol@deploy2002:~$ k get svc postgresql-airflow-test-k8s-pooler-rw
NAME                                    TYPE        CLUSTER-IP   EXTERNAL-IP   PORT(S)    AGE
postgresql-airflow-test-k8s-pooler-rw   ClusterIP   10.67.39.4   <none>        5432/TCP   110d

We'll now wait to see whether the scheduler crashes from time to time, or whether we've successfully sidestepped. this issue.

Change #1112206 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] airflow: bypass DNS resolution for the PG URI

https://gerrit.wikimedia.org/r/1112206

Change #1112206 merged by Brouberol:

[operations/deployment-charts@master] airflow: bypass DNS resolution for the PG URI

https://gerrit.wikimedia.org/r/1112206

I've deployed this patch onto all the schedulers. We'll now monitor the logs for a couple of days, to see whether they still crash. I had already deployed it into airflow-test-k8s, and the scheduler had been stable for 3 days.