Обсуждение: Lock out PostgreSQL users for maintenance

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?

Thank you
Alex

On Sat, 12 May 2012 06:29:54 +0200
Alexander Farber <alexander.farber@gmail.com> wrote:

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

At least this is what we are doing. We are having a normal pg_hba.conf
and a pg_hba.conf for maintenance and switching on demand. Maybe not
the best solution, but its working ;)

Cheers,
Frank
--
Frank Lanitz <frank@frank.uvena.de>

2012-05-12 06:29 keltezéssel, Alexander Farber írta:
> Or should I edit pg_hba.conf and restart the process?

host    all    postgres    127.0.0.1/32    md5
host    all    all         0.0.0.0/0        reject

Only postgres user is allowed to connect. You don't need
to restart the server, one of these would do:

killall -HUP postmaster
or
SELECT pg_reload_conf();

To kick out the current users except yourself, do:

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity WHERE procpid <> pg_backend_pid();

>
> Thank you
> Alex
>


--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig&  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
      http://www.postgresql.at/

On 2012-05-12, Alexander Farber <alexander.farber@gmail.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