Re: [GENERAL] Lock out PostgreSQL users for maintenance
On Sat, 12 May 2012 06:29:54 +0200 Alexander Farber alexander.far...@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 fr...@frank.uvena.de pgpKHQ4kaTSLV.pgp Description: PGP signature
Re: [GENERAL] Lock out PostgreSQL users for maintenance
2012-05-12 06:29 keltezéssel, Alexander Farber írta: Or should I edit pg_hba.conf and restart the process? hostallpostgres127.0.0.1/32md5 hostallall 0.0.0.0/0reject 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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock out PostgreSQL users for maintenance
On 2012-05-12, Alexander Farber alexander.far...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general