Nondeterministic timeouts connecting Airflow to Postgres server on Azure

[Ethan-Henley]

Apache Airflow version

2.1.0

What happened

Noticed today that running tasks that involve connecting to a postgres database via sqlalchemy inconsistently succeed or fail due to connection timeout, apparently at random. When they fail, get logs as below.

Postgres server is running normally as per tests. Did not experience any such timeouts before the holiday and have only pushed one edit since, adding a sensor at the start of the dag where this was first noticed.

What you expected to happen

Would expect this to consistently succeed (or consistently fail, signaling a clearer problem with the server or connection).

How to reproduce

1. Set up Azure account and environment
2. Create Azure Kubernetes Service (below)

az aks create -g PROJECTNAME-rg -n kube-PROJECTNAME --node-vm-size Standard_D2_v3 --node-count 2 --generate-ssh-keys --nodepool-name system
az aks nodepool update --resource-group PROJECTNAME-rg --cluster-name kube-PROJECTNAME --name system --mode System
az aks nodepool add --resource-group PROJECTNAME-rg --cluster-name kube-PROJECTNAME --name computepool --node-vm-size Standard_D8_v3 --node-count 3 --labels agentpool=app01 --mode User
az aks get-credentials --name kube-PROJECTNAME --resource-group PROJECTNAME-rg
az aks nodepool update --resource-group PROJECTNAME-rg --cluster-name kube-PROJECTNAME --name computepool --enable-cluster-autoscaler --min-count 1 --max-count 3

3. Create postgres servers for airflow and for project data

az postgres server create --resource-group PROJECTNAME-rg --name PROJECTNAME-airflow  --location eastus --admin-user <USER> --admin-password <PASS> --sku-name GP_Gen5_2 --version 11
az postgres server firewall-rule create --resource-group PROJECTNAME-rg --server-name PROJECTNAME-airflow --name AirflowRule --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
az postgres server create --resource-group PROJECTNAME-rg --name PROJECTNAME  --location eastus --admin-user <USER> --admin-password <PASS> --sku-name GP_Gen5_8 --version 11
az postgres server firewall-rule create --resource-group PROJECTNAME-rg --server-name PROJECTNAME --name DataRule --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
az postgres server update \
    --resource-group PROJECTNAME-rg \
    --name PROJECTNAME \
    --storage-size 550000

4. Set up DNS zone as per https://github.com/kubernetes-sigs/external-dns/blob/master/docs/tutorials/azure.md
5. Populate the Postgres server with at least one large table, run a dag made up of repeated tasks that query and read that table into memory temporarily, repeat until observing timeout.

Operating System

Running this in Kubernetes on an Azure server.

Versions of Apache Airflow Providers

apache-airflow-providers-microsoft-azure==1.0.0

Deployment

Other

Deployment details

Helm version 3.0 as per https://raw.githubusercontent.com/helm/helm/master/scripts/get-helm-3, uses
helm.sh/chart: airflow-2.1.0

Anything else

Log from a failed task run:

Running <TaskInstance: DAG_NAME.TASK_NAME DATE_TIME [queued]> on host POD_ADDRESS
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 241, in _do_get
    return self._create_connection()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 508, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/airflow/.local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection timed out
        Is the server running on host "POSTGRES_SERVER_ADDRESS" (POSTGRES_SERVER_IP) and accepting
        TCP/IP connections on port 5432?

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg[bot]]

Thanks for opening your first issue here! Be sure to follow the issue template!

[potiuk]

Please do not open issues on Airlfow when the problem is with your deployment. You can open discussion instead.

This is not an airflow bug - this is an issue with your deployment instability. Check your server what resources are blocking the server.

Maybe you have a problem with too many copnnectio, You should generally setup pgbouncer if you use postgres so maybe that's your problem: https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html?highlight=pgbouncer#setting-up-a-postgresql-database

Converting it into discussion.

[Ethan-Henley]

Appreciate the move/correction.

Interested in setting up pgbouncer for best practice, but connection count should not be an issue; never had more than 4-5 live at a time during any of the failures. Server was consistently accessible for local query at time of tests, whether or not I simultaneously had another connection live does not appear to have affected likelihood of error—that's why I initially thought this was an airflow-related bug.

Curious if you or anyone else might know where to look next with something like this.

[potiuk]

It's interesting - but intermittent issues like that are usually caused by the deployment. Do you have some monitoring of the network/db
logs/db/memory/disk/server uptime monitoring ?. Usually those should be set up and give some answers. Airflow is a software available for free and you install it and deploy in the way you usually deploy other software and "embed" and monitor resources in the same way as any other app.

There is definitely nothing "airflow" specific here - you can search other's issues yourselv (it's all publicly available and searchable) but I personally do not recall any similar issue. PGBouncer is a general recommended approach, so I'd try it anyway.

[stoiandl]

@Ethan-Henley Look at your postgresql logs. Also check the number of concurrent connections.
Could also be the connection from AKS to Postgres. Check your Azure Prosgresql firewall rules and ensure that encompasses all IPs of the AKS Cluster nodes. If this doesn't work, ssh into a container and try connecting from there to postgres. Try to replicate what Airflow is doing.

[bheesma]

I am having the same issue. I am able to connect to the Postgres database from the docker container where airflow runs, but airflow is giving Connection Times Out issue. There’s hardly 5 or 6 connections to database.

[potiuk]

Just searchiung google gave me this: https://stackoverflow.com/questions/60496337/remote-postgres-database-disconnects-frequently-on-azure , Not sure if it works, but if it does not, I recomment to search more as there were plenty other similar cases and rather than passively waiting for a solution you could actively search for them.

[potiuk]

From that - I belive you need to enable keepalives, but again - you need to search what parameters to configure your sqlalchemy connection with to enable them (and this all can be found in sqlalchemy docs and Airflow docs explaining how to pass custom configuraiton to sqlalachemy - just look at "configuring the database".

[csingla11]

I have got same issue today, the exact same, did you find any solution please?

[sfirke]

I experience this about once per month with Airflow 2.6.1 that uses an Azure PostgreSQL database. Revisiting this thread, I have just tried @potiuk's solution above, implementing the keepalive_kwargs described at https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html#setting-up-a-postgresql-database. Future users, feel free to @ me in June 2024 or so to see if that fixed it. I don't have PGbouncer set up, that would be my next step.