3

I am trying to install three node airflow cluster. Each node has airflow scheduler, airflow worker, airflow webserver, also it has celery, RabbitMQ cluster and Postgres multi master cluster(implemented with Bucardo). Versions of software:

  • Airflow 2.0.1
  • Postregsql 13.2
  • Ubuntu 20.04
  • pyhton 3.8.5
  • celery 4.4.7
  • bucardo 5.6.0
  • RabbitMQ 3.8.2

And I occur the problem starting airflow scheduler.

When I launch the first one(database is empty), it successfully starts. But then when I'm launching another scheduler on another machine(I tried to launch on the same machine too), it fails with the following:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "job_pkey"
DETAIL:  Key (id)=(25) already exists.

[SQL: INSERT INTO job (dag_id, state, job_type, start_date, end_date, latest_heartbeat, executor_class, hostname, unixname) VALUES (%(dag_id)s, %(state)s, %(job_type)s, %(start_date)s, %(end_date)s, %(latest_heartbeat)s, %(executor_class)s, %(hostname)s, %(unixname)s) RETURNING job.id]
[parameters: {'dag_id': None, 'state': 'running', 'job_type': 'SchedulerJob', 'start_date': datetime.datetime(2021, 4, 21, 7, 39, 20, 429478, tzinfo=Timezone('UTC')), 'end_date': None, 'latest_heartbeat': datetime.datetime(2021, 4, 21, 7, 39, 20, 429504, tzinfo=Timezone('UTC')), 'executor_class': 'CeleryExecutor', 'hostname': 'hostname', 'unixname': 'root'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

After trying to launch a few times eventually scheduler is working. I am assuming id is incremented and then data is successfully added into database:

airflow=> select * from job order by state;
 id | dag_id |  state  |   job_type   |          start_date           |           end_date            |       latest_heartbeat        | executor_class |           hostname           | unixname 
----+--------+---------+--------------+-------------------------------+-------------------------------+-------------------------------+----------------+------------------------------+----------
 26 |        | running | SchedulerJob | 2021-04-21 07:39:22.243721+00 |                               | 2021-04-21 07:39:22.243734+00 | CeleryExecutor |                machine name  | root
 25 |        | running | SchedulerJob | 2021-04-21 07:39:14.515009+00 |                               | 2021-04-21 07:39:19.632811+00 | CeleryExecutor |                machine name  | root

There is a warning with log tables as well(If the second and subsequent schedulers successfully started):

WARNING - Failed to log action with (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "log_pkey"
DETAIL:  Key (id)=(40) already exists.

I understand why scheduler cannot insert data into table, but how should it work correctly, how to launch multiple schedulers? Official documentation tells no additional configuration required. Hope I explained very clear. Thanks!

Denis
  • 31
  • 1
  • 3
  • Can you tell us which airflow version are you using and the value of the `use_row_level_locking` configuration parameter in _all_ the servers? – Iñigo González Apr 21 '21 at 09:04
  • Yes, version airflow is 2.0.1, ```use_row_level_locking = True``` on all servers – Denis Apr 21 '21 at 09:09
  • Is your connection string is like this `postgres://user@localhost/database` ? or like this `postgres://user@host1,host2,host3/database` ? – Iñigo González Apr 21 '21 at 09:19
  • I'm using the first connection string, since I have Postgresql with master-master replication and located on the same machines. – Denis Apr 21 '21 at 09:42

2 Answers2

0

Looks like there is a race condition between the Airflow Schedulers and Bucardo.

Probably the easiest way to fix it is to query all servers sequentially with a connection string like this in your airflow.cfg (the same on all nodes):

[core]
sql_alchemy_conn=postgresql://USER:PASS@/DB?host=node1:port1&host=node2B&host=node3

For this to work you'll need sqlalchemy >= 1.3

Why this happens

There is a race condition between your schedulers and bucardo trying to read and write data from the table in different hosts. Changes does not propagate as quickly as they should and server writes to the table fail.

Even if you treat all your nodes as "multimaster", making all nodes look first at the same server will remediate this problem. In case of failure, they will use the second one.

Iñigo González
  • 3,735
  • 1
  • 11
  • 27
  • Thanks for the reply! But I don't think so. Let me explain. I am launching the first scheduler, then I am waiting until it starts, then I am trying to launch another one. – Denis Apr 21 '21 at 12:16
0

I asked Airflow developers. The problem is in Bucardo since it does not support

'SELECT ... FOR UPDATE' :

I suspect that the problem is with Bucardo, which does not support record locking properly. We have high expectations, because it is a key protection mechanism against running the same task by many schedulers. http://airflow.apache.org/docs/apache-airflow/stable/scheduler.html#database-requirements If that doesn't work you will have problems with duplicate keys.

Thanks!

Denis
  • 31
  • 1
  • 3