Error while connecting postgres db from airflow

Question

Using:

sql_alchemy_conn = db+postgresql://username:[email protected]:5432/airflow gives error:

sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:db.postgresql

and when using:

sql_alchemy_conn = postgresql+psycopg2://username:xxx@localhost:5432/airflow I could run the airlfow webserver -p 8080 but while running the scheduler: airflow scheduler it throws error:

ModuleNotFoundError: No module named 'MySQLdb'

packages versions I am using:

psycopg2==2.7.3.1
sqlalchemy==1.1.15
sqlalchemy-redshift== 0.7.0
apache_airflow=1.8.2

Earlier sql_alchemy_conn = db+postgresql://username:[email protected]:5432/airflow did worked for me - 2 months ago. I don't know what is the problem now.

Please don't post images of text. Include tracebacks as text, in their entirety. The (typical) form of DB URL is dialect+driver://username:password@host:port/database and I'm pretty sure the dialect "db" has never existed. Typical values would be postgresql, mysql, mssql, and such. — Ilja Everilä
– Ilja Everilä, Commented Nov 12, 2017 at 14:38
Earlier I followed the answer posted here:stackoverflow.com/questions/37785061/… about using "db" and it worked then. Now it is causing problem because of the upgrades in some packages, I tried all possible solutions that I could found - nothing worked. — Javed
– Javed, Commented Nov 12, 2017 at 14:52
Ah it's a configuration URL for Celery, not SQLAlchemy, so that's why. They look rather similar, but are meant for different things, it'd seem. About that module not found, it'd seem Celery is for some reason trying to pass SQLAlchemy a DB URL that is using the MySQL dialect and MySQLdb driver specifically. A default perhaps? — Ilja Everilä
– Ilja Everilä, Commented Nov 12, 2017 at 14:54
This:result_backend = 'db+postgresql://scott:tiger@localhost/mydatabase' is the celery format to connect postgres database as mentioned in official documentation:docs.celeryproject.org/en/latest/userguide/configuration.html, but this did not work either. — Javed
– Javed, Commented Nov 12, 2017 at 14:56

Javed · Accepted Answer · 2021-09-08 09:01:47Z

18

finally,the following settings worked - in airflow.cfg:

sql_alchemy_conn = postgresql+psycopg2://scot:tiger@localhost:5432/airflow
celery_result_backend = db+postgres://scot:tiger@localhost:5432/airflow

It is important to note that dialect+driver are different for sql_alchemy_conn and celery_result_backend settings in airflow.cfg file, although they are pointing to the same database.

edited Sep 8, 2021 at 9:01

answered Nov 23, 2017 at 12:01

Javed

6,2975 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

saadi Over a year ago

I am doing it exactly like this, webserver & scheduler are running fine as well, but when I do $ airflow celery worker, I get

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "postgres" to address: nodename nor servname provided, or not known

, I checked airflow.cfg, no where in there I wrongly have placed postgres in place of localhost.

Ali Husham Over a year ago

should I put these in settings.py? or where?

Daniel Chepenko Over a year ago

You should put it in airflow.cfg

tsveti_iko Over a year ago

Also for me the Celery results backend is called just result_backend instead of celery_result_backend

Collectives™ on Stack Overflow

Error while connecting postgres db from airflow

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related