pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStopping writes in master
10+ messages / 5 participants
[nested] [flat]
* Stopping writes in master
@ 2018-06-14 10:46 Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Debraj Manna @ 2018-06-14 10:46 UTC (permalink / raw)
To: pgsql-admin
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,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 11:01 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Debraj Manna <subharaj.manna@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Shreeyansh Dba @ 2018-06-14 11:01 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: pgsql-admin
Hi Debraj Manna,
You have to make your client as read only so that client can not write
on the master.
Hope this helps..
<http://www.shreeyansh.com;
On Thu, Jun 14, 2018 at 4:16 PM, Debraj Manna <subharaj.manna@gmail.com>
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,
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 11:06 Robert Zenz <robert.zenz@sibvisions.com>
parent: Debraj Manna <subharaj.manna@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Robert Zenz @ 2018-06-14 11:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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,
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 11:23 Debraj Manna <subharaj.manna@gmail.com>
parent: Robert Zenz <robert.zenz@sibvisions.com>
0 siblings, 1 reply; 10+ messages in thread
From: Debraj Manna @ 2018-06-14 11:23 UTC (permalink / raw)
To: Robert Zenz <robert.zenz@sibvisions.com>; +Cc: pgsql-admin@lists.postgresql.org
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@sibvisions.com>
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,
> >
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 12:06 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Shreeyansh Dba @ 2018-06-14 12:06 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Robert Zenz <robert.zenz@sibvisions.com>; pgsql-admin@lists.postgresql.org
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;
On Thu, Jun 14, 2018 at 4:53 PM, Debraj Manna <subharaj.manna@gmail.com>
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@sibvisions.com>
> 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,
>> >
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 13:46 Debraj Manna <subharaj.manna@gmail.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Debraj Manna @ 2018-06-14 13:46 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: Robert Zenz <robert.zenz@sibvisions.com>; pgsql-admin@lists.postgresql.org
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@gmail.com>
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..
>
>
> <http://www.shreeyansh.com;
>
> On Thu, Jun 14, 2018 at 4:53 PM, Debraj Manna <subharaj.manna@gmail.com>
> 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@sibvisions.com>
>> 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,
>>> >
>>
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 14:59 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Debraj Manna <subharaj.manna@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Shreeyansh Dba @ 2018-06-14 14:59 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Robert Zenz <robert.zenz@sibvisions.com>; pgsql-admin@lists.postgresql.org
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.
<http://www.shreeyansh.com;
On Thu, Jun 14, 2018 at 7:16 PM, Debraj Manna <subharaj.manna@gmail.com>
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@gmail.com>
> 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..
>>
>>
>> <http://www.shreeyansh.com;
>>
>> On Thu, Jun 14, 2018 at 4:53 PM, Debraj Manna <subharaj.manna@gmail.com>
>> 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@sibvisions.com>
>>> 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,
>>>> >
>>>
>>>
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 15:20 Rui DeSousa <rui.desousa@icloud.com>
parent: Debraj Manna <subharaj.manna@gmail.com>
1 sibling, 2 replies; 10+ messages in thread
From: Rui DeSousa @ 2018-06-14 15:20 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>; Robert Zenz <robert.zenz@sibvisions.com>; pgsql-admin@lists.postgresql.org
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.
> On Jun 14, 2018, at 9:46 AM, Debraj Manna <subharaj.manna@gmail.com> 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@gmail.com <mailto:shreeyansh2014@gmail.com>> 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..
>
>
> <http://www.shreeyansh.com/;
>
> On Thu, Jun 14, 2018 at 4:53 PM, Debraj Manna <subharaj.manna@gmail.com <mailto:subharaj.manna@gmail.com>> 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@sibvisions.com <mailto:robert.zenz@sibvisions.com>> 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,
> >
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 16:07 Rui DeSousa <rui.desousa@icloud.com>
parent: Rui DeSousa <rui.desousa@icloud.com>
1 sibling, 0 replies; 10+ messages in thread
From: Rui DeSousa @ 2018-06-14 16:07 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>; Robert Zenz <robert.zenz@sibvisions.com>; pgsql-admin@lists.postgresql.org
Actually, I think the better option is just to reroute all your clients to one of the replias and leave the master as read/write. It really going to depend on how you have HA setup.
It would be good to know that actual use case.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stopping writes in master
@ 2018-06-14 16:13 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Rui DeSousa <rui.desousa@icloud.com>
1 sibling, 0 replies; 10+ messages in thread
From: MichaelDBA @ 2018-06-14 16:13 UTC (permalink / raw)
To: Rui DeSousa <rui.desousa@icloud.com>; +Cc: pgsql-admin@lists.postgresql.org
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;
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2018-06-14 16:13 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-14 10:46 Stopping writes in master Debraj Manna <subharaj.manna@gmail.com>
2018-06-14 11:01 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-06-14 11:06 ` Robert Zenz <robert.zenz@sibvisions.com>
2018-06-14 11:23 ` Debraj Manna <subharaj.manna@gmail.com>
2018-06-14 12:06 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-06-14 13:46 ` Debraj Manna <subharaj.manna@gmail.com>
2018-06-14 14:59 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-06-14 15:20 ` Rui DeSousa <rui.desousa@icloud.com>
2018-06-14 16:07 ` Rui DeSousa <rui.desousa@icloud.com>
2018-06-14 16:13 ` MichaelDBA <MichaelDBA@sqlexec.com>
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