Kill postgre connection in rails console

Question

How can I kill all postgre connections using rails console? I am getting this:

PG::ConnectionBad
FATAL: sorry, too many clients already FATAL: sorry, too many clients already

Rails.root: C:/Users/IBM_ADMIN/Desktop/Work/Extreme Blue Summer Internship/extreme-blue

Application Trace | Framework Trace | Full Trace
activerecord (4.2.7.1) lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `initialize'
activerecord (4.2.7.1) lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `new'
activerecord (4.2.7.1) lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `connect'
activerecord (4.2.7.1) lib/active_record/connection_adapters/pos

The thing is I don't want to drop the database, but just to kill all the connections to it? Any help will be kindly appreciated!

tgresql_adapter.rb:242:in `initialize'

Is there somewhere in your code where you are making connections? Perhaps the connections are being left open perpetually? — ydaniju, Dec 07 '16 at 15:38
http://stackoverflow.com/questions/2757549/org-postgresql-util-psqlexception-fatal-sorry-too-many-clients-already — ydaniju, Dec 07 '16 at 15:45

score 1 · Accepted Answer · answered Dec 07 '16 at 15:47

As a PostgreSQL superuser, you can use the function pg_terminate_backend to kill a session. You can get a list of backend process IDs from the pg_stat_activity view.

So the query could look like this:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

To be less disruptive, you could add AND state = 'idle'.

Make sure your application does not connect as superuser, then there will always be superuser_reserved_connections (default 3) connections left that can be used by a superuser.

All that said, your application should really not exceed a maximum number of connections. If you can't guarantee that, use a connection pooler like pgBouncer.

Thanks @Laurenz for your answer. Is rails automatically closing connections as I don't remember to have written any loops which should let me active connections — Vlad Balanescu, Dec 07 '16 at 18:36

score 0 · Answer 2 · answered Dec 07 '16 at 18:00

You cannot use the rails console to kill Postgres processes.

You should restart Postgres however you started it (either from the command-line or e.g. from the PostgresApp application) and it will clear the current connections.

As a side note - you shouldn't be exceeding the number of connections Postgres can offer inside a Rails app. You'd either need a lot of concurrent users, or some really problematic loop somewhere.

Kill postgre connection in rails console

2 Answers2