Thread: 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>
Attachment
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