From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Rui DeSousa <rui.desousa@icloud.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Stopping writes in master
Date: Thu, 14 Jun 2018 12:13:00 -0400
Message-ID: <5B22940C.9070201@sqlexec.com> (raw)
In-Reply-To: <DBEF8D41-1004-4FBF-8560-8FD3726B7A00@icloud.com>
References: <CAF6DVKMNN845W+MCRPXhJQH=a2-uBcKwktx7353a-fs4YR1OQg@mail.gmail.com>
<5B224C42.6060107@sibvisions.com>
<CAF6DVKOxxOJgG36zKjqwCd8oNOLAqmpOxCFCMWs6Bs7owBVd8A@mail.gmail.com>
<CAGDYbUOPFgrJKtSaYO3Z71z3uYcp5mYG7ijQekL4J_cpwgCqUg@mail.gmail.com>
<CAF6DVKOMGX41rV-P=1_o1j=18pq7buLXhK7jr5Y9pFoFc_6cxQ@mail.gmail.com>
<773C842E-0684-424D-B7A0-E24264A87495@icloud.com>
<5B228BF0.1060306@sqlexec.com>
<DBEF8D41-1004-4FBF-8560-8FD3726B7A00@icloud.com>
Yeah forgot to send to thread
> Rui DeSousa <mailto:rui.desousa@icloud.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@sqlexec.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.desousa@icloud.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.manna@gmail.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@gmail.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;
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, rui.desousa@icloud.com, pgsql-admin@lists.postgresql.org
Subject: Re: Stopping writes in master
In-Reply-To: <5B22940C.9070201@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox