I did think about it after I
hit sent; but it really doing to depend on if they setup all permissions
using roles; which is good practice to do.
Did you just mean to send this to me? You should suggest it to
thread as an option.
I wonder why nobody
suggested access control changes to accomplish the read only. That is
change permissions on some or all users that can log into the database
and adjust them accordingly.
Regards,
Michael Vitale
I can think of two other ways
but the latter is more a pause than read-only. Neither are ideal
solutions but hacks that may work depending on your needs.
1. If you’re restarting the server; then
just make it a replia of nobody.
Con:
1.
Requires database restart which could take a while depending on
database activity
2. Truly is read only… i.e. no temp
tables, etc.
i.e.
Create a recovery.conf and restart the server… the database is truly
read only.
recovery.conf:
standby_mode = 'on'
primary_conninfo
= ‘host=nowhere'
2.
Without restarting server; enable synchronous replication to nowhere
and reload config.
Cons:
1. More of a
pause than read-only
2. Clients can still write changes but
will hang on commit (causing applications to hang).
3. Ctrl-c on a
hanged session will actually commit the record on the master (oops)
Pro or Con depending on your view:
1. When
releasing the synchronous setup to nowhere; hung sessions will carry on
with their commit.
postgres.conf:
synchronous_standby_names = ‘1 (nowhere)’
If you trust your clients;
then setting the default transaction state and killing the sessions
forcing a reconnect might be the best solution like has already been
suggested; however, if you dealing with end users they can change their
session transaction level back.
Thanks
Shreeyansh.
Can this be
handled from the server side may be setting some config parameters and
then restarting the master so that existing connections gets dropped and
the new ones not able to write?
