Force client disconnect using PostgreSQL

Question

Is there a way to force clients to disconnect from PostgreSQL? I'm looking for the equivlent of DB2's force application all.

I'd like to do this on my development box because when I've got database consoles open, I can't load a database dump. I have to quit them first.

Answer 1

Kills idle processes in PostgreSQL 8.4:

SELECT procpid, (SELECT pg_terminate_backend(procpid)) as killed from pg_stat_activity
   WHERE current_query LIKE '<IDLE>';

Answer 2

Combine pg_terminate_backend function and the pg_stat_activity system view.

Answer 3

This SO answer beautifully explains (full quote from araqnid between the horizontal rules, then me again):

---

To mark database 'applogs' as not accepting new connections:

update pg_database set datallowconn = false where datname = 'applogs';

Another possibility would be to revoke 'connect' access on the database for the client role(s).

Disconnect users from database = kill backend. So to disconnect all other users from "applogs" database, for example:

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'applogs' and procpid <> pg_backend_pid();

Once you've done both of those, you are the only user connected to 'applogs'. Although there might actually be a delay before the backends actually finish disconnecting?

---

Update from MarkJL: There is indeed a delay before the backends finish disconnecting.

Now me again: That being said, mind that the procpid column was renamed to pid in PostgreSQL 9.2 and later.

I think that this is much more helpful than the answer by Milen A. Radev which, while technically the same, does not come with usage examples and real-life suggestions.

Answer 4

I post my answer because I couldn't use any of them in my script, server 9.3:

psql -U postgres -c "SELECT pid, (SELECT pg_terminate_backend(pid)) as killed from pg_stat_activity WHERE datname = 'my_database_to_alter';"

In the next line, you can do anything yo want with 'my_database_to_alter'. As you can see, yo perform the query from the "postgres" database, which exists almost in every postgresql installation.

Doing by superuser and outside the problem-database itself worked perfect for me.

Answer 5

probably a more heavy handed approach then should be used but:

for x in `ps -eF | grep -E "postgres.*idle"| awk '{print $2}'`;do kill $x; done

Answer 6

I found this thread on the mailing list. It suggests using SIGTERM to cause the clients to disconnect.

Not as clean as db2 force application all.