Re: Lock out PostgreSQL users for maintenance

On 2012-05-12, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
> Hello,
>
> 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 at
> http://www.postgresql.org/docs/8.4/static/admin.html
>
> Or should I edit pg_hba.conf and restart the process?

you can do

REVOKE CONNECT ON DATABASE foo FROM PUBLIC,other_user,etc;
SELECT pg_terminate_backend(procpid) from pg_stat_activity where datname = 'foo';

Afterwards you'll have to GRANT CONNECT back to the users who need it.

--
⚂⚃ 100% natural