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.

[sfirke]

This morning I had another one of these incidents. The first error in the Airflow scheduler log was:

File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.OperationalError: SSL connection has been closed unexpectedly

Then in the Azure Portal for my resource, I see under Resource Health that there was an outage that coincided with the Airflow failure:

Due to maintenance task being performed on your Azure Database for PostgreSQL - Flexible server, the server is temporarily unavailable.

Then subsequent error messages from Airflow looked like the above, with connection timed out or refused. So yes, seems like an Azure SQL problem unrelated to the particulars of Airflow - except that the Airflow scheduler container did not recover after the DB was back up. Sharing this in case it helps others in the future.

[potiuk]

except that the Airflow scheduler container did not recover after the DB was back up.

Generally speaking - such an error should cause failure of scheduler - and that's EXPECTED way to handle such operational errors. Airflow components have a number of places where such a failure can be recovered from (and leads to exponential retries) but most cases might lead to internal memory vs. database inconsistencies, so deliberate choice in this case is to stop the whole component (for example scheduler). This is pretty valid, and very robust way of handling such issue.

Whatever deployment you are doing should restart airflow - possibly with some exponential back-off and attempt to restart such failed component few times before it gives up - this is usual, and expected behaviour and deployment is responsible for it.

Was it different in your case @sfirke ? Was scheduler "shut down" as result of it? Because if it was then correct assesment should be

So yes, seems like an Azure SQL problem and the way Airflow has been deployed did not handle recovery appropriately.

[sfirke]

I agree that's a reasonable interpretation and I can adjust my deployment (in this case, docker compose) to work around a case like this where the scheduler goes unhealthy due to the Postgres metadata DB becoming temporarily unavailable. I think concerns of "docker compose isn't adequate for production" are often overblown, and the added complexity of Kubernetes is not worth it to my org, but it feels relevant here -- I will put something in place to mitigate this.

[potiuk]

I agree that's a reasonable interpretation and I can adjust my deployment (in this case, docker compose) to work around a case like this where the scheduler goes unhealthy due to the Postgres metadata DB becoming temporarily unavailable.

Let me be clear. This is not a workaround. This is expected behaviour that your deployment need to be prepared to handle. Full stop. Developing a software that is resilient and self-recovering from all potential problems is order of magnitude more costly than just failing in case of unexpected situation and this is the choice pretty much any open-source software makes (and also this is the choice made by Airflow). We have explained all the different installation methods of Airflow in https://airflow.apache.org/docs/apache-airflow/stable/installation/index.html# and we explicitly stated that when you choose certain option of deployment, you are responsible to manage and certain problems there.

I actually think that docker compose isn't adequate for production is not a correct statement. I think my personal concern is about a different statement that people who want to deploy airflow often express: expecting that you can get docker-compose.yaml that is ready for production. As opposed to Helm chart that you can really make configurable and handle a lot of cases and treat it more as "easy to deploy" thing, running docker compose is fine, as long as you heavily update and modify your docker compose yaml file(s) and learn about all the ways your docker compose features that you would like to use.

When you compare docker-compose to Helm deployment in Kubernetes - this makes things easier for you as deployment "configuration" - because with Helm you generally don't need to - usually learn about all the nitty-gritty details on how you have to prepare your resources to run Airflow. You still need to learn kubernetes internals to monitor and operate Airflow there, because k8s is a leaky abstraction, but you do not have to learn how to define the deployment - you only have to learn how to configure it.

With docker-compose - you need to know how docker, container etc. works, but also you need to learn how to write your own docker-compose.yaml files and learn all the details on how to define things like liveness checks etc. - because - almost by definition, whereHelm is a configurable set of templates that you can configure via declarative options, for docker compose, the docker-compose.yaml IS the declarative way to describe your deployment, and there is no useful abstraction where you can make it simpler and configurable for deployment managers. Deployment managers need to write their own docker-compose.yaml files essentiall and know how to do it.

But people think that this is not the case and when you expose docker-compose.yaml, they think it is production-ready out-of-the-box, which is wrong.

But yes - you can make Docker-compose deployment production-ready - but you have to be prepared to handle and solve a lot of problems and exceptional cases by making your docke-compose yaml configuration handle those cases.