Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fTUs5-0003Ev-Sn for pgsql-admin@arkaria.postgresql.org; Thu, 14 Jun 2018 16:13:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fTUs4-0004Ia-Gj for pgsql-admin@arkaria.postgresql.org; Thu, 14 Jun 2018 16:13:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fTUs4-0004IO-7z for pgsql-admin@lists.postgresql.org; Thu, 14 Jun 2018 16:13:12 +0000 Received: from mout.perfora.net ([74.208.4.197]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fTUrz-0001ns-VM for pgsql-admin@lists.postgresql.org; Thu, 14 Jun 2018 16:13:11 +0000 Received: from [192.168.1.101] ([98.169.215.140]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Le3w2-1g1Tmc2fLI-00pq5R; Thu, 14 Jun 2018 18:13:02 +0200 Message-ID: <5B22940C.9070201@sqlexec.com> Date: Thu, 14 Jun 2018 12:13:00 -0400 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Rui DeSousa CC: pgsql-admin@lists.postgresql.org Subject: Re: Stopping writes in master References: <5B224C42.6060107@sibvisions.com> <773C842E-0684-424D-B7A0-E24264A87495@icloud.com> <5B228BF0.1060306@sqlexec.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------070901000603050300060609" X-Provags-ID: V03:K1:C0hDpJ/p9Trb20xUSex9LjfVEFKx/ECGL7xprlGH1a4jVf/PGuG LjJOi6SGR4wlLUkTGPT8+LYbTOPZXyLZ/dlzF4LwWgftlY/IxpYNIGK1CODpdv/hFY//Bxq ZI2VbvDx+659Bsi/c7zt5BpAhysoNuuhZWo+QE+wngiwqsiBD8vHvmnVYVF/jWXBCFoE+Db 5TPuPhHA9cVdFdQPTL7Ug== X-UI-Out-Filterresults: notjunk:1;V01:K0:B6NQbpGa3YA=:RUC4RkEAvvaJLZ8+bEaMb9 L6i9rGFxq7a595ogdMg6L8BdRNKXzELbSZYCF7wP57PtCVkWLdHv6G6X7F/yZRMlY310Tlj7S HyyOCQLxtBPgSCScmkySw4AqtTT3p0Dz3IfWhzm5jPPXEg42WzpT9MSYrjitmOOWp7mjt4Ezg FeGuDUDSrZzWhjkD3A1YeCbmEvAjD3Y94wBFKW96nKws/IspLFxU4+2Wed/RzKpTEx31nIF3g 2nwSO7RNu/SRR1+NyF1evOYUlohooOsZ2/2ljOXuJaTAfe7pasV55pTyhpZLsgja47z6pZZ2X WAREAstGDt1d7QQKvkM8DyMI0BsAJo0tg+jofBbvy6twtUw1RG5k7Y52dAxo5er/xuP0cmw/4 F5mbPG6dYqqvvNtH0xs9GsLqLCzYBGipCWpk+xdwsC5D+2cwY0yPxaqkQEgyBTn7rNtJd82Ph z5rTDa3vbbMXzmeN4xD1gr/uT67N2RuMbDzt1/V5tGkszP1+G7IWFFVl7V7n2lkmPptYbHM+g qP/B64zKMhp+0Abq0lXWv9Ocfw+0JgpfqbNmHSJg+GfUVH+0ECzDMBqP9QiDIBuplWmblHU8j SgycRAgucwVlDC0iKFo9mNBuOiDdy+NFoqLgjC/niY9x+XBaoSHnuVLpNVQH0qmjeCBMg+FA3 LKZB1LgmCUetEdmQCM7DRCHWm+TGUNup7SWM7QQ2LHcFmHU/ukCiO6U29G7qFiigWBuw6ZoPT p808PcoQJsDFthyD List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------070901000603050300060609 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Yeah forgot to send to thread > Rui DeSousa > 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 > 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 > 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 > 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 > 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.. > > > > > --------------070901000603050300060609 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Yeah forgot to send to thread


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.


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


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.


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?

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..






--------------070901000603050300060609--