Re: Stopping writes in master

Yeah forgot to send to thread

> Rui DeSousa <mailto:rui(dot)desousa(at)icloud(dot)com>
> Thursday, June 14, 2018 11:42 AM
> 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.
>
>
> MichaelDBA <mailto:MichaelDBA(at)sqlexec(dot)com>
> Thursday, June 14, 2018 11:38 AM
> 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
>
>
> Rui DeSousa <mailto:rui(dot)desousa(at)icloud(dot)com>
> Thursday, June 14, 2018 11:20 AM
> 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.
>
>
> Debraj Manna <mailto:subharaj(dot)manna(at)gmail(dot)com>
> Thursday, June 14, 2018 9:46 AM
> 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?
>
> Shreeyansh Dba <mailto:shreeyansh2014(at)gmail(dot)com>
> Thursday, June 14, 2018 8:06 AM
> Hi Debraj Manna,
>
> Yes,You can make the database read only as well just by setting the
> below parameter is equal to true at the session level.But you need
> reconnect your existing connections.
>
>
> ALTER DATABASE YOUR_DATABASE_NAME SET default_transaction_read_only =
> true;
>
> Hope this helps..
>
>
> <http://www.shreeyansh.com>
>
>