Lock out PostgreSQL users for maintenance

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