1

I want to see what application is the cause, but even I change to user postgres and run psql, it still throw error

  • Could you disconnect current users? stackoverflow.com/a/5109190/3270427 – McNets May 23 '18 at 8:23
  • @McNets but how ?, I cannot connect to postgres to run any query – duc May 23 '18 at 9:44
  • It sounds you have access to the OS. If on linux, you can run as postgres a variant of ps fuxwww to see what's going on. – dezso May 23 '18 at 9:46
  • @dezso thank you, run ps fuxwww as postgres I can see all the connection and database. Is there a way I can see more, each connection in my app have application_name I want to see it too :) – duc May 23 '18 at 10:01
  • I'm afraid you can do that only if you kick a process (using kill - be careful not to use kill -9 as it will stop the whole DB) and quickly log in to the 'slot' made free that way. Or dig the log files. – dezso May 23 '18 at 13:44
2

If you want to connect to database any how you need to free some connections on the server

  1. Check all the idle postgres connection ps auxwww|grep 'idle in transaction' which will return list of all idle transaction processes with pid .

  2. 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

  1. 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

  2. Login as root and edit that file. Search for the string: max_connections. You'll see a line that says max_connections=100

  3. Set that number bigger, check the limit for your postgresql version.

  4. Restart the postgresql database for the changes to take effect service postgresql restart.

  • You should log in as postgres to edit the configuration file, not as root – a_horse_with_no_name May 28 '18 at 7:21
  • root(server level user) will also do as later he need to restart postgresql services as well – Ashiq Ahamed May 28 '18 at 7:25
3

Slots reserved for postgres superuser

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

See the 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_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus 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_connections minus max_wal_senders. This parameter can only be set at server start.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.