How to forcefully drop all active connections off a database?

Question

On one server I have a Postgresql database and there're many clients on other servers accross the internet that connect to it. I want to drop all the active connections on demand, they should be dropped forcefully and for N minutes. Then I'll do some maintance work on a database and then allow clients to reconnect.

I don't have control over the clients accross the internet, hence, forceful way of dropping connections. There're also local clients. Postgresql service should remain active.

How can I do it?

---

And I'd prefer to refrain from changing the Postgresql config files to make the connections drop off, the settings such as "allow only connections on local <> all interface(s)". Although, this might be the easiest way to achive the goal? But there're also clients who connect on the local interface, even psql itself.

Answer 1

You can do the following (adapted from here):

First, you block new connections.

Now, we are fortunate in that you only have one username which is connecting (see commments), so the process should be relatively painless (if you are the postgresql user).

 REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;

So now, having blocked new connections, we get rid of current ones:

SELECT 
    pg_cancel_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

If you are the postgresql user and the other user(s) have a different name, you can do this (from here):

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity 
WHERE usename = 'foo_user';

So, you do your maintenance work and then:

GRANT CONNECT ON DATABASE dbname TO PUBLIC, username;

Now, there is another command which can be used, but it's a bit more drastic (from here) - pg_terminate_backend(pid):

• pg_cancel_backend(pid) will attempt to gracefully kill a running query process.

• pg_terminate_backend(pid) will immediately kill the running query process, but potentially have side affects across additional queries running on your database server. The full connection may be reset when running pg_terminate_backend, so other running queries can be affected. Use as a last resort.

If that's not enough of a warning, check this out (from here)!

Be careful with that! As pointed by Erwin Andreasen in the comments bellow, pg_terminate_backend is the kill -9 in PostgreSQL. It will terminate the entire process which can lead to a full database restart in order to recover consistency.

So, using pg_terminate_backend(pid) may cause a server restart, which you have specified is not what you want!

Further reading:

• PostgreSQL documentation here.

• a good blog on this subject is available from the excellent Cybertec site here.

Related:

• How to terminate all DB usage of a PostgreSQL user.