Services backed by Postgres fail or hang

The number of connections between the CDH services and the PostgreSQL database is governed by the max_connections setting. By default, the maximum number of available connections to your PostgreSQL database is 115. 15 connections are reserved for the superuser to maintain the state and integrity of your database, and 100 connections are available for CDH and other services. If the number of connections to the database exceeds the connection limit, new connections may fail, Cloudera Manager may hang, and you may be unable to log into Hue. The logs show the following error: “FATAL: remaining connection slots are reserved for non-replication superuser connections”.

1. Check the number of available and idle connections:
   1. SSH into the PostgreSQL database from the command-line client psql as an admin user.
   2. Run the following query to check the number of idle connections:

      SELECT datname, count(datname) FROM pg_stat_activity WHERE state = 'idle' GROUP BY datname;

   3. Run the following query to check the number of connections currently in use:

      SELECT datname, count(datname) FROM pg_stat_activity GROUP BY datname;

   4. Run the following command to view the maximum number of connections:

      show max_connections;

   5. Run the following query to know where the connections are going:

      SELECT datname, numbackends FROM pg_stat_database;

2. If most connections are idle and the max_connections value is less than 100, then increase the max_connections value in the postgresql.conf file:
   1. Log into Cloudera Manager and stop all services that use the Postgres database.
   2. SSH into the host on which the Postgres server is running.
   3. Open the postgresql.conf file for editing.

      The postgresql.conf file is typically present in the /var/lib/pgsql/data directory. But this may vary depending on where you have installed the database.

   4. Increase the value of max_connections as per the following recommendation:

      Allow a maximum of 100 connections for each database and add 50 extra connections. For example, for two databases, set the maximum connections to 250.

      If you store five databases on one host (the databases for Cloudera Manager Server, Activity Monitor, Reports Manager, Cloudera Navigator, and Hive Metastore), set the maximum connections to 550.

   5. Save the changes and exit.
   6. Restart the Postgres database by running the following command:

      pg_ctl restart

   7. Restart all the affected services from Cloudera Manager.

If increasing the connection limit does not solve your problem and you see a need to scale up, then add new Postgres instances on other hosts and migrate the services to those hosts with the help of your Database Administrator (DBA).