I want to see what application is the cause, but even I change to user postgres and run psql, it still throw error
If you want to connect to database any how you need to free some connections on the server
Check all the idle postgres connection
ps auxwww|grep 'idle in transaction'which will return list of all idle transaction processes with pid .
kill "pid" e.g say 10544 process having idle connection to database so
kill 10544will free up a single connection.
Now you can get access to database and check what/whom/when/where is holding your connection using query for monitoring
SELECT * FROM pg_stat_activity;
It's better to increase
max_connections of your postgresql server if you have more connections and users.
Just follow below steps
First find your
postgresql.conffile If you don't know where it is, query the database with the sql:
Mine is in:
Login as root and edit that file. Search for the string:
max_connections. You'll see a line that says
Set that number bigger, check the limit for your postgresql version.
Restart the postgresql database for the changes to take effect
service postgresql restart.
Slots reserved for
PostgreSQL reserves a few connections for superusers. If your application is not using superuser connections you can connect as a superuser (eg
postgres). Then run diagnostic queries like:
select * from pg_stat_activity;
superuser_reserved_connections setting in the Postgres configuration. Defaults to 3 reserved slots.
To quote the version 11 documentation:
Determines the number of connection “slots” that are reserved for connections by PostgreSQL superusers. At most
max_connectionsconnections can ever be active simultaneously. Whenever the number of active concurrent connections is at least
superuser_reserved_connections, new connections will be accepted only for superusers, and no new replication connections will be accepted.
The default value is three connections. The value must be less than
max_wal_senders. This parameter can only be set at server start.