Multiple Airflow deployment in Single DB

Problem Statement:

Airflow deployments can use only one DB at the time. This is because, at the time of deployment, Airflow will create initial tables in public schema as show in the figure

So we can't create, another Airflow deployment in same DB.

Solution:

Create a new schema with an unique user credential, grant all the privileges on the user credential to access the newly created schema. Then alter the search path of new user set to new schema. Finally add the schema name with DB user credentials in Airflow YAML deployment file.

Steps to achieve this:

Create a new schema and user credentials

CREATE SCHEMA airflow_ciapi;

CREATE USER nishok_ciapi WITH PASSWORD '***************';

Grant privileges queries

GRANT ALL PRIVILEGES ON DATABASE postgres to nishok_ciapi;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA airflow_ciapi TO nishok_ciapi;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA airflow_ciapi TO nishok_ciapi;

GRANT ALL ON ALL TABLES IN SCHEMA airflow_ciapi TO nishok_ciapi;
GRANT ALL ON ALL SEQUENCES IN SCHEMA  airflow_ciapi TO nishok_ciapi;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA airflow_ciapi TO s_ciapi;
nishok
GRANT CREATE ON SCHEMA airflow_ciapi TO nishok_ciapi;

Alter search path query


ALTER USER nishok_ciapi SET search_path = airflow_ciapi;

Both the Schema in same DB

Sample YAML file for Airflow deployment

airflow:
  service:
    type: NodePort
  image: ********.dkr.ecr.us-east-1.amazonaws.com/***********
  tag: GIT_SHA
  nodeselector: airflow
  config:
    dags_folder: /root/airflow/dags
    s3_url: s3://airflow
    base_url: airflow.xyz.com
    schema: airflow_ciapi

Blog