psycopg2.OperationalError) FATAL sorry, too many clients already

psycopg2.OperationalError) FATAL: sorry, too many clients already

Problem Description

The below FATAL error comes up in some of the workflow nodes resulting in node failure during workflow execution in Infoworks Data Foundry.

xxxxxxxxxx

Subtask PB_18MX psycopg2.OperationalError: FATAL:  sorry, too many clients already

Root cause: This issue occurs if there are multiple workflows running at the same time and if the threshold value for the connections that these workflows can make to Postgres DB is exceeded.

The default value for the number of connections to Postgres DB is configured to 100 (max_connections=100).

Solution

a) Increase the number of connections to 500 from 100 by changing the value for the below config in $IW_HOME/resources/postgres/pgsql/data/postgresql.conf.

Before Change:

max_connections=100

After Change

max_connections=500

b) Set the below config to True in $IW_HOME/orchestrator-engine/airflow.cfg

sql_alchemy_pool_enabled = True

c) source $IW_HOME/bin/env.sh

d) $IW_HOME/bin/stop.sh orchestrator rabbitmq postgres

e) $IW_HOME/bin/start.sh orchestrator rabbitmq postgres

f) Restart the failed workfow.

Applicable Infoworks Data Foundry Versions

IWX v2.x,3.x,4.x