0

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.

4
  • Do these clients connect through an app server under the same name or are the usernames all different?
    – Vérace
    Aug 8, 2021 at 9:46
  • @Vérace what's "app server"? Whether under same postgresql user_name/role - yes.
    – Nilcale
    Aug 8, 2021 at 10:17
  • change the firewall so that it does allow traffic from "outside" per script make your maintanance and remove the changes. if you have a rest api shut the web server down for the time being
    – nbk
    Aug 8, 2021 at 16:47
  • Short explanation of what an "app server" (application server) is. BTW, please put any extra information into the question as well as responding to the person who originally asked for it - it is imposing an extra cognitive burden on those who wish to help having to trawl through comments. Keeping all the relevant information in the question is preferable.
    – Vérace
    Aug 9, 2021 at 7:47

1 Answer 1

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:

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.