Lock out PostgreSQL users for maintenance

Solution 1:

There isn't one, as far as I'm aware -- and I don't see the value in it. PgSQL gives you the ability to modify the database structure live, and in many cases all within a single transaction.

The hard part in any schema migration is having the users of the database know that they have to change the way they use the database (modifying code that calls the database, rewriting documentation, re-training users who execute manual, ad-hoc queries). Preventing users from connecting while you're doing the maintenance doesn't help with that, because they're just going to do things the old way when you allow them to reconnect. If you're able to do a lock-step migration of all users of the database at the same time as the maintenance, then you'd also be able to tell them all to stop using the database while you fiddle with it anyway.

Solution 2:

Kicking them off should be as easy as something like:

SELECT pg_terminate_backend( psa.procpid )
    FROM pg_stat_activity psa
    JOIN pg_user u
    ON u.usesysid = psa.usesysid
    WHERE psa.procpid <> pg_backend_pid( )
        AND psa.datname = current_database( )
        AND u.usename NOT IN ('list', 'of', 'users', 'to', 'not', 'disconnect')
;

You could combine that with either revoke connect on <datname> from <role> or editing pg_hba.conf...

ref: "How do I detach all other users from a postgres database?"

Solution 3:

According to https://dba.stackexchange.com/a/163518/118371 you can also:

ALTER DATABASE db SET CONNECTION LIMIT 0; and more