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.


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