I want to see what application is the cause, but even I change to user postgres and run psql, it still throw error
PostgreSQL – How to Connect When ‘Sorry, Too Many Clients Already’ Error Occurs
postgresql
Related Question
- Postgresql – What does this error mean when doing a pg_restore on a Postgres 9.6.2 database
- Postgresql – Change owner of relation in PostgreSQL database
- PostgreSQL – Fixing ‘Invalid Data Directory’ Error in psql
- PostgreSQL – Switch User to ‘postgres’ on macOS Results in ‘su: Sorry’ Error
- PostgreSQL – Fixing ‘Improper Qualified Name’ Error in ALTER FUNCTION
- Postgresql – connecting to Postgres running inside docker container ‘posgres’ does not exist
- Postgresql – Receiving connection timeout error when call PQconnectdbParams to connect to Postgres
Best Answer
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 10544
will 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.conf
file If you don't know where it is, query the database with the sql:SHOW config_file;
Mine is in:
/var/lib/pgsql/data/postgresql.conf
Login as root and edit that file. Search for the string:
max_connections
. You'll see a line that saysmax_connections=100
Set that number bigger, check the limit for your postgresql version.
Restart the postgresql database for the changes to take effect
service postgresql restart
.