Re: Stopping writes in master

[Shreeyansh Dba <shreeyansh2014@xxxxxxxxx>]

Hi Debraj Manna,

Yes, You may change some other parameters in config file except default_transaction_read_only=true and restart master.

But you can not set this default_transaction_read_only=true in the config file, because it will affect on whole database cluster means your cluster will go in read-only mode.

You can set this parameter at the session level to make particular database read-only. If you want to drop existing connections then reconnect the existing connections or restart the application if you can afford the downtime.

On Thu, Jun 14, 2018 at 7:16 PM, Debraj Manna <subharaj.manna@xxxxxxxxx> wrote:

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?

On Thu 14 Jun, 2018, 5:36 PM Shreeyansh Dba, <shreeyansh2014@xxxxxxxxx> wrote:

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

On Thu, Jun 14, 2018 at 4:53 PM, Debraj Manna <subharaj.manna@xxxxxxxxx> wrote:

Yes I want to switch the database to read-only mode. The existing connections should also be not able to write.

On Thu 14 Jun, 2018, 4:37 PM Robert Zenz, <robert.zenz@xxxxxxxxxxxxxx> wrote:

Can existing connections be dropped or do they need to stay established? Also,
are you trying to switch the database to a read-only mode for backup purposes or
something different?


On 14.06.2018 12:46, Debraj Manna wrote:
> Hi
>
> In a 3 node cluster with 1 master and 2 slaves with postgres 10.4 is there
> any  configuration setting that will stop all writes to the master from the
> client. But the clients can read data from master and other slaves ?
>
> Thanks,
>