CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlconnection
Vlad Balanescu
Vlad Balanescu

Reputation: 674

Kill postgre connection in rails console

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'

Upvotes: 0

Views: 1336

Answers (2)

Sam Morgan
Sam Morgan

Reputation: 228

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.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246013

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.

Upvotes: 1

Related Questions