Standalone synchronous master

On Wed, Nov 13, 2013 at 6:39 PM, Rajeev rastogi
<rajeev.rastogi@huawei.com> wrote:

Add a new "eager" synchronous mode that starts out synchronous but reverts
to asynchronous after a failure timeout period

This would require some type of command to be executed to alert
administrators of this change.

http://archives.postgresql.org/pgsql-hackers/2011-12/msg01224.php
This patch implementation is in the same line as it was given in the earlier
thread.

Some Of the additional important changes are:

1. Have added two GUC variable to take commands from user to be
executed

a. Master_to_standalone_cmd: To be executed before master switches to
standalone mode.

b. Master_to_sync_cmd: To be executed before master switches from sync
mode to standalone mode.

In description of both switches (a & b), you are telling that it
will switch to
standalone mode, I think by your point 1b. you mean to say other way
(switch from standalone to sync mode).

Instead of getting commands, why can't we just log such actions? I think
adding 3 new guc variables for this functionality seems to be bit high.

Also what will happen when it switches to standalone mode incase there
are some async standby's already connected to it before going to
standalone mode, if it continues to send data then I think naming it as
'enable_standalone_master' is not good.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 8th Jan, 2014, Amit Kapila Wrote

Add a new "eager" synchronous mode that starts out synchronous but
reverts to asynchronous after a failure timeout period

This would require some type of command to be executed to alert
administrators of this change.

http://archives.postgresql.org/pgsql-hackers/2011-12/msg01224.php
This patch implementation is in the same line as it was given in the
earlier thread.

Some Of the additional important changes are:

1. Have added two GUC variable to take commands from user to be
executed

a. Master_to_standalone_cmd: To be executed before master

switches to

standalone mode.

b. Master_to_sync_cmd: To be executed before master switches

from sync

mode to standalone mode.

In description of both switches (a & b), you are telling that it
will switch to
standalone mode, I think by your point 1b. you mean to say other way
(switch from standalone to sync mode).

Yes you are right. Its typo mistake.

Instead of getting commands, why can't we just log such actions? I
think
adding 3 new guc variables for this functionality seems to be bit
high.

Actually in earlier discussion as well as in TODO added, it is mentioned
to execute some kind of command to be executed to alert administrator.
http://archives.postgresql.org/pgsql-hackers/2011-12/msg01224.php

In my current patch, I have kept the LOG along with command.

Also what will happen when it switches to standalone mode incase
there
are some async standby's already connected to it before going to
standalone mode, if it continues to send data then I think naming it
as
'enable_standalone_master' is not good.

Yes we can change name to something more appropriate, some of them are:
1. enable_async_master
2. sync_standalone_master
3. enable_nowait_master
4. enable_nowait_resp_master

Please provide your suggestion on above name or any other?.

Thanks and Regards,
Kumar Rajeev Rastogi

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 11/13/2013 03:09 PM, Rajeev rastogi wrote:

This patch implements the following TODO item:

Add a new "eager" synchronous mode that starts out synchronous but reverts to asynchronous after a failure timeout period
This would require some type of command to be executed to alert administrators of this change.
http://archives.postgresql.org/pgsql-hackers/2011-12/msg01224.php

This patch implementation is in the same line as it was given in the earlier thread.
Some Of the additional important changes are:

1. Have added two GUC variable to take commands from user to be executed

a. Master_to_standalone_cmd: To be executed before master switches to standalone mode.

b. Master_to_sync_cmd: To be executed before master switches from sync mode to standalone mode.

2. Master mode switch will happen only if the corresponding command executed successfully.

3. Taken care of replication timeout to decide whether synchronous standby has gone down. i.e. only after expiry of

wal_sender_timeout, the master will switch from sync mode to standalone mode.

Please provide your opinion or any other expectation out of this patch.

I'm going to say right off the bat that I think the whole notion to
automatically disable synchronous replication when the standby goes down
is completely bonkers. If you don't need the strong guarantee that your
transaction is safe in at least two servers before it's acknowledged to
the client, there's no point enabling synchronous replication in the
first place. If you do need it, then you shouldn't fall back to a
degraded mode, at least not automatically. It's an idea that keeps
coming back, but I have not heard a convincing argument why it makes
sense. It's been discussed many times before, most recently in that
thread you linked to.

Now that I got that out of the way, I concur that some sort of hooks or
commands that fire when a standby goes down or comes back up makes
sense, for monitoring purposes. I don't much like this particular
design. If you just want to write log entry, when all the standbys are
disconnected, running a shell command seems like an awkward interface.
It's OK for raising an alarm, but there are many other situations where
you might want to raise alarms, so I'd rather have us implement some
sort of a generic trap system, instead of adding this one particular
extra config option. What do people usually use to monitor replication?

There are two things we're trying to solve here: raising an alarm when
something interesting happens, and changing the configuration to
temporarily disable synchronous replication. What would be a good API to
disable synchronous replication? Editing the config file and SIGHUPing
is not very nice. There's been talk of an ALTER command to change the
config, but I'm not sure that's a very good API either. Perhaps expose
the sync_master_in_standalone_mode variable you have in your patch to
new SQL-callable functions. Something like:

pg_disable_synchronous_replication()
pg_enable_synchronous_replication()

I'm not sure where that state would be stored. Should it persist
restarts? And you probably should get some sort of warnings in the log
when synchronous replication is disabled.

In summary, more work is required to design a good
user/admin/programming interface. Let's hear a solid proposal for that,
before writing patches.

BTW, calling an external command with system(), while holding
SyncRepLock in exclusive-mode, seems like a bad idea. For starters,
holding a lock will prevent a new WAL sender from starting up and
becoming a synchronous standby, and the external command might take a
long time to return.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-01-08 11:07:48 +0200, Heikki Linnakangas wrote:

I'm going to say right off the bat that I think the whole notion to
automatically disable synchronous replication when the standby goes down is
completely bonkers. If you don't need the strong guarantee that your
transaction is safe in at least two servers before it's acknowledged to the
client, there's no point enabling synchronous replication in the first
place.

I think that's likely caused by the misconception that synchronous
replication is synchronous in apply, not just remote write/fsync. I have
now seen several sites that assumed that and just set up sync rep to
maintain that goal to then query standbys instead of the primary after
the commit finished.
If that assumption were true, supporting a timeout that way would
possibly be helpful, but it is not atm...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 8 January 2014 09:07, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

I'm going to say right off the bat that I think the whole notion to
automatically disable synchronous replication when the standby goes down is
completely bonkers.

Agreed

We had this discussion across 3 months and we don't want it again.
This should not have been added as a TODO item.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jan 8, 2014 at 05:39:23PM +0000, Simon Riggs wrote:

On 8 January 2014 09:07, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

I'm going to say right off the bat that I think the whole notion to
automatically disable synchronous replication when the standby goes down is
completely bonkers.

Agreed

We had this discussion across 3 months and we don't want it again.
This should not have been added as a TODO item.

I am glad Heikki and Simon agree, but I don't. ;-)

The way that I understand it is that you might want durability, but
might not want to sacrifice availability. Phrased that way, it makes
sense, and notifying the administrator seems the appropriate action.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Jan 8, 2014, at 9:27 PM, Bruce Momjian wrote:

On Wed, Jan 8, 2014 at 05:39:23PM +0000, Simon Riggs wrote:

On 8 January 2014 09:07, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

I'm going to say right off the bat that I think the whole notion to
automatically disable synchronous replication when the standby goes down is
completely bonkers.

Agreed

We had this discussion across 3 months and we don't want it again.
This should not have been added as a TODO item.

I am glad Heikki and Simon agree, but I don't. ;-)

The way that I understand it is that you might want durability, but
might not want to sacrifice availability. Phrased that way, it makes
sense, and notifying the administrator seems the appropriate action.

technically and conceptually i agree with andres and simon but from daily experience i would say that we should make it configurable.
some people got some nasty experiences when their systems stopped working.

+1 for a GUC to control this one.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/08/2014 10:27 PM, Bruce Momjian wrote:

On Wed, Jan 8, 2014 at 05:39:23PM +0000, Simon Riggs wrote:

On 8 January 2014 09:07, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

I'm going to say right off the bat that I think the whole notion to
automatically disable synchronous replication when the standby goes down is
completely bonkers.

Agreed

We had this discussion across 3 months and we don't want it again.
This should not have been added as a TODO item.

I am glad Heikki and Simon agree, but I don't. ;-)

The way that I understand it is that you might want durability, but
might not want to sacrifice availability. Phrased that way, it makes
sense, and notifying the administrator seems the appropriate action.

They want to have the cake and eat it too. But they're not actually
getting that. What they actually get is extra latency when things work,
with no gain in durability.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jan 8, 2014 at 10:46:51PM +0200, Heikki Linnakangas wrote:

On 01/08/2014 10:27 PM, Bruce Momjian wrote:

On Wed, Jan 8, 2014 at 05:39:23PM +0000, Simon Riggs wrote:

On 8 January 2014 09:07, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

I'm going to say right off the bat that I think the whole notion to
automatically disable synchronous replication when the standby goes down is
completely bonkers.

Agreed

We had this discussion across 3 months and we don't want it again.
This should not have been added as a TODO item.

I am glad Heikki and Simon agree, but I don't. ;-)

The way that I understand it is that you might want durability, but
might not want to sacrifice availability. Phrased that way, it makes
sense, and notifying the administrator seems the appropriate action.

They want to have the cake and eat it too. But they're not actually
getting that. What they actually get is extra latency when things
work, with no gain in durability.

They are getting guaranteed durability until they get a notification ---
that seems valuable. When they get the notification, they can
reevaluate if they want that tradeoff.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Bruce Momjian <bruce@momjian.us> wrote:

Heikki Linnakangas wrote:

They want to have the cake and eat it too. But they're not
actually getting that. What they actually get is extra latency
when things work, with no gain in durability.

They are getting guaranteed durability until they get a
notification --- that seems valuable.  When they get the
notification, they can reevaluate if they want that tradeoff.

My first reaction to this has been that if you want synchronous
replication without having the system wait if the synchronous
target goes down, you should configure an alternate target.  With
the requested change we can no longer state that when a COMMIT
returns with an indication of success that the data has been
persisted to multiple clusters.  We would be moving to a situation
where the difference between synchronous is subtle -- either way
the data may or may not be on a second cluster by the time the
committer is notified of success.  We wait up to some threshold
time to try to make the success indication indicate that, but then
return success even if the guarantee has not been provided, without
any way for the committer to know the difference.

On the other hand, we keep getting people saying they want the
database to make the promise of synchronous replication, and tell
applications that it has been successful even when it hasn't been,
as long as there's a line in the server log to record the lie.  Or,
more likely, to record the boundaries of time blocks where it has
been a lie.  This appears to be requested because other products
behave that way.

I'm torn on whether we should cave to popular demand on this; but
if we do, we sure need to be very clear in the documentation about
what a successful return from a commit request means.  Sooner or
later, Murphy's Law being what it is, if we do this someone will
lose the primary and blame us because the synchronous replica is
missing gobs of transactions that were successfully committed.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-01-08 13:34:08 -0800, Kevin Grittner wrote:

On the other hand, we keep getting people saying they want the
database to make the promise of synchronous replication, and tell
applications that it has been successful even when it hasn't been,
as long as there's a line in the server log to record the lie.

Most people having such a position I've talked to have held that
position because they thought synchronous replication would mean that
apply (and thus visibility) would also be synchronous. Is that
different from your experience?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Kevin Grittner <kgrittn@ymail.com> writes:

I'm torn on whether we should cave to popular demand on this; but
if we do, we sure need to be very clear in the documentation about
what a successful return from a commit request means.� Sooner or
later, Murphy's Law being what it is, if we do this someone will
lose the primary and blame us because the synchronous replica is
missing gobs of transactions that were successfully committed.

I'm for not caving. I think people who are asking for this don't
actually understand what they'd be getting.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/08/2014 12:27 PM, Bruce Momjian wrote:

I am glad Heikki and Simon agree, but I don't. ;-)

The way that I understand it is that you might want durability, but
might not want to sacrifice availability. Phrased that way, it makes
sense, and notifying the administrator seems the appropriate action.

I think there's a valid argument to want things the other way, but I
find the argument not persuasive. In general, people who want
auto-degrade for sync rep either:

a) don't understand what sync rep actually does (lots of folks confuse
synchronous with simultaneous), or

b) want more infrastructure than we actually have around managing sync
replicas

Now, the folks who want (b) have a legitimate need, and I'll point out
that we always planned to have more features around sync rep, it's just
that we never actually worked on any. For example, "quorum sync" was
extensively discussed and originally projected for 9.2, only certain
hackers changed jobs and interests.

If we just did the minimal change, that is, added an "auto-degrade" GUC
and an alert to the logs each time the master server went into degraded
mode, as Heikki says we'd be loading a big foot-gun for a bunch of
ill-informed DBAs. People who want that are really much better off with
async rep in the first place.

If we really want auto-degrading sync rep, then we'd (at a minimum) need
a way to determine *from the replica* whether or not it was in degraded
mode when the master died. What good do messages to the master log do
you if the master no longer exists?

Mind you, being able to determine on the replica whether it was
synchronous or not when it lost communication with the master would be a
great feature to have for sync rep groups as well, and would make them
practical (right now, they're pretty useless). However, I seriously
doubt that someone is going to code that up in the next 5 days.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/08/2014 01:34 PM, Kevin Grittner wrote:

I'm torn on whether we should cave to popular demand on this; but
if we do, we sure need to be very clear in the documentation about
what a successful return from a commit request means. Sooner or
later, Murphy's Law being what it is, if we do this someone will
lose the primary and blame us because the synchronous replica is
missing gobs of transactions that were successfully committed.

I am trying to follow this thread and perhaps I am just being dense but
it seems to me that:

If you are running synchronous replication, as long as the target
(subscriber) is up, synchronous replication operates as it should. That
is that the origin will wait for a notification from the subscriber that
the write has been successful before continuing.

However, if the subscriber is down, the origin should NEVER wait. That
is just silly behavior and makes synchronous replication pretty much
useless. Machines go down, that is the nature of things. Yes, we should
log and log loudly if the subscriber is down:

ERROR: target xyz is non-communicative: switching to async replication.

We then should store the wal logs up to wal_keep_segments.

When the subscriber comes back up, it will then replicate in async mode
until the two are back in sync and then switch (perhaps by hand) to sync
mode. This of course assumes that we have a valid database on the
subscriber and we have not overrun wal_keep_segments.

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/08/2014 11:37 PM, Andres Freund wrote:

On 2014-01-08 13:34:08 -0800, Kevin Grittner wrote:

On the other hand, we keep getting people saying they want the
database to make the promise of synchronous replication, and tell
applications that it has been successful even when it hasn't been,
as long as there's a line in the server log to record the lie.

Most people having such a position I've talked to have held that
position because they thought synchronous replication would mean that
apply (and thus visibility) would also be synchronous.

And I totally agree that it would be a useful mode if apply was
synchronous. You could then build a master-standby pair where it's
guaranteed that when you commit a transaction in the master, it's
thereafter always seen as committed in the standby too. In that usage,
if the link between the two is broken, you could set up timeouts e.g so
that the standby stops accepting new queries after 20 seconds, and then
the master proceeds without the standby after 25 seconds. Then the
guarantee would hold.

I don't know if the people asking for the fallback mode are thinking
that synchronous replication means synchronous apply, or if they're
trying to have the cake and eat it too wrt. durability and availability.

Synchronous apply would be cool..

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Josh Berkus <josh@agliodbs.com> writes:

If we really want auto-degrading sync rep, then we'd (at a minimum) need
a way to determine *from the replica* whether or not it was in degraded
mode when the master died. What good do messages to the master log do
you if the master no longer exists?

How would it be possible for a replica to know whether the master had
committed more transactions while communication was lost, if the master
dies without ever restoring communication? It sounds like pie in the
sky from here ...

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Joshua D. Drake" <jd@commandprompt.com> writes:

However, if the subscriber is down, the origin should NEVER wait. That
is just silly behavior and makes synchronous replication pretty much
useless. Machines go down, that is the nature of things. Yes, we should
log and log loudly if the subscriber is down:

ERROR: target xyz is non-communicative: switching to async replication.

We then should store the wal logs up to wal_keep_segments.

When the subscriber comes back up, it will then replicate in async mode
until the two are back in sync and then switch (perhaps by hand) to sync
mode. This of course assumes that we have a valid database on the
subscriber and we have not overrun wal_keep_segments.

It sounds to me like you are describing the existing behavior of async
mode, with the possible exception of exactly what shows up in the
postmaster log.

Sync mode is about providing a guarantee that the data exists on more than
one server *before* we tell the client it's committed. If you don't need
that guarantee, you shouldn't be using sync mode. If you do need it,
it's not clear to me why you'd suddenly not need it the moment the going
actually gets tough.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> wrote:

On 2014-01-08 13:34:08 -0800, Kevin Grittner wrote:

On the other hand, we keep getting people saying they want the
database to make the promise of synchronous replication, and
tell applications that it has been successful even when it
hasn't been, as long as there's a line in the server log to
record the lie.

Most people having such a position I've talked to have held that
position because they thought synchronous replication would mean
that apply (and thus visibility) would also be synchronous. Is
that different from your experience?

I haven't pursued it that far because we don't have
maybe-synchronous mode yet and seem unlikely to ever support it.
I'm not sure why that use-case is any better than any other.  You
still would never really know whether the data read is current.  If
we were to implement this, the supposedly synchronous replica could
be out-of-date by any arbitrary amount of time (from milliseconds
to months).  (Consider what could happen if the replication
connection authorizations got messed up while application
connections to the replica were fine.)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/08/2014 01:55 PM, Tom Lane wrote:

Sync mode is about providing a guarantee that the data exists on more than
one server *before* we tell the client it's committed. If you don't need
that guarantee, you shouldn't be using sync mode. If you do need it,
it's not clear to me why you'd suddenly not need it the moment the going
actually gets tough.

As I understand it what is being suggested is that if a subscriber or
target goes down, then the master will just sit there and wait. When I
read that, I read that the master will no longer process write
transactions. If I am wrong in that understanding then cool. If I am not
then that is a serious problem with a production scenario. There is an
expectation that a master will continue to function if the target is
down, synchronous or not.

Sincerely,

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers