Home / server / Questions / 388430
Accepted
Alexander Farber
Asked: 2012-05-12 20:23:27 +0800 CST

Lock out PostgreSQL users for maintenance

  • 772

In PostgreSQL 8.4.9 with pgbouncer 1.3.4 in pool_mode = session (but some users connect to the database directly) - when I login as "super user" with 

   psql -U postgres -W postgres

What is the command please to disconnect all "normal users" and prevent them from connecting again while I perform maintenance (I'd like to rename some table columns for more consistent naming and correspondingly change some stored procedures).

I can't find it in the III. Server Administration doc.

Or should I edit pg_hba.conf and restart the service/process?

postgresql

3 Answers

  1. Best Answer

    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.

    • 6

  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?"

    • 2

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

    ALTER DATABASE db SET CONNECTION LIMIT 0; and more

    • 1