10

I have Apache-Airflow implemented on an Ubuntu version 18.04.3 server. When I set it up, I used the sql lite generic database, and this uses the sequential executor. I did this just to play around and get used to the system. Now I'm trying to use the Local Executor, and will need to transition my database from sqlite to the recommended postgres sql.

Does anybody know how to make this transition? All of the tutorials I've found entail setting up Airflow with postgres sql from the beginning. I know there are a ton of moving parts and I'm scared of messsing up what I currently have running. Anybody who knows how to do this or can point me at where to look is much appreciated. Thanks!

Improve this question
1
  • you should be fine. After installing postgres, create a user/password and update airflow.cfg file accordingly. Commented Oct 15, 2019 at 1:39

3 Answers 3

Reset to default
13

Just to complete @lalligood answer with some commands:

In airflow.cfg file look for sql_alchemy_conn and update it to point to your PostgreSQL serv:

sql_alchemy_conn = postgresql+psycopg2://user:pass@hostadress:port/database

For instance:

sql_alchemy_conn = postgresql+psycopg2://airflow:airflow@localhost:5432/airflow

As indicated in the above line you need both user and database called airflow, therefore you need to create that. To do so, open your psql command line and type the following commands to create a user and database called airflow and give all privileges over database airflow to user airflow:

CREATE USER airflow;
CREATE DATABASE airflow;
GRANT ALL PRIVILEGES ON DATABASE airflow TO airflow;

Now you are ready to init the airflow application using postgres:

airflow initdb

If everything was right, access the psql command line again, enter in airflow database with \c airflow command and type \dt command to list all tables of that database. You should see a list of airflow tables, currently it is 23.

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

2 Comments

in newer versions it's airflow db init
In my case, I had to slightly change the CREATE USER command - CREATE USER 'airflow' WITH PASSWORD 'airflow';
1

Another option other than adding to the airflow.cfg file

is to set the ENV varibale AIRFLOW__CORE__SQL_ALCHEMY_CONN to the postgresql server you want.

Example: export AIRFLOW__CORE__SQL_ALCHEMY_CONN_SECRET=sql_alchemy_conn

Or you can set it in your Dockerfile setting.

See documentation here

Improve this answer

Comments

0

I was able to get it working by doing the following 4 steps:

  1. Assuming that you are starting from scratch, initialize your airflow environment with the SQLite database. The key takeaway here is for it to generate the airflow.cfg file.
  2. Update the sql_alchemy_conn line in airflow.cfg to point to your PostgreSQL server.
  3. Create the airflow role + database in PostgreSQL. (Revoke all permissions from public to airflow database & ensure airflow role owns airflow database!)
  4. (Re)Initialize airflow (airflow initdb) & confirm that you see ~19 tables in the airflow database.
Improve this answer

1 Comment

I'm installing postgresql. When i enter the command 'psql createdb airflow' to create a database to point to, i get the following error: psql: could not connect to server: Connection refused Is the server running on host "server.domain.org" (65.254.244.180) and accepting TCP/IP connections on port 5432? I read i need to update a directory but cant find it. I'm pretty lost now. thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.