I’m having an annoying problem with PGAdmin4. One of our workflows involves dropping and recreating a DB from a backup fairly frequently. If someone is connected using PGAdmin4 at this time, it blocks dropping the DB. Killing the connection doesn’t work because it is instantly resumed to generate the server dashboard amongst other things.

I am trying to solve this by temporarily disabling login for any users connected to the DB until it is dropped and re-created, but I am having difficulty passing the list of users to the ALTER USER function, and am unsure if I can operate on multiple users at once. Query below:

ALTER USER (SELECT usename FROM pg_stat_activity WHERE dataname="THEDATABASE" and usename NOT IN ('postgres', 'systemuser1', 'systemuser2')) WITH NOLOGIN;

Does anyone have insight into how I might do this? I am on Postgres 12 if that is relevant.

Anonymous Asked question May 14, 2021