Re: Configuring synchronous replication

Log Message:
-----------
Use a latch to make startup process wake up and replay immediately when
new WAL arrives via streaming replication. This reduces the latency, and
also allows us to use a longer polling interval, which is good for energy
efficiency.

We still need to poll to check for the appearance of a trigger file, but
the interval is now 5 seconds (instead of 100ms), like when waiting for
a new WAL segment to appear in WAL archive.

Modified Files:
--------------
pgsql/src/backend/access/transam:
xlog.c (r1.434 -> r1.435)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.434&r2=1.435)
pgsql/src/backend/replication:
walreceiver.c (r1.16 -> r1.17)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/replication/walreceiver.c?r1=1.16&r2=1.17)
pgsql/src/include/access:
xlog.h (r1.116 -> r1.117)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/access/xlog.h?r1=1.116&r2=1.117)

On Wed, Sep 15, 2010 at 7:35 PM, Heikki Linnakangas
<heikki(at)postgresql(dot)org> wrote:
> Log Message:
> -----------
> Use a latch to make startup process wake up and replay immediately when
> new WAL arrives via streaming replication. This reduces the latency, and
> also allows us to use a longer polling interval, which is good for energy
> efficiency.
>
> We still need to poll to check for the appearance of a trigger file, but
> the interval is now 5 seconds (instead of 100ms), like when waiting for
> a new WAL segment to appear in WAL archive.

Good work!

+ /*
+ * Take ownership of the wakup latch if we're going to sleep during
+ * recovery.
+ */

I found a typo: s/wakup/wakeup/

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On 15 September 2010 11:35, Heikki Linnakangas <heikki(at)postgresql(dot)org> wrote:
> Log Message:
> -----------
> Use a latch to make startup process wake up and replay immediately when
> new WAL arrives via streaming replication. This reduces the latency, and
> also allows us to use a longer polling interval, which is good for energy
> efficiency.
>
> We still need to poll to check for the appearance of a trigger file, but
> the interval is now 5 seconds (instead of 100ms), like when waiting for
> a new WAL segment to appear in WAL archive.
>
> Modified Files:
> --------------
>    pgsql/src/backend/access/transam:
>        xlog.c (r1.434 -> r1.435)
>        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.434&r2=1.435)
>    pgsql/src/backend/replication:
>        walreceiver.c (r1.16 -> r1.17)
>        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/replication/walreceiver.c?r1=1.16&r2=1.17)
>    pgsql/src/include/access:
>        xlog.h (r1.116 -> r1.117)
>        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/access/xlog.h?r1=1.116&r2=1.117)
>
> --

+ * We don't need the latch anymore. It's not strictly necessary to disown
+ * it, but let's do it for the sake of tidyness.
+ */

s/tidyness/tidiness/

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

heikki(at)postgresql(dot)org (Heikki Linnakangas) writes:
> Log Message:
> -----------
> Use a latch to make startup process wake up and replay immediately when
> new WAL arrives via streaming replication. This reduces the latency, and
> also allows us to use a longer polling interval, which is good for energy
> efficiency.

> We still need to poll to check for the appearance of a trigger file, but
> the interval is now 5 seconds (instead of 100ms), like when waiting for
> a new WAL segment to appear in WAL archive.

This is just speculation at this point, because I haven't taken time
to think through the details, but couldn't we improve on that still
further?

There are always going to be some conditions that we have to poll for,
in particular death of the postmaster (since Unix unaccountably fails
to provide a SIGPARNT signal condition :-(). However postmaster death
isn't really something that needs an instant response IMO. I would like
to get the wakeup-and-poll interval for our background processes down to
a minute or so; so far as postmaster death goes that doesn't seem like
an unacceptable response time.

So I'm wondering if we couldn't eliminate the five-second sleep
requirement here too. It's problematic anyhow, since somebody looking
for energy efficiency will still feel it's too short, while somebody
concerned about fast failover will feel it's too long. Could the
standby triggering protocol be modified so that it involves sending a
signal, not just creating a file? (One issue is that it's not clear
what that'd translate to on Windows.)

regards, tom lane

On 15/09/10 14:14, Fujii Masao wrote:
> + /*
> + * Take ownership of the wakup latch if we're going to sleep during
> + * recovery.
> + */
>
> I found a typo: s/wakup/wakeup/
>

On 15/09/10 14:48, Thom Brown wrote:
> + * We don't need the latch anymore. It's not strictly necessary to disown
> + * it, but let's do it for the sake of tidyness.
> + */
>
> s/tidyness/tidiness/

Fixed both of these. Thank you.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On 15/09/10 16:55, Tom Lane wrote:
> So I'm wondering if we couldn't eliminate the five-second sleep
> requirement here too. It's problematic anyhow, since somebody looking
> for energy efficiency will still feel it's too short, while somebody
> concerned about fast failover will feel it's too long.

Yep.

> Could the
> standby triggering protocol be modified so that it involves sending a
> signal, not just creating a file?

Seems reasonable, at least if we still provide an option for more
frequent polling and no need to send signal.

> (One issue is that it's not clear what that'd translate to on Windows.)

pg_ctl failover ? At the moment, the location of the trigger file is
configurable, but if we accept a constant location like
"$PGDATA/failover" pg_ctl could do the whole thing, create the file and
send signal. pg_ctl on Window already knows how to send the "signal" via
the named pipe signal emulation.

Fujii-san suggested that we might have a user-defined function for
triggering failover as well. That's also handy, but it's not a
replacement because it only works in hot standby mode.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, 2010-09-15 at 20:14 +0900, Fujii Masao wrote:
> On Wed, Sep 15, 2010 at 7:35 PM, Heikki Linnakangas
> <heikki(at)postgresql(dot)org> wrote:
> > Log Message:
> > -----------
> > Use a latch to make startup process wake up and replay immediately when
> > new WAL arrives via streaming replication. This reduces the latency, and
> > also allows us to use a longer polling interval, which is good for energy
> > efficiency.
> >
> > We still need to poll to check for the appearance of a trigger file, but
> > the interval is now 5 seconds (instead of 100ms), like when waiting for
> > a new WAL segment to appear in WAL archive.
>
> Good work!

No, not good work.

You both know very well that I'm working on this area also and these
commits are not agreed... yet. They might not be contended but they are
very likely to break my patch, again.

Please desist while we resolve which are the good ideas and which are
not. We won't know that if you keep breaking other people's patches in a
stream of commits that prevent anybody completing other options.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, Sep 15, 2010 at 03:35:30PM +0100, Simon Riggs wrote:
> On Wed, 2010-09-15 at 20:14 +0900, Fujii Masao wrote:
> > On Wed, Sep 15, 2010 at 7:35 PM, Heikki Linnakangas
> > <heikki(at)postgresql(dot)org> wrote:
> > > Log Message:
> > > -----------
> > > Use a latch to make startup process wake up and replay immediately when
> > > new WAL arrives via streaming replication. This reduces the latency, and
> > > also allows us to use a longer polling interval, which is good for energy
> > > efficiency.
> > >
> > > We still need to poll to check for the appearance of a trigger file, but
> > > the interval is now 5 seconds (instead of 100ms), like when waiting for
> > > a new WAL segment to appear in WAL archive.
> >
> > Good work!
>
> No, not good work.
>
> You both know very well that I'm working on this area also and these
> commits are not agreed... yet. They might not be contended but they are
> very likely to break my patch, again.
>
> Please desist while we resolve which are the good ideas and which are
> not. We won't know that if you keep breaking other people's patches in a
> stream of commits that prevent anybody completing other options.

Simon,

No matter how many times you try, you are not going to get a license
to stop all work on anything you might chance to think about. It is
quite simply never going to happen, so you need to back off.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Wed, 2010-09-15 at 07:59 -0700, David Fetter wrote:
> On Wed, Sep 15, 2010 at 03:35:30PM +0100, Simon Riggs wrote:

> > Please desist while we resolve which are the good ideas and which are
> > not. We won't know that if you keep breaking other people's patches in a
> > stream of commits that prevent anybody completing other options.

> No matter how many times you try, you are not going to get a license
> to stop all work on anything you might chance to think about. It is
> quite simply never going to happen, so you need to back off.

I agree that asking people to stop work is not OK. However, I haven't
asked for development work to stop, only that commits into that area
stop until proper debate has taken place. Those might be minor commits,
but they might not. Had I made those commits, they would have been
called premature by others also.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Wed, 2010-09-15 at 20:14 +0900, Fujii Masao wrote:
>> Good work!

> No, not good work.

> You both know very well that I'm working on this area also and these
> commits are not agreed... yet. They might not be contended but they are
> very likely to break my patch, again.

Simon, you can't just command the tide to stop coming in while you do
something that no one else can see. This patch is perfectly reasonable,
not at all invasive, and a clear improvement over the way the code was
before. If you can improve on it further, fine, but we're not going to
declare the code off-limits while waiting for an unspecified patch with
no firm delivery date.

regards, tom lane

On Wed, Sep 15, 2010 at 11:24 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I agree that asking people to stop work is not OK. However, I haven't
> asked for development work to stop, only that commits into that area
> stop until proper debate has taken place. Those might be minor commits,
> but they might not. Had I made those commits, they would have been
> called premature by others also.

I do not believe that Heikki has done anything inappropriate. We've
spent weeks discussing the latch facility and its various
applications.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Wed, 2010-09-15 at 11:25 -0400, Tom Lane wrote:
> ... an unspecified patch with no firm delivery date.

I'm happy to post my current work, if it's considered helpful. The sole
intent of that work is to help the community understand the benefits of
the proposals I have made, so perhaps this patch does serve that
purpose.

The attached patch compiles, but I wouldn't bother trying to run it yet.
I'm still wading through the latch rewrite. It probably doesn't apply
cleanly to head anymore either, hence discussion.

I wouldn't normally waste people's time by posting a non-working patch,
the majority of the code is in about the right place of execution. There
aren't any unclear aspects in the design, so its worth looking at.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, 2010-09-15 at 12:45 -0400, Robert Haas wrote:
> On Wed, Sep 15, 2010 at 11:24 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > I agree that asking people to stop work is not OK. However, I haven't
> > asked for development work to stop, only that commits into that area
> > stop until proper debate has taken place. Those might be minor commits,
> > but they might not. Had I made those commits, they would have been
> > called premature by others also.
>
> I do not believe that Heikki has done anything inappropriate. We've
> spent weeks discussing the latch facility and its various
> applications.

Sounds reasonable, but my comments were about this commit, not the one
that happened on Saturday. This patch was posted about 32 hours ago, and
the commit need not have taken place yet. If I had posted such a patch
and committed it knowing other work is happening in that area we both
know that you would have objected.

It's not actually a major issue, but at some point I have to ask for no
more commits, so Fujii and I can finish our patches, compare and
contrast, so the best ideas can get into Postgres.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, Sep 15, 2010 at 1:30 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Wed, 2010-09-15 at 12:45 -0400, Robert Haas wrote:
>> On Wed, Sep 15, 2010 at 11:24 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> > I agree that asking people to stop work is not OK. However, I haven't
>> > asked for development work to stop, only that commits into that area
>> > stop until proper debate has taken place. Those might be minor commits,
>> > but they might not. Had I made those commits, they would have been
>> > called premature by others also.
>>
>> I do not believe that Heikki has done anything inappropriate.  We've
>> spent weeks discussing the latch facility and its various
>> applications.
>
> Sounds reasonable, but my comments were about this commit, not the one
> that happened on Saturday. This patch was posted about 32 hours ago, and
> the commit need not have taken place yet. If I had posted such a patch
> and committed it knowing other work is happening in that area we both
> know that you would have objected.

I've often felt that we ought to have a bit more delay between when
committers post patches and when they commit them. I was told 24
hours and I've seen cases where people haven't even waited that long.
On the other hand, if we get to strict about it, it can easily get to
the point where it just gets in the way of progress, and certainly
some patches are far more controversial than others. So I don't know
what the best thing to do is. Still, I have to admit that I feel
fairly positive about the direction we're going with this particular
patch. Clearing away these peripheral issues should make it easier
for us to have a rational discussion about the core issues around how
this is going to be configured and actually work at the protocol
level.

> It's not actually a major issue, but at some point I have to ask for no
> more commits, so Fujii and I can finish our patches, compare and
> contrast, so the best ideas can get into Postgres.

I don't think anyone is prepared to agree to that. I think that
everyone is prepared to accept a limited amount of further delay in
pressing forward with the main part of sync rep, but I expect that no
one will be willing to freeze out incremental improvements in the
meantime, even if it does induce a certain amount of rebasing. It's
also worth noting that Fujii Masao's patch has been around for months,
and yours isn't finished yet. That's not to say that we don't want to
consider your ideas, because we do: and you've had more than your
share of good ones. At the same time, it would be unfair and
unreasonable to expect work on a patch that is done, and has been done
for some time, to wait on one that isn't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On 15/09/10 20:58, Robert Haas wrote:
> On Wed, Sep 15, 2010 at 1:30 PM, Simon Riggs<simon(at)2ndquadrant(dot)com> wrote:
>> On Wed, 2010-09-15 at 12:45 -0400, Robert Haas wrote:
>>> On Wed, Sep 15, 2010 at 11:24 AM, Simon Riggs<simon(at)2ndquadrant(dot)com> wrote:
>>>> I agree that asking people to stop work is not OK. However, I haven't
>>>> asked for development work to stop, only that commits into that area
>>>> stop until proper debate has taken place. Those might be minor commits,
>>>> but they might not. Had I made those commits, they would have been
>>>> called premature by others also.
>>>
>>> I do not believe that Heikki has done anything inappropriate. We've
>>> spent weeks discussing the latch facility and its various
>>> applications.
>>
>> Sounds reasonable, but my comments were about this commit, not the one
>> that happened on Saturday. This patch was posted about 32 hours ago, and
>> the commit need not have taken place yet. If I had posted such a patch
>> and committed it knowing other work is happening in that area we both
>> know that you would have objected.
>
> I've often felt that we ought to have a bit more delay between when
> committers post patches and when they commit them. I was told 24
> hours and I've seen cases where people haven't even waited that long.
> On the other hand, if we get to strict about it, it can easily get to
> the point where it just gets in the way of progress, and certainly
> some patches are far more controversial than others. So I don't know
> what the best thing to do is.

With anything non-trivial, I try to "sleep on it" before committing.
More with complicated patches, but it's really up to your own comfort
level with the patch, and whether you think anyone might have different
opinions on it. I don't mind quick commits if it's something that has
been discussed in the past and the committer thinks it's
non-controversial. There's always the option of complaining afterwards.
If it comes to that, though, it wasn't really ripe for committing yet.
(That doesn't apply to gripes about typos or something like that,
because that happens to me way too often ;-) )

> Still, I have to admit that I feel
> fairly positive about the direction we're going with this particular
> patch. Clearing away these peripheral issues should make it easier
> for us to have a rational discussion about the core issues around how
> this is going to be configured and actually work at the protocol
> level.

Yeah, I don't think anyone has any qualms about the substance of these
patches.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, 2010-09-15 at 13:58 -0400, Robert Haas wrote:
> > It's not actually a major issue, but at some point I have to ask for
> no
> > more commits, so Fujii and I can finish our patches, compare and
> > contrast, so the best ideas can get into Postgres.
>
> I don't think anyone is prepared to agree to that. I think that
> everyone is prepared to accept a limited amount of further delay in
> pressing forward with the main part of sync rep, but I expect that no
> one will be willing to freeze out incremental improvements in the
> meantime, even if it does induce a certain amount of rebasing.

> It's
> also worth noting that Fujii Masao's patch has been around for months,
> and yours isn't finished yet. That's not to say that we don't want to
> consider your ideas, because we do: and you've had more than your
> share of good ones. At the same time, it would be unfair and
> unreasonable to expect work on a patch that is done, and has been done
> for some time, to wait on one that isn't.

I understand your viewpoint there. I'm sure we all agree sync rep is a
very important feature that must get into the next release.

The only reason my patch exists is because debate around my ideas was
ruled out on various grounds. One of those was it would take so long to
develop we shouldn't risk not getting sync rep in this release. I am
amenable to such arguments (and I make the same one on MERGE, btw, where
I am getting seriously worried) but the reality is that there is
actually very little code here and we can definitely do this, whatever
ideas we pick. I've shown this by providing an almost working version in
about 4 days work. Will finishing it help?

We definitely have the time, so the question is, what are the best
ideas? We must discuss the ideas properly, not just plough forwards
claiming time pressure when it isn't actually an issue at all. We *need*
to put the tools down and talk in detail about the best way forwards.

Before, I had no patch. Now mine "isn't finished". At what point will my
ideas be reviewed without instant dismissal? If we accept your seniority
argument, then "never" because even if I finish it you'll say "Fujii was
there first".

If who mentioned it first was important, then I'd say I've been
discussing this for literally years (late 2006) and have regularly
explained the benefits of the master-side approach I've outlined on list
every time this has come up (every few months). I have also explained
the implementation details many times as well an I'm happy to say that
latches are pretty much exactly what I described earlier. (I called them
LSN queues, similar to lwlocks, IIRC). But thats not the whole deal.

If we simply wanted a patch that was "done" we would have gone with
Zoltan's wouldn't we, based on the seniority argument you use above?
Zoltan's patch didn't perform well at all. Fujii's performs much better.
However, my proposed approach offers even better performance, so
whatever argument you use to include Fujii's also applies to mine
doesn't it? But that's silly and divisive, its not about who's patch
"wins" is it?

Do we have to benchmark multiple patches to prove which is best? If
that's the criteria I'll finish my patch and demonstrate that.

But it doesn't make sense to start committing pieces of Fujii's patch,
so that I can't ever keep up and as a result "Simon never finished his
patch, but it sounded good".

Next steps should be: tools down, discuss what to do. Then go forwards.

We have time, so lets discuss all of the ideas on the table not just
some of them.

For me this is not about the number or names of parameters, its about
master-side control of sync rep and having very good performance.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, Sep 15, 2010 at 3:18 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Will finishing it help?

Yes, I expect that to help a lot.

> Before, I had no patch. Now mine "isn't finished". At what point will my
> ideas be reviewed without instant dismissal? If we accept your seniority
> argument, then "never" because even if I finish it you'll say "Fujii was
> there first".

I said very clearly in my previous email that "I think that everyone
is prepared to accept a limited amount of further delay in pressing
forward with the main part of sync rep". In other words, I think
everyone is willing to consider your ideas provided that they are
submitted in a form which everyone can understand and think through
sometime soon. I am not, nor do I think anyone is, saying that we
don't wish to consider your ideas. I'm actually really pleased that
you are only a day or two from having a working patch. It can be much
easier to conceptualize a patch than to find the time to finish it
(unfortunately, this problem has overtaken me rather badly in the last
few weeks, which is why I have no new patches in this CommitFest) and
if you can finish it up and get it out in front of everyone I expect
that to be a good thing for this feature and our community.

> Do we have to benchmark multiple patches to prove which is best? If
> that's the criteria I'll finish my patch and demonstrate that.

I was thinking about that earlier today. I think it's definitely
possible that we'll need to do some benchmarking, although I expect
that people will want to read the code first.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Wed, Sep 15, 2010 at 11:14 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> (One issue is that it's not clear what that'd translate to on Windows.)
>
> pg_ctl failover ? At the moment, the location of the trigger file is
> configurable, but if we accept a constant location like "$PGDATA/failover"
> pg_ctl could do the whole thing, create the file and send signal. pg_ctl on
> Window already knows how to send the "signal" via the named pipe signal
> emulation.

Right.

> Fujii-san suggested that we might have a user-defined function for
> triggering failover as well.

The attached patch introduces such a user-defined function. This is
useful especially when clusterware like pgpool-II is located in remote
server since it can trigger failover without using something like ssh.

> That's also handy, but it's not a replacement
> because it only works in hot standby mode.

Yep.

And we should increase the sleep time in walsender's poll loop (i.e.,
increase the default value of wal_sender_delay) too? Currently it's
very small, 200ms.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Wed, Sep 15, 2010 at 10:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> So I'm wondering if we couldn't eliminate the five-second sleep
> requirement here too.

That would make the shutdown time longer since startup process currently
cannot respond to SIGTERM and SIGHUP immediately. To avoid this, I think
that we should change the signal handlers of startup process so that they
call WakeupRecovery.

The attached patch makes StartupProcSigHupHandler and StartupProcShutdownHandler
call WakeupRecovery.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Thu, Sep 16, 2010 at 1:23 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> Fujii-san suggested that we might have a user-defined function for
>> triggering failover as well.
>
> The attached patch introduces such a user-defined function. This is
> useful especially when clusterware like pgpool-II is located in remote
> server since it can trigger failover without using something like ssh.

I forgot to check if the caller of that function has superuser permission.
Here is the updated version.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Thu, Sep 16, 2010 at 4:18 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> We definitely have the time, so the question is, what are the best
> ideas?

Before advancing the review of each patch, we must determine what
should be committed in 9.1, and what's in this CF.

"Synchronization level on per-transaction" feature is included in Simon's
patch, but not in mine. This is most important difference, which would
have wide-reaching impact on the implementation, e.g., protocol between
walsender and walreceiver. So, at first we should determine whether we'll
commit the feature in 9.1. Then we need to determine how far we should
implement in this CF. Thought?

Each patch provides "synchronization level on per-standby" feature. In
Simon's patch, that level is specified in the standbys's recovery.conf.
In mine, it's in the master's standbys.conf. I think that the former is simpler.
But if we support the capability to register the standbys, the latter would
be required. Which is the best?

Simon's patch seems to include simple quorum commit feature (correct
me if I'm wrong). That is, when there are multiple synchronous standbys,
the master waits until ACK has arrived from at least one standby. OTOH,
in my patch, the master waits until ACK has arrived from all the synchronous
standbys. Which should we choose? I think that we should commit my
straightforward approach first, and enable the quorum commit on that.
Thought?

Simon proposes to invoke walwriter in the standby. This is not included
in my patch, but looks good idea. ISTM that this is not essential feature
for synchronous replication, so how about detachmenting of the walwriter
part from the patch and reviewing it independently?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Fri, 2010-09-17 at 14:33 +0900, Fujii Masao wrote:
> On Thu, Sep 16, 2010 at 4:18 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > We definitely have the time, so the question is, what are the best
> > ideas?
>
> Before advancing the review of each patch, we must determine what
> should be committed in 9.1, and what's in this CF.

Thank you for starting the discussion.

> "Synchronization level on per-transaction" feature is included in Simon's
> patch, but not in mine. This is most important difference

Agreed. It's also a very important option for users.

> which would
> have wide-reaching impact on the implementation, e.g., protocol between
> walsender and walreceiver. So, at first we should determine whether we'll
> commit the feature in 9.1. Then we need to determine how far we should
> implement in this CF. Thought?

Yes, sync rep specified per-transaction changes many things at a low
level. Basically, we have a choice of two mostly incompatible
implementations, plus some other options common to both.

There is no danger that we won't commit in 9.1. We have time for
discussion and thought. We also have time for performance testing and
since many of my design proposals are performance related that seems
essential to properly reviewing the patches.

I don't think we can determine how far to implement without considering
both approaches in detail. With regard to your points below, I don't
think any of those points could be committed first.

> Each patch provides "synchronization level on per-standby" feature. In
> Simon's patch, that level is specified in the standbys's recovery.conf.
> In mine, it's in the master's standbys.conf. I think that the former is simpler.
> But if we support the capability to register the standbys, the latter would
> be required. Which is the best?

Either approach is OK for me. Providing both options is also possible.
My approach was just less code and less change to existing mechanisms,
so I did it that way.

There are some small optimisations possible on standby if the standby
knows what role it's being asked to play. It doesn't matter to me
whether we let standby tell master or master tell standby and the code
is about the same either way.

> Simon's patch seems to include simple quorum commit feature (correct
> me if I'm wrong). That is, when there are multiple synchronous standbys,
> the master waits until ACK has arrived from at least one standby. OTOH,
> in my patch, the master waits until ACK has arrived from all the synchronous
> standbys. Which should we choose? I think that we should commit my
> straightforward approach first, and enable the quorum commit on that.
> Thought?

Yes, my approach is simple. For those with Oracle knowledge, my approach
(first-reply-releases-waiter) is equivalent to Oracle's Maximum
Protection mode (= 'fsync' in my design). Providing even higher levels
of protection would not be the most common case.

Your approach of waiting for all replies is much slower and requires
more complex code, since we need to track intermediate states. It also
has additional complexities of behaviour, such as how long do we wait
for second acknowledgement when we already have one, and what happens
when a second ack is not received? More failure modes == less stable.
ISTM that it would require more effort to do this also, since every ack
needs to check all WAL sender data to see if it is the last ack. None of
that seems straightforward.

I don't agree we should commit your approach to that aspect.

In my proposal, such additional features would be possible as a plugin.
The majority of users would not this facility and the plugin leaves the
way open for high-end users that need this.

> Simon proposes to invoke walwriter in the standby. This is not included
> in my patch, but looks good idea. ISTM that this is not essential feature
> for synchronous replication, so how about detachmenting of the walwriter
> part from the patch and reviewing it independently?

I regard it as an essential feature for implementing 'recv' mode of sync
rep, which is the fastest mode. At present WALreceiver does all of
these: receive, write and fsync. Of those the fsync is the slowest and
increases response time significantly.

Of course 'recv' option doesn't need to be part of first commit, but
splitting commits doesn't seem likely to make this go quicker or easier
in the early stages. In particular, splitting some features out could
make it much harder to put back in again later. That point is why my
patch even exists.

I would like to express my regret that the main feature proposal from me
necessitates low level changes that cause our two patches to be in
conflict. Nobody should take this as a sign that there is a personal or
professional problem between Fujii-san and myself.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

(changed subject again.)

On 17/09/10 10:06, Simon Riggs wrote:
> I don't think we can determine how far to implement without considering
> both approaches in detail. With regard to your points below, I don't
> think any of those points could be committed first.

Yeah, I think we need to decide on the desired feature set first, before
we dig deeper into the the patches. The design and implementation will
fall out of that.

That said, there's a few small things that can be progressed regardless
of the details of synchronous replication. There's the changes to
trigger failover with a signal, and it seems that we'll need some libpq
changes to allow acknowledgments to be sent back to the master
regardless of the rest of the design. We can discuss those in separate
threads in parallel.

So the big question is what the user interface looks like. How does one
configure synchronous replication, and what options are available.
Here's a list of features that have been discussed. We don't necessarily
need all of them in the first phase, but let's avoid painting ourselves
in the corner.

* Support multiple standbys with various synchronization levels.

* What happens if a synchronous standby isn't connected at the moment?
Return immediately vs. wait forever.

* Per-transaction control. Some transactions are important, others are not.

* Quorum commit. Wait until n standbys acknowledge. n=1 and n=all
servers can be seen as important special cases of this.

* async, recv, fsync and replay levels of synchronization.

So what should the user interface be like? Given the 1st and 2nd
requirement, we need standby registration. If some standbys are
important and others are not, the master needs to distinguish between
them to be able to determine that a transaction is safely delivered to
the important standbys.

For per-transaction control, ISTM it would be enough to have a simple
user-settable GUC like synchronous_commit. Let's call it
"synchronous_replication_commit" for now. For non-critical transactions,
you can turn it off. That's very simple for developers to understand and
use. I don't think we need more fine-grained control than that at
transaction level, in all the use cases I can think of you have a stream
of important transactions, mixed with non-important ones like log
messages that you want to finish fast in a best-effort fashion. I'm
actually tempted to tie that to the existing synchronous_commit GUC, the
use case seems exactly the same.

OTOH, if we do want fine-grained per-transaction control, a simple
boolean or even an enum GUC doesn't really cut it. For truly
fine-grained control you want to be able to specify exceptions like
"wait until this is replayed in slave named 'reporting'" or 'don't wait
for acknowledgment from slave named 'uk-server'". With standby
registration, we can invent a syntax for specifying overriding rules in
the transaction. Something like SET replication_exceptions =
'reporting=replay, uk-server=async'.

For the control between async/recv/fsync/replay, I like to think in
terms of
a) asynchronous vs synchronous
b) if it's synchronous, how synchronous is it? recv, fsync or replay?

I think it makes most sense to set sync vs. async in the master, and the
level of synchronicity in the slave. Although I have sympathy for the
argument that it's simpler if you configure it all from the master side
as well.

Putting all of that together. I think Fujii-san's standby.conf is pretty
close. What it needs is the additional GUC for transaction-level control.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2010-09-17 at 11:09 +0300, Heikki Linnakangas wrote:
> That said, there's a few small things that can be progressed
> regardless of the details of synchronous replication. There's the
> changes to trigger failover with a signal, and it seems that we'll
> need some libpq changes to allow acknowledgments to be sent back to
> the master regardless of the rest of the design. We can discuss those
> in separate threads in parallel.

Agree to both of those points.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> * Support multiple standbys with various synchronization levels.
>
> * What happens if a synchronous standby isn't connected at the moment?
> Return immediately vs. wait forever.
>
> * Per-transaction control. Some transactions are important, others are not.
>
> * Quorum commit. Wait until n standbys acknowledge. n=1 and n=all servers
> can be seen as important special cases of this.
>
> * async, recv, fsync and replay levels of synchronization.
>
> So what should the user interface be like? Given the 1st and 2nd
> requirement, we need standby registration. If some standbys are important
> and others are not, the master needs to distinguish between them to be able
> to determine that a transaction is safely delivered to the important
> standbys.

Well the 1st point can be handled in a distributed fashion, where the
sync level is setup at the slave. Ditto for second point, you can get
the exact same behavior control attached to the quorum facility.

What I think you're description is missing is the implicit feature that
you want to be able to setup the "ignore-or-wait" failure behavior per
standby. I'm not sure we need that, or more precisely that we need to
have that level of detail in the master's setup.

Maybe what we need instead is a more detailed quorum facility, but as
you're talking about something similar later in the mail, let's follow
you.

> For per-transaction control, ISTM it would be enough to have a simple
> user-settable GUC like synchronous_commit. Let's call it
> "synchronous_replication_commit" for now. For non-critical transactions, you
> can turn it off. That's very simple for developers to understand and use. I
> don't think we need more fine-grained control than that at transaction
> level, in all the use cases I can think of you have a stream of important
> transactions, mixed with non-important ones like log messages that you want
> to finish fast in a best-effort fashion. I'm actually tempted to tie that to
> the existing synchronous_commit GUC, the use case seems exactly the
> same.

Well, that would be an over simplification. In my applications I set the
"sessions" transaction with synchronous_commit = off, but the business
transactions to synchronous_commit = on. Now, among those last, I have
backoffice editing and money transactions. I'm not willing to be forced
to endure the same performance penalty for both when I know the
distributed durability needs aren't the same.

> OTOH, if we do want fine-grained per-transaction control, a simple boolean
> or even an enum GUC doesn't really cut it. For truly fine-grained control
> you want to be able to specify exceptions like "wait until this is replayed
> in slave named 'reporting'" or 'don't wait for acknowledgment from slave
> named 'uk-server'". With standby registration, we can invent a syntax for
> specifying overriding rules in the transaction. Something like SET
> replication_exceptions = 'reporting=replay, uk-server=async'.

Then you want to be able to have more than one reporting server and need
only one of them at the "replay" level, but you don't need to know which
it is. Or on the contrary you have a failover server and you want to be
sure this one is at the replay level whatever happens.

Then you want topology flexibility: you need to be able to replace a
reporting server with another, ditto for the failover one.

Did I tell you my current thinking on how to tackle that yet? :) Using a
distributed setup, where each slave has a weight (several votes per
transaction) and a level offering would allow that I think.

Now something similar to your idea that I can see a need for is being
able to have a multi-part quorum target: when you currently say that you
want 2 votes for sync, you would be able to say you want 2 votes for
recv, 2 for fsync and 1 for replay. Remember that any slave is setup to
offer only one level of synchronicity but can offer multiple votes.

How this would look like in the setup? Best would be to register the
different service levels your application need. Time to bikeshed a
little?

sync_rep_services = {critical: recv=2, fsync=2, replay=1;
important: fsync=3;
reporting: recv=2, apply=1}

Well you get the idea, it could maybe get stored on a catalog somewhere
with nice SQL commands etc. The goal is then to be able to handle a much
simpler GUC in the application, sync_rep_service = important for
example. Reserved label would be off, the default value.

> For the control between async/recv/fsync/replay, I like to think in terms of
> a) asynchronous vs synchronous
> b) if it's synchronous, how synchronous is it? recv, fsync or replay?

Same here.

> I think it makes most sense to set sync vs. async in the master, and the
> level of synchronicity in the slave.

Yeah, exactly.

If you add a weight to each slave then a quorum commit, you don't change
the implementation complexity and you offer lot of setup flexibility. If
the slave sync-level and weight are SIGHUP, then it even become rather
easy to switch roles online or to add new servers or to organise a
maintenance window — the quorum to reach is a per-transaction GUC on the
master, too, right?

Regards,
--
dim

On Fri, 2010-09-17 at 09:15 +0100, Simon Riggs wrote:
> On Fri, 2010-09-17 at 11:09 +0300, Heikki Linnakangas wrote:
> > That said, there's a few small things that can be progressed
> > regardless of the details of synchronous replication. There's the
> > changes to trigger failover with a signal, and it seems that we'll
> > need some libpq changes to allow acknowledgments to be sent back to
> > the master regardless of the rest of the design. We can discuss those
> > in separate threads in parallel.
>
> Agree to both of those points.

But I don't agree that those things should be committed just yet.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 17/09/10 12:10, Dimitri Fontaine wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> * Support multiple standbys with various synchronization levels.
>>
>> * What happens if a synchronous standby isn't connected at the moment?
>> Return immediately vs. wait forever.
>>
>> * Per-transaction control. Some transactions are important, others are not.
>>
>> * Quorum commit. Wait until n standbys acknowledge. n=1 and n=all servers
>> can be seen as important special cases of this.
>>
>> * async, recv, fsync and replay levels of synchronization.
>>
>> So what should the user interface be like? Given the 1st and 2nd
>> requirement, we need standby registration. If some standbys are important
>> and others are not, the master needs to distinguish between them to be able
>> to determine that a transaction is safely delivered to the important
>> standbys.
>
> Well the 1st point can be handled in a distributed fashion, where the
> sync level is setup at the slave.

If the synchronicity is configured in the standby, how does the master
know that there's a synchronous slave out there that it should wait for,
if that slave isn't connected at the moment?

>> OTOH, if we do want fine-grained per-transaction control, a simple boolean
>> or even an enum GUC doesn't really cut it. For truly fine-grained control
>> you want to be able to specify exceptions like "wait until this is replayed
>> in slave named 'reporting'" or 'don't wait for acknowledgment from slave
>> named 'uk-server'". With standby registration, we can invent a syntax for
>> specifying overriding rules in the transaction. Something like SET
>> replication_exceptions = 'reporting=replay, uk-server=async'.
>
> Then you want to be able to have more than one reporting server and need
> only one of them at the "replay" level, but you don't need to know which
> it is. Or on the contrary you have a failover server and you want to be
> sure this one is at the replay level whatever happens.
>
> Then you want topology flexibility: you need to be able to replace a
> reporting server with another, ditto for the failover one.
>
> Did I tell you my current thinking on how to tackle that yet? :) Using a
> distributed setup, where each slave has a weight (several votes per
> transaction) and a level offering would allow that I think.

Yeah, the quorum stuff. That's all good, but doesn't change the way you
would do per-transaction control. By specifying overrides on a
per-transaction basis, you can have as fine-grained control as you
possibly can. Anything you can specify in a configuration file can then
also be specified per-transaction with overrides. The syntax just needs
to be flexible enough.

If we buy into the concept of per-transaction exceptions, we can put
that issue aside for the moment, and just consider how to configure
things in a config file. Anything you can express in the config file can
also be expressed per-transaction with the exceptions GUC.

> Now something similar to your idea that I can see a need for is being
> able to have a multi-part quorum target: when you currently say that you
> want 2 votes for sync, you would be able to say you want 2 votes for
> recv, 2 for fsync and 1 for replay. Remember that any slave is setup to
> offer only one level of synchronicity but can offer multiple votes.
>
> How this would look like in the setup? Best would be to register the
> different service levels your application need. Time to bikeshed a
> little?
>
> sync_rep_services = {critical: recv=2, fsync=2, replay=1;
> important: fsync=3;
> reporting: recv=2, apply=1}
>
> Well you get the idea, it could maybe get stored on a catalog somewhere
> with nice SQL commands etc. The goal is then to be able to handle a much
> simpler GUC in the application, sync_rep_service = important for
> example. Reserved label would be off, the default value

So ignoring the quorum stuff for a moment, the general idea is that you
have predefined sets of configurations (or exceptions to the general
config) specified in a config file, and in the application you just
choose among those with "sync_rep_service=XXX". Yeah, I like that, it
allows you to isolate the details of the topology from the application.

> If you add a weight to each slave then a quorum commit, you don't change
> the implementation complexity and you offer lot of setup flexibility. If
> the slave sync-level and weight are SIGHUP, then it even become rather
> easy to switch roles online or to add new servers or to organise a
> maintenance window — the quorum to reach is a per-transaction GUC on the
> master, too, right?

I haven't bought into the quorum idea yet, but yeah, if we have quorum
support, then it would be configurable on a per-transaction basis too
with the above mechanism.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2010-09-17 at 11:09 +0300, Heikki Linnakangas wrote:
> (changed subject again.)
>
> On 17/09/10 10:06, Simon Riggs wrote:
> > I don't think we can determine how far to implement without considering
> > both approaches in detail. With regard to your points below, I don't
> > think any of those points could be committed first.
>
> Yeah, I think we need to decide on the desired feature set first, before
> we dig deeper into the the patches. The design and implementation will
> fall out of that.

Well, we've discussed these things many times and talking hasn't got us
very far on its own. We need measurements and neutral assessments.

The patches are simple and we have time.

This isn't just about UI, there are significant and important
differences between the proposals in terms of the capability and control
they offer.

I propose we develop both patches further and performance test them.
Many of the features I have proposed are performance related and people
need to be able to see what is important, and what is not. But not
through mere discussion, we need numbers to show which things matter and
which things don't. And those need to be derived objectively.

> * Support multiple standbys with various synchronization levels.
>
> * What happens if a synchronous standby isn't connected at the moment?
> Return immediately vs. wait forever.
>
> * Per-transaction control. Some transactions are important, others are not.
>
> * Quorum commit. Wait until n standbys acknowledge. n=1 and n=all
> servers can be seen as important special cases of this.
>
> * async, recv, fsync and replay levels of synchronization.

That's a reasonable starting list of points, there may be others.

> So what should the user interface be like? Given the 1st and 2nd
> requirement, we need standby registration. If some standbys are
> important and others are not, the master needs to distinguish between
> them to be able to determine that a transaction is safely delivered to
> the important standbys.

My patch provides those two requirements without standby registration,
so we very clearly don't "need" standby registration.

The question is do we want standby registration on master and if so,
why?

> For per-transaction control, ISTM it would be enough to have a simple
> user-settable GUC like synchronous_commit. Let's call it
> "synchronous_replication_commit" for now.

If you wish to change the name of the GUC away from the one I have
proposed, fine. Please note that aspect isn't important to me and I will
happily concede all such points to the majority view.

> For non-critical transactions,
> you can turn it off. That's very simple for developers to understand and
> use. I don't think we need more fine-grained control than that at
> transaction level, in all the use cases I can think of you have a stream
> of important transactions, mixed with non-important ones like log
> messages that you want to finish fast in a best-effort fashion.

Sounds like we're getting somewhere. See below.

> I'm
> actually tempted to tie that to the existing synchronous_commit GUC, the
> use case seems exactly the same.

http://archives.postgresql.org/pgsql-hackers/2008-07/msg01001.php
Check the date!

I think that particular point is going to confuse us. It will draw much
bike shedding and won't help us decide between patches. It's a nicety
that can be left to a time after we have the core feature committed.

> OTOH, if we do want fine-grained per-transaction control, a simple
> boolean or even an enum GUC doesn't really cut it. For truly
> fine-grained control you want to be able to specify exceptions like
> "wait until this is replayed in slave named 'reporting'" or 'don't wait
> for acknowledgment from slave named 'uk-server'". With standby
> registration, we can invent a syntax for specifying overriding rules in
> the transaction. Something like SET replication_exceptions =
> 'reporting=replay, uk-server=async'.
>
> For the control between async/recv/fsync/replay, I like to think in
> terms of
> a) asynchronous vs synchronous
> b) if it's synchronous, how synchronous is it? recv, fsync or replay?
>
> I think it makes most sense to set sync vs. async in the master, and the
> level of synchronicity in the slave. Although I have sympathy for the
> argument that it's simpler if you configure it all from the master side
> as well.

I have catered for such requests by suggesting a plugin that allows you
to implement that complexity without overburdening the core code.

This strikes me as an "ad absurdum" argument. Since the above
over-complexity would doubtless be seen as insane by Tom et al, it
attempts to persuade that we don't need recv, fsync and apply either.

Fujii has long talked about 4 levels of service also. Why change? I had
thought that part was pretty much agreed between all of us.

Without performance tests to demonstrate "why", these do sound hard to
understand. But we should note that DRBD offers recv ("B") and fsync
("C") as separate options. And Oracle implements all 3 of recv, fsync
and apply. Neither of them describe those options so simply and easily
as the way we are proposing with a 4 valued enum (with async as the
fourth option).

If we have only one option for sync_rep = 'on' which of recv | fsync |
apply would it implement? You don't mention that. Which do you choose?
For what reason do you make that restriction? The code doesn't get any
simpler, in my patch at least, from my perspective it would be a
restriction without benefit.

I no longer seek to persuade by words alone. The existence of my patch
means that I think that only measurements and tests will show why I have
been saying these things. We need performance tests. I'm not ready for
them today, but will be very soon. I suspect you aren't either since
from earlier discussions you didn't appear to have much about overall
throughput, only about response times for single transactions. I'm happy
to be proved wrong there.

> Putting all of that together. I think Fujii-san's standby.conf is pretty
> close.

> What it needs is the additional GUC for transaction-level control.

The difference between the patches is not a simple matter of a GUC.

My proposal allows a single standby to provide efficient replies to
multiple requested durability levels all at the same time. With
efficient use of network resources. ISTM that because the other patch
cannot provide that you'd like to persuade us that we don't need that,
ever. You won't sell me on that point, cos I can see lots of uses for
it.

Another use case for you:

* customer orders are important, but we want lots of them, so we use
recv mode for those.

* pricing data hardly ever changes, but when it does we need it to be
applied across the cluster so we don't get read mismatches, so those
rare transactions use apply mode.

If you don't want multiple modes at once, you don't need to use that
feature. But there is no reason to prevent people having the choice,
when a design exists that can provide it.

(A separate and later point, is that I would one day like to annotate
specific tables and functions with different modes, so a sysadmin can
point out which data is important at table level - which is what MySQL
provides by allowing choice of storage engine for particular tables.
Nobody cares about the specific engine, they care about the durability
implications of those choices. This isn't part of the current proposal,
just a later statement of direction.)

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> If the synchronicity is configured in the standby, how does the master know
> that there's a synchronous slave out there that it should wait for, if that
> slave isn't connected at the moment?

That's what quorum is trying to solve. The master knows how many votes
per sync level the transaction needs. If no slave is acknowledging any
vote, that's all you need to know to ROLLBACK (after the timeout),
right? — if setup says so, on the master.

> Yeah, the quorum stuff. That's all good, but doesn't change the way you
> would do per-transaction control.

That's when I bought in on the feature. It's all dynamic and
distributed, and it offers per-transaction control.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

On Fri, 2010-09-17 at 12:30 +0300, Heikki Linnakangas wrote:

> If the synchronicity is configured in the standby, how does the master
> know that there's a synchronous slave out there that it should wait for,
> if that slave isn't connected at the moment?

That isn't a question you need standby registration to answer.

In my proposal, the user requests a certain level of confirmation and
will wait until timeout to see if it is received. The standby can crash
and restart, come back and provide the answer, and it will still work.

So it is the user request that informs the master that there would
normally be a synchronous slave out there it should wait for.

So far, I have added the point that if a user requests a level of
confirmation that is currently unavailable, then it will use the highest
level of confirmation available now. That stops us from waiting for
timeout for every transaction we run if standby goes down hard, which
just freezes the application for long periods to no real benefit. It
also prevents applications from requesting durability levels the cluster
cannot satisfy, in the opinion of the sysadmin, since the sysadmin
specifies the max level on each standby.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 17/09/10 12:49, Simon Riggs wrote:
> This isn't just about UI, there are significant and important
> differences between the proposals in terms of the capability and control
> they offer.

Sure. The point of focusing on the UI is that the UI demonstrates what
capability and control a proposal offers.

>> So what should the user interface be like? Given the 1st and 2nd
>> requirement, we need standby registration. If some standbys are
>> important and others are not, the master needs to distinguish between
>> them to be able to determine that a transaction is safely delivered to
>> the important standbys.
>
> My patch provides those two requirements without standby registration,
> so we very clearly don't "need" standby registration.

It's still not clear to me how you would configure things like "wait for
ack from reporting slave, but not other slaves" or "wait until replayed
in the server on the west coast" in your proposal. Maybe it's possible,
but doesn't seem very intuitive, requiring careful configuration in both
the master and the slaves.

In your proposal, you also need to be careful not to connect e.g a test
slave with "synchronous_replication_service = apply" to the master, or
it will possible shadow a real production slave, acknowledging
transactions that are not yet received by the real slave. It's certainly
possible to screw up with standby registration too, but you have more
direct control of the master behavior in the master, instead of
distributing it across all slaves.

> The question is do we want standby registration on master and if so,
> why?

Well, aside from how to configure synchronous replication, standby
registration would help with retaining the right amount of WAL in the
master. wal_keep_segments doesn't guarantee that enough is retained, and
OTOH when all standbys are connected you retain much more than might be
required.

Giving names to slaves also allows you to view their status in the
master in a more intuitive format. Something like:

postgres=# SELECT * FROM pg_slave_status ;
name | connected | received | fsyncd | applied
------------+-----------+------------+------------+------------
reporting | t | 0/26000020 | 0/26000020 | 0/25550020
ha-standby | t | 0/26000020 | 0/26000020 | 0/26000020
testserver | f | | 0/15000020 |
(3 rows)

>> For the control between async/recv/fsync/replay, I like to think in
>> terms of
>> a) asynchronous vs synchronous
>> b) if it's synchronous, how synchronous is it? recv, fsync or replay?
>>
>> I think it makes most sense to set sync vs. async in the master, and the
>> level of synchronicity in the slave. Although I have sympathy for the
>> argument that it's simpler if you configure it all from the master side
>> as well.
>
> I have catered for such requests by suggesting a plugin that allows you
> to implement that complexity without overburdening the core code.

Well, plugins are certainly one possibility, but then we need to design
the plugin API. I've been thinking along the lines of a proxy, which can
implement whatever logic you want to decide when to send the
acknowledgment. With a proxy as well, if we push any features people
that want to a proxy or plugin, we need to make sure that the
proxy/plugin has all the necessary information available.

> This strikes me as an "ad absurdum" argument. Since the above
> over-complexity would doubtless be seen as insane by Tom et al, it
> attempts to persuade that we don't need recv, fsync and apply either.
>
> Fujii has long talked about 4 levels of service also. Why change? I had
> thought that part was pretty much agreed between all of us.

Now you lost me. I agree that we need 4 levels of service (at least
ultimately, not necessarily in the first phase).

> Without performance tests to demonstrate "why", these do sound hard to
> understand. But we should note that DRBD offers recv ("B") and fsync
> ("C") as separate options. And Oracle implements all 3 of recv, fsync
> and apply. Neither of them describe those options so simply and easily
> as the way we are proposing with a 4 valued enum (with async as the
> fourth option).
>
> If we have only one option for sync_rep = 'on' which of recv | fsync |
> apply would it implement? You don't mention that. Which do you choose?

You would choose between recv, fsync and apply in the slave, with a GUC.

> I no longer seek to persuade by words alone. The existence of my patch
> means that I think that only measurements and tests will show why I have
> been saying these things. We need performance tests.

I don't expect any meaningful differences in terms of performance
between any of the discussed options. The big question right now is what
features we provide and how they're configured. Performance will depend
primarily on the mode you use, and secondarily on the implementation of
the mode. It would be completely premature to do performance testing yet
IMHO.

>> Putting all of that together. I think Fujii-san's standby.conf is pretty
>> close.
>
>> What it needs is the additional GUC for transaction-level control.
>
> The difference between the patches is not a simple matter of a GUC.
>
> My proposal allows a single standby to provide efficient replies to
> multiple requested durability levels all at the same time. With
> efficient use of network resources. ISTM that because the other patch
> cannot provide that you'd like to persuade us that we don't need that,
> ever. You won't sell me on that point, cos I can see lots of uses for
> it.

Simon, how the replies are sent is an implementation detail I haven't
given much thought yet. The reason we delved into that discussion
earlier was that you seemed to contradict yourself with the claims that
you don't need to send more than one reply per transaction, and that the
standby doesn't need to know the synchronization level. Other than that
the curiosity about that contradiction, it doesn't seem like a very
interesting detail to me right now. It's not a question that drives the
rest of the design, but the other way round.

But FWIW, something like your proposal of sending 3 XLogRecPtrs in each
reply seems like a good approach. I'm not sure about using walwriter. I
can see that it helps with getting the 'recv' and 'replay'
acknowledgments out faster, but I still have the scars from starting
bgwriter during recovery.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> So far, I have added the point that if a user requests a level of
> confirmation that is currently unavailable, then it will use the highest
> level of confirmation available now. That stops us from waiting for
> timeout for every transaction we run if standby goes down hard, which
> just freezes the application for long periods to no real benefit. It
> also prevents applications from requesting durability levels the cluster
> cannot satisfy, in the opinion of the sysadmin, since the sysadmin
> specifies the max level on each standby.

That sounds like the commit-or-rollback when slave are gone question. I
think this behavior should be user-setable, again per-transaction. I
agree with you that the general case looks like your proposed default,
but we already know that some will need "don't ack if not replied before
the timeout", and they even will go as far as asking for it to be
reported as a serialisation error of some sort, I guess…

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

On Fri, 2010-09-17 at 13:41 +0300, Heikki Linnakangas wrote:
> On 17/09/10 12:49, Simon Riggs wrote:
> > This isn't just about UI, there are significant and important
> > differences between the proposals in terms of the capability and control
> > they offer.
>
> Sure. The point of focusing on the UI is that the UI demonstrates what
> capability and control a proposal offers.

My patch does not include server registration. It could be added later
on top of my patch without any issues.

The core parts of my patch are the fine grained transaction-level
control and the ability to mix them dynamically with good performance.

To me server registration is not a core issue. I'm not actively against
it, I just don't see the need for it at all. Certainly not committed
first, especially since its not actually needed by either of our
patches.

Standby registration doesn't provide *any* parameter that can't be
supplied from standby recovery.conf.

The only thing standby registration allows you to do is know whether
there was supposed to be a standby there, but yet it isn't there now. I
don't see that point as being important because it seems strange to me
to want to wait for a standby that ought to be there, but isn't anymore.
What happens if it never comes back? Manual intervention required.

(We agree on how to handle a standby that *is* "connected", yet never
returns a reply or takes too long to do so).

> >> So what should the user interface be like? Given the 1st and 2nd
> >> requirement, we need standby registration. If some standbys are
> >> important and others are not, the master needs to distinguish between
> >> them to be able to determine that a transaction is safely delivered to
> >> the important standbys.
> >
> > My patch provides those two requirements without standby registration,
> > so we very clearly don't "need" standby registration.
>
> It's still not clear to me how you would configure things like "wait for
> ack from reporting slave, but not other slaves" or "wait until replayed
> in the server on the west coast" in your proposal. Maybe it's possible,
> but doesn't seem very intuitive, requiring careful configuration in both
> the master and the slaves.

In the use cases we discussed we had simple 2 or 3 server configs.

master
standby1 - preferred sync target - set to recv, fsync or apply
standby2 - non-preferred sync target, maybe test server - set to async

So in the two cases you mention we might set

"wait for ack from reporting slave"
master: sync_replication = 'recv' #as default, can be changed
reporting-slave: sync_replication_service = 'recv' #gives max level

"wait until replayed in the server on the west coast"
master: sync_replication = 'recv' #as default, can be changed
west-coast: sync_replication_service = 'apply' #gives max level

The absence of registration in my patch makes some things easier and
some things harder. For example, you can add a new standby without
editing the config on the master.

If you had 2 standbys, both offering the same level of protection, my
proposal would *not* allow you to specify that you preferred one master
over another. But we could add a priority parameter as well if that's an
issue.

> In your proposal, you also need to be careful not to connect e.g a test
> slave with "synchronous_replication_service = apply" to the master, or
> it will possible shadow a real production slave, acknowledging
> transactions that are not yet received by the real slave. It's certainly
> possible to screw up with standby registration too, but you have more
> direct control of the master behavior in the master, instead of
> distributing it across all slaves.
>
> > The question is do we want standby registration on master and if so,
> > why?
>
> Well, aside from how to configure synchronous replication, standby
> registration would help with retaining the right amount of WAL in the
> master. wal_keep_segments doesn't guarantee that enough is retained, and
> OTOH when all standbys are connected you retain much more than might be
> required.
>
> Giving names to slaves also allows you to view their status in the
> master in a more intuitive format. Something like:

We can give servers a name without registration. It actually makes more
sense to set the name in the standby and it can be passed through from
standby when we connect.

I very much like the idea of server names and think this next SRF looks
really cool.

> postgres=# SELECT * FROM pg_slave_status ;
> name | connected | received | fsyncd | applied
> ------------+-----------+------------+------------+------------
> reporting | t | 0/26000020 | 0/26000020 | 0/25550020
> ha-standby | t | 0/26000020 | 0/26000020 | 0/26000020
> testserver | f | | 0/15000020 |
> (3 rows)

That could be added on top of my patch also.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, Sep 17, 2010 at 6:41 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>> So what should the user interface be like? Given the 1st and 2nd
>>> requirement, we need standby registration. If some standbys are
>>> important and others are not, the master needs to distinguish between
>>> them to be able to determine that a transaction is safely delivered to
>>> the important standbys.
>>
>> My patch provides those two requirements without standby registration,
>> so we very clearly don't "need" standby registration.
>
> It's still not clear to me how you would configure things like "wait for ack
> from reporting slave, but not other slaves" or "wait until replayed in the
> server on the west coast" in your proposal. Maybe it's possible, but doesn't
> seem very intuitive, requiring careful configuration in both the master and
> the slaves.

Agreed. I think this will be much simpler if all the configuration is
in one place (on the master).

> In your proposal, you also need to be careful not to connect e.g a test
> slave with "synchronous_replication_service = apply" to the master, or it
> will possible shadow a real production slave, acknowledging transactions
> that are not yet received by the real slave. It's certainly possible to
> screw up with standby registration too, but you have more direct control of
> the master behavior in the master, instead of distributing it across all
> slaves.

Similarly agreed.

>> The question is do we want standby registration on master and if so,
>> why?
>
> Well, aside from how to configure synchronous replication, standby
> registration would help with retaining the right amount of WAL in the
> master. wal_keep_segments doesn't guarantee that enough is retained, and
> OTOH when all standbys are connected you retain much more than might be
> required.

+1.

> Giving names to slaves also allows you to view their status in the master in
> a more intuitive format. Something like:
>
> postgres=# SELECT * FROM pg_slave_status ;
>    name    | connected |  received  |   fsyncd   |  applied
> ------------+-----------+------------+------------+------------
>  reporting  | t         | 0/26000020 | 0/26000020 | 0/25550020
>  ha-standby | t         | 0/26000020 | 0/26000020 | 0/26000020
>  testserver | f         |            | 0/15000020 |
> (3 rows)

+1.

Having said all of the above, I am not in favor your (Heikki's)
proposal to configure sync/async on the slave and the level on the
master. That seems like a somewhat bizarre division of labor,
splitting what is essentially one setting across two machines.

>>> For the control between async/recv/fsync/replay, I like to think in
>>> terms of
>>> a) asynchronous vs synchronous
>>> b) if it's synchronous, how synchronous is it? recv, fsync or replay?
>>>
>>> I think it makes most sense to set sync vs. async in the master, and the
>>> level of synchronicity in the slave. Although I have sympathy for the
>>> argument that it's simpler if you configure it all from the master side
>>> as well.
>>
>> I have catered for such requests by suggesting a plugin that allows you
>> to implement that complexity without overburdening the core code.
>
> Well, plugins are certainly one possibility, but then we need to design the
> plugin API. I've been thinking along the lines of a proxy, which can
> implement whatever logic you want to decide when to send the acknowledgment.
> With a proxy as well, if we push any features people that want to a proxy or
> plugin, we need to make sure that the proxy/plugin has all the necessary
> information available.

I'm not really sold on the proxy idea. That seems like it adds a lot
of configuration complexity, not to mention additional hops. Of
course, the plug-in idea also won't be suitable for any but the most
advanced users. I think of the two I prefer the idea of a plug-in,
slightly, but maybe this doesn't have to be done in version 1.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Fri, Sep 17, 2010 at 7:31 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> The only thing standby registration allows you to do is know whether
> there was supposed to be a standby there, but yet it isn't there now. I
> don't see that point as being important because it seems strange to me
> to want to wait for a standby that ought to be there, but isn't anymore.
> What happens if it never comes back? Manual intervention required.
>
> (We agree on how to handle a standby that *is* "connected", yet never
> returns a reply or takes too long to do so).

Doesn't Oracle provide a mode where it shuts down if this occurs?

> The absence of registration in my patch makes some things easier and
> some things harder. For example, you can add a new standby without
> editing the config on the master.

That's actually one of the reasons why I like the idea of
registration. It seems rather scary to add a new standby without
editing the config on the master. Actually, adding a new fully-async
slave without touching the master seems reasonable, but adding a new
sync slave without touching the master gives me the willies. The
behavior of the system could change quite sharply when you do this,
and it might not be obvious what has happened. (Imagine DBA #1 makes
the change and DBA #2 is then trying to figure out what's happened -
he checks the configs of all the machines he knows about and finds
them all unchanged... head-scratching ensues.)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Fri, Sep 17, 2010 at 7:41 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> The question is do we want standby registration on master and if so,
>> why?
>
> Well, aside from how to configure synchronous replication, standby
> registration would help with retaining the right amount of WAL in the
> master. wal_keep_segments doesn't guarantee that enough is retained, and
> OTOH when all standbys are connected you retain much more than might be
> required.

Yep.

And standby registration is required when we support "wait forever when
synchronous standby isn't connected at the moment" option that Heikki
explained upthread. Though I don't think that standby registration is
required in the first phase since "wait forever" option is not used in
basic use case. Synchronous replication is basically used to reduce the
downtime, and "wait forever" option opposes that.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Fri, Sep 17, 2010 at 8:31 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> The only thing standby registration allows you to do is know whether
> there was supposed to be a standby there, but yet it isn't there now. I
> don't see that point as being important because it seems strange to me
> to want to wait for a standby that ought to be there, but isn't anymore.

According to what I heard, some people want to guarantee that all the
transactions are *always* written in *all* the synchronous standbys.
IOW, they want to keep the transaction waiting until it has been written
in all the synchronous standbys. Standby registration is required to
support such a use case. Without the registration, the master cannot
determine whether the transaction has been written in all the synchronous
standbys.

> What happens if it never comes back? Manual intervention required.

Yep.

> In the use cases we discussed we had simple 2 or 3 server configs.
>
> master
> standby1 - preferred sync target - set to recv, fsync or apply
> standby2 - non-preferred sync target, maybe test server - set to async
>
> So in the two cases you mention we might set
>
> "wait for ack from reporting slave"
> master: sync_replication = 'recv'   #as default, can be changed
> reporting-slave: sync_replication_service = 'recv' #gives max level
>
> "wait until replayed in the server on the west coast"
> master: sync_replication = 'recv'   #as default, can be changed
> west-coast: sync_replication_service = 'apply' #gives max level

What synchronization level does each combination of sync_replication
and sync_replication_service lead to? I'd like to see something like
the following table.

sync_replication | sync_replication_service | result
------------------+--------------------------+--------
async | async | ???
async | recv | ???
async | fsync | ???
async | apply | ???
recv | async | ???
...

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Fri, 2010-09-17 at 21:20 +0900, Fujii Masao wrote:

> What synchronization level does each combination of sync_replication
> and sync_replication_service lead to? I'd like to see something like
> the following table.
>
> sync_replication | sync_replication_service | result
> ------------------+--------------------------+--------
> async | async | ???
> async | recv | ???
> async | fsync | ???
> async | apply | ???
> recv | async | ???
> ...

Good question.

There are only 4 possible outcomes. There is no combination, so we don't
need a table like that above.

The "service" specifies the highest request type available from that
specific standby. If someone requests a higher service than is currently
offered by this standby, they will either
a) get that service from another standby that does offer that level
b) automatically downgrade the sync rep mode to the highest available.

For example, if you request recv but there is only one standby and it
only offers async, then you get downgraded to async.

In all cases, if you request async then we act same as 9.0.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, Sep 17, 2010 at 5:09 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> * Quorum commit. Wait until n standbys acknowledge. n=1 and n=all servers
> can be seen as important special cases of this.

I think that we should skip quorum commit at the first phase
because the design seems to be still poorly-thought-out.

I'm concerned about the case where the faster synchronous standby
goes down and the lagged synchronous one remains when n=1. In this
case, some transactions marked as committed in a client might not
be replicated to the remaining synchronous standby yet. What if
the master goes down at this point? How can we determine whether
promoting the remaining standby to the master causes data loss?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Fri, Sep 17, 2010 at 8:43 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Fri, Sep 17, 2010 at 5:09 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> * Quorum commit. Wait until n standbys acknowledge. n=1 and n=all servers
>> can be seen as important special cases of this.
>
> I think that we should skip quorum commit at the first phase
> because the design seems to be still poorly-thought-out.
>
> I'm concerned about the case where the faster synchronous standby
> goes down and the lagged synchronous one remains when n=1. In this
> case, some transactions marked as committed in a client might not
> be replicated to the remaining synchronous standby yet. What if
> the master goes down at this point? How can we determine whether
> promoting the remaining standby to the master causes data loss?

Yep. That issue has been raised before, and I think it's quite valid.
That's not to say the feature isn't valid, but I think trying to
include it in the first commit is going to lead to endless wrangling
about design.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Fri, 2010-09-17 at 21:20 +0900, Fujii Masao wrote:
> On Fri, Sep 17, 2010 at 8:31 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > The only thing standby registration allows you to do is know whether
> > there was supposed to be a standby there, but yet it isn't there now. I
> > don't see that point as being important because it seems strange to me
> > to want to wait for a standby that ought to be there, but isn't anymore.
>
> According to what I heard, some people want to guarantee that all the
> transactions are *always* written in *all* the synchronous standbys.
> IOW, they want to keep the transaction waiting until it has been written
> in all the synchronous standbys. Standby registration is required to
> support such a use case. Without the registration, the master cannot
> determine whether the transaction has been written in all the synchronous
> standbys.

You don't need standby registration at all. You can do that with a
single parameter, already proposed:

quorum_commit = N.

But most people said they didn't want it. If they do we can put it back
later.

I don't think we're getting anywhere here. I just don't see any *need*
to have it. Some people might *want* to set things up that way, and if
that's true, that's enough for me to agree with them. The trouble is, I
know some people have said they *want* to set it in the standby and we
definitely *need* to set it somewhere. After this discussion, I think
"both" is easily done and quite cool.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2010-09-17 at 20:56 +0900, Fujii Masao wrote:
> On Fri, Sep 17, 2010 at 7:41 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> >> The question is do we want standby registration on master and if so,
> >> why?
> >
> > Well, aside from how to configure synchronous replication, standby
> > registration would help with retaining the right amount of WAL in the
> > master. wal_keep_segments doesn't guarantee that enough is retained, and
> > OTOH when all standbys are connected you retain much more than might be
> > required.
>
> Yep.

Setting wal_keep_segments is difficult, but its not a tunable.

The sysadmin needs to tell us what is the maximum number of files she'd
like to keep. Otherwise we may fill up a disk, use space intended for
use by another app, etc..

The server cannot determine what limits the sysadmin may wish to impose.
The only sane default is 0, because "store everything, forever" makes no
sense. Similarly, if we register a server, it goes down and we forget to
deregister it then we will attempt to store everything, forever and our
system will go down.

The bigger problem is base backups, not server restarts. We don't know
how to get that right because we don't register base backups
automatically. If we did dynamically alter the number of WALs we store
then we'd potentially screw up new base backups. Server registration
won't help with that at all, so you'd need to add a base backup
registration scheme as well. But even if you had that, you'd still need
a "max" setting defined by sysadmin.

So the only sane thing to do is to set wal_keep_segments as high as
possible. And doing that doesn't need server reg.

> And standby registration is required when we support "wait forever when
> synchronous standby isn't connected at the moment" option that Heikki
> explained upthread. Though I don't think that standby registration is
> required in the first phase since "wait forever" option is not used in
> basic use case. Synchronous replication is basically used to reduce the
> downtime, and "wait forever" option opposes that.

Agreed, but I'd say "if" we support that.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2010-09-17 at 13:41 +0300, Heikki Linnakangas wrote:
> On 17/09/10 12:49, Simon Riggs wrote:
> > Fujii has long talked about 4 levels of service also. Why change? I had
> > thought that part was pretty much agreed between all of us.
>
> Now you lost me. I agree that we need 4 levels of service (at least
> ultimately, not necessarily in the first phase).

OK, good.

> > Without performance tests to demonstrate "why", these do sound hard to
> > understand. But we should note that DRBD offers recv ("B") and fsync
> > ("C") as separate options. And Oracle implements all 3 of recv, fsync
> > and apply. Neither of them describe those options so simply and easily
> > as the way we are proposing with a 4 valued enum (with async as the
> > fourth option).
> >
> > If we have only one option for sync_rep = 'on' which of recv | fsync |
> > apply would it implement? You don't mention that. Which do you choose?
>
> You would choose between recv, fsync and apply in the slave, with a GUC.

So you would have both registration on the master and parameter settings
on the standby? I doubt you mean that, so possibly need more explanation
there for me to understand what you mean and also why you would do that.

> > I no longer seek to persuade by words alone. The existence of my patch
> > means that I think that only measurements and tests will show why I have
> > been saying these things. We need performance tests.
>

> I don't expect any meaningful differences in terms of performance
> between any of the discussed options. The big question right now is...

This is the critical point. Politely, I would observe that *You* do not
think there is a meaningful difference. *I* do, and evidence suggests
that both Oracle and DRBD think so too. So we differ on what the "big
question" is here.

It's sounding to me that if we don't know these things, then we're quite
a long way from committing something. This is basic research.

> what
> features we provide and how they're configured. Performance will depend
> primarily on the mode you use, and secondarily on the implementation of
> the mode. It would be completely premature to do performance testing yet
> IMHO.

If a patch is "ready" then we should be able to performance test it
*before* we commit it. From what you say it sounds like Fujii's patch
might yet require substantial tuning, so it might even be the case that
my patch is closer in terms of readiness to commit. Whatever the case,
we have two patches and I can't see any benefit in avoiding performance
tests.

> >> Putting all of that together. I think Fujii-san's standby.conf is pretty
> >> close.
> >
> >> What it needs is the additional GUC for transaction-level control.
> >
> > The difference between the patches is not a simple matter of a GUC.
> >
> > My proposal allows a single standby to provide efficient replies to
> > multiple requested durability levels all at the same time. With
> > efficient use of network resources. ISTM that because the other patch
> > cannot provide that you'd like to persuade us that we don't need that,
> > ever. You won't sell me on that point, cos I can see lots of uses for
> > it.
>
> Simon, how the replies are sent is an implementation detail I haven't
> given much thought yet.

It seems clear we've thought about different details around these
topics. Now I understand your work on latches, I see it is an important
contribution and I very much respect that. IMHO, each of us has seen
something important that the other has not.

> The reason we delved into that discussion
> earlier was that you seemed to contradict yourself with the claims that
> you don't need to send more than one reply per transaction, and that the
> standby doesn't need to know the synchronization level. Other than that
> the curiosity about that contradiction, it doesn't seem like a very
> interesting detail to me right now. It's not a question that drives the
> rest of the design, but the other way round.

There was no contradiction. You just didn't understand how it could be
possible, so dismissed it.

It's a detail, yes. Some are critical, some are not. (e.g. latches.)

My view is that it is critical and drives the design. So I don't agree
with you on "the other way around".

> But FWIW, something like your proposal of sending 3 XLogRecPtrs in each
> reply seems like a good approach. I'm not sure about using walwriter. I
> can see that it helps with getting the 'recv' and 'replay'
> acknowledgments out faster, but

> I still have the scars from starting
> bgwriter during recovery.

I am happy to apologise for those problems. I was concentrating on HS at
the time, not on that aspect. You sorted out those problems for me and I
thank you for that.

With that in mind, I will remove the aspect of my patch that relate to
starting wal writer. Small amount of code only. That means we will
effectively disable recv mode for now, but I definitely want to be able
to put it back later.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2010-09-17 at 21:43 +0900, Fujii Masao wrote:
> On Fri, Sep 17, 2010 at 5:09 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > * Quorum commit. Wait until n standbys acknowledge. n=1 and n=all servers
> > can be seen as important special cases of this.
>
> I think that we should skip quorum commit at the first phase
> because the design seems to be still poorly-thought-out.

Agreed

> I'm concerned about the case where the faster synchronous standby
> goes down and the lagged synchronous one remains when n=1. In this
> case, some transactions marked as committed in a client might not
> be replicated to the remaining synchronous standby yet. What if
> the master goes down at this point? How can we determine whether
> promoting the remaining standby to the master causes data loss?

In that config if the faster sync standby goes down then your
application performance goes down dramatically. That would be fragile.

So you would set up like this
master - requests are > async
standby1 - fast - so use recv | fsync | apply
standby2 - async

So if standby1 goes down we don't wait for standby2, but we do continue
to stream to it.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 17/09/10 15:56, Simon Riggs wrote:
> On Fri, 2010-09-17 at 13:41 +0300, Heikki Linnakangas wrote:
>> On 17/09/10 12:49, Simon Riggs wrote:
>>> Without performance tests to demonstrate "why", these do sound hard to
>>> understand. But we should note that DRBD offers recv ("B") and fsync
>>> ("C") as separate options. And Oracle implements all 3 of recv, fsync
>>> and apply. Neither of them describe those options so simply and easily
>>> as the way we are proposing with a 4 valued enum (with async as the
>>> fourth option).
>>>
>>> If we have only one option for sync_rep = 'on' which of recv | fsync |
>>> apply would it implement? You don't mention that. Which do you choose?
>>
>> You would choose between recv, fsync and apply in the slave, with a GUC.
>
> So you would have both registration on the master and parameter settings
> on the standby? I doubt you mean that, so possibly need more explanation
> there for me to understand what you mean and also why you would do that.

Yes, that's what I meant. No-one else seems to think that's a good idea :-).

>> I don't expect any meaningful differences in terms of performance
>> between any of the discussed options. The big question right now is...
>
> This is the critical point. Politely, I would observe that *You* do not
> think there is a meaningful difference. *I* do, and evidence suggests
> that both Oracle and DRBD think so too. So we differ on what the "big
> question" is here.

We must be talking about different things again. There's certainly big
differences in the different synchronization levels and configurations,
but I don't expect there to be big performance differences between
patches to implement those levels. Once we got rid of the polling loops,
I expect the network and disk latencies to dominate.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

* Robert Haas <robertmhaas(at)gmail(dot)com> [100917 07:44]:
> On Fri, Sep 17, 2010 at 7:31 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > The only thing standby registration allows you to do is know whether
> > there was supposed to be a standby there, but yet it isn't there now. I
> > don't see that point as being important because it seems strange to me
> > to want to wait for a standby that ought to be there, but isn't anymore.
> > What happens if it never comes back? Manual intervention required.

> > The absence of registration in my patch makes some things easier and
> > some things harder. For example, you can add a new standby without
> > editing the config on the master.
>
> That's actually one of the reasons why I like the idea of
> registration. It seems rather scary to add a new standby without
> editing the config on the master. Actually, adding a new fully-async
> slave without touching the master seems reasonable, but adding a new
> sync slave without touching the master gives me the willies. The
> behavior of the system could change quite sharply when you do this,
> and it might not be obvious what has happened. (Imagine DBA #1 makes
> the change and DBA #2 is then trying to figure out what's happened -
> he checks the configs of all the machines he knows about and finds
> them all unchanged... head-scratching ensues.)

So, those both give me the willies too...

I've had a rack loose all power. Now, let's say I've got two servers
(plus trays of disks for each) in the same rack. Ya, I know, I should
move them to separate racks, preferably in separate buildings on the
same campus, but realistically...

I want to have them configured in a fsync WAL/style sync rep, I want to
make sure that if the master comes up first after I get power back, it's
not going to be claiming transactions are committed while the slave
(which happens to have 4x the disks because it keeps PITR backups for a
period too) it still chugging away on SCSI probes yet, not gotten to
having PostgreSQL up yet...

And I want to make sure the dev box that was testing another slave setup
on, which is running in some test area by some other DBA, but not in the
same rack, *can't* through some mis-configuration make my master think
that it's production slave has properly fsync'ed the replicated WAL.

</hopes & dreams>

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

* Fujii Masao <masao(dot)fujii(at)gmail(dot)com> [100917 07:57]:

> Synchronous replication is basically used to reduce the
> downtime, and "wait forever" option opposes that.

Hm... I'm not sure I'ld agree with that. I'ld rather have some
downtime, and my data available, then have less downtime, but find that
I'm missing valuable data that was committed, but happend to not be
replicated because no slave was available "yet".

Sync rep is about "data availability", "data recoverability", *and*
"downtime". The three are definitely related, but each use has their
own tradeoffs.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Fri, 2010-09-17 at 09:36 -0400, Aidan Van Dyk wrote:

> I want to have them configured in a fsync WAL/style sync rep, I want to
> make sure that if the master comes up first after I get power back, it's
> not going to be claiming transactions are committed while the slave
> (which happens to have 4x the disks because it keeps PITR backups for a
> period too) it still chugging away on SCSI probes yet, not gotten to
> having PostgreSQL up yet...

Nobody has mentioned the ability to persist the not-committed state
across a crash before, and I think it's an important discussion point.

We already have it: its called "two phase commit". (2PC)

If you run 2PC on 3 servers and one goes down, you can just commit the
in-flight transactions and continue. But it doesn't work on hot standby.

It could: If we want that we could prepare the transaction on the master
and don't allow commit until we get positive confirmation from standby.
All of the machinery is there.

I'm not sure if that's a 5th sync rep mode, or that idea is actually
good enough to replace all the ideas we've had up until now. I would say
probably not, but we should think about this.

A slightly modified idea would be avoid writing the transaction prepare
file as a separate file, just write the WAL for the prepare. We then
remember the LSN of the prepare so we can re-access the WAL copy of it
by re-reading the WAL files on master. Make sure we don't get rid of WAL
that refers to waiting transactions. That would then give us the option
to commit or abort depending upon whether we receive a reply within
timeout.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, Sep 17, 2010 at 11:22 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, 2010-09-17 at 09:36 -0400, Aidan Van Dyk wrote:
>
>> I want to have them configured in a fsync WAL/style sync rep, I want to
>> make sure that if the master comes up first after I get power back, it's
>> not going to be claiming transactions are committed while the slave
>> (which happens to have 4x the disks because it keeps PITR backups for a
>> period too) it still chugging away on SCSI probes yet, not gotten to
>> having PostgreSQL up yet...
>
> Nobody has mentioned the ability to persist the not-committed state
> across a crash before, and I think it's an important discussion point.

Eh? I think all Aidan is asking for is the ability to have a mode
where sync rep is really always sync, or nothing commits. Rather than
timing out and continuing merrily on its way...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

* Robert Haas <robertmhaas(at)gmail(dot)com> [100917 11:24]:
> On Fri, Sep 17, 2010 at 11:22 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > On Fri, 2010-09-17 at 09:36 -0400, Aidan Van Dyk wrote:
> >
> >> I want to have them configured in a fsync WAL/style sync rep, I want to
> >> make sure that if the master comes up first after I get power back, it's
> >> not going to be claiming transactions are committed while the slave
> >> (which happens to have 4x the disks because it keeps PITR backups for a
> >> period too) it still chugging away on SCSI probes yet, not gotten to
> >> having PostgreSQL up yet...
> >
> > Nobody has mentioned the ability to persist the not-committed state
> > across a crash before, and I think it's an important discussion point.
>
> Eh? I think all Aidan is asking for is the ability to have a mode
> where sync rep is really always sync, or nothing commits. Rather than
> timing out and continuing merrily on its way...

Right, I'm not asking for a "new" mode. I'm just hope that there will
be a way to guarantee my "sync rep" is actually replicating. Having it
"not replicate" simply because no slave has (yet) connected means I have
to dance jigs around pg_hba.conf so that it won't allow non-replication
connections until I've manual verified that the replication slave
is connected...

a.
--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Fri, 2010-09-17 at 11:30 -0400, Aidan Van Dyk wrote:
> * Robert Haas <robertmhaas(at)gmail(dot)com> [100917 11:24]:
> > On Fri, Sep 17, 2010 at 11:22 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > > On Fri, 2010-09-17 at 09:36 -0400, Aidan Van Dyk wrote:
> > >
> > >> I want to have them configured in a fsync WAL/style sync rep, I want to
> > >> make sure that if the master comes up first after I get power back, it's
> > >> not going to be claiming transactions are committed while the slave
> > >> (which happens to have 4x the disks because it keeps PITR backups for a
> > >> period too) it still chugging away on SCSI probes yet, not gotten to
> > >> having PostgreSQL up yet...
> > >
> > > Nobody has mentioned the ability to persist the not-committed state
> > > across a crash before, and I think it's an important discussion point.
> >
> > Eh? I think all Aidan is asking for is the ability to have a mode
> > where sync rep is really always sync, or nothing commits. Rather than
> > timing out and continuing merrily on its way...
>
> Right, I'm not asking for a "new" mode. I'm just hope that there will
> be a way to guarantee my "sync rep" is actually replicating. Having it
> "not replicate" simply because no slave has (yet) connected means I have
> to dance jigs around pg_hba.conf so that it won't allow non-replication
> connections until I've manual verified that the replication slave
> is connected...

I agree that aspect is a problem.

One solution, to me, would be to have a directive included in the
pg_hba.conf that says entries below it are only allowed if it passes the
test. So your hba file looks like this

local postgres postgres
host replication ...
need replication
host any any

So the "need" test is an extra option in the first column. We might want
additional "need" tests before we allow other rules also. Text following
the "need" verb will be additional info for that test, sufficient to
allow some kind of execution on the backend.

I definitely don't like the idea that anyone that commits will just sit
there waiting until the standby comes up. That just sounds an insane way
of doing it.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2010-09-17 at 16:09 +0300, Heikki Linnakangas wrote:

> >> I don't expect any meaningful differences in terms of performance
> >> between any of the discussed options. The big question right now is...
> >
> > This is the critical point. Politely, I would observe that *You* do not
> > think there is a meaningful difference. *I* do, and evidence suggests
> > that both Oracle and DRBD think so too. So we differ on what the "big
> > question" is here.
>
> We must be talking about different things again. There's certainly big
> differences in the different synchronization levels and configurations,
> but I don't expect there to be big performance differences between
> patches to implement those levels. Once we got rid of the polling loops,
> I expect the network and disk latencies to dominate.

So IIUC you seem to agree with
* 4 levels of synchronous replication (specified on master)
* transaction-controlled replication from the master
* sending 3 LSN values back from standby

Well, then that pretty much is my patch, except for the parameter UI.
Did I misunderstand?

We also agree that we need a standby to master protocol change; I used
Zoltan's directly and I've had zero problems with it in testing.

The only disagreement has been about

* the need for standby registration (I understand "want")
which seems to boil down to whether we wait for servers that *ought* to
be there, but currently aren't.

* whether to have wal writer active (I'm happy to add that later in this
release, so we get the "recv" option also)

* whether we have a parameter for quorum commit > 1 (happy to add later)
Not sure if there is debate about whether quorum_commit = 1 is the
default.

* whether we provide replication_exceptions as core feature or as a
plugin

The only area of doubt is when we send replies, which you haven't
thought about yet. So presumably you've no design-level objection to
what I've proposed.

Things we all seem to like are

* different standbys can offer different sync levels

* standby names

* a set returning function which tells you current LSNs of all standbys

* the rough idea of being able to specify a "service" and have that
equate to a more complex config underneath the covers, without needing
to have the application know the details - I think we need more details
on that before we could say "we agree".

So seems like a good days work.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 2010-09-17 10:09, Heikki Linnakangas wrote:
> I think it makes most sense to set sync vs. async in the master, and
> the level of synchronicity in the slave. Although I have sympathy
> for the argument that it's simpler if you configure it all from the
> master side as well.

Just a comment as a sysadmin, It would be hugely beneficial if the
master and slaves all was able to run from the "exact same" configuration
file. This would leave out any doubt of the configuration of the
"complete cluster"
in terms of debugging. Slave would be able to just "copy" over the masters
configuration, etc. etc.

I dont know if it is doable or has any huge backsides.

--
Jesper

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Fri, 2010-09-17 at 21:20 +0900, Fujii Masao wrote:
>> According to what I heard, some people want to guarantee that all the
>> transactions are *always* written in *all* the synchronous standbys.
>
> You don't need standby registration at all. You can do that with a
> single parameter, already proposed:
>
> quorum_commit = N.

I think you also need another parameter to control the behavior upon
timeout. You received less than N votes, now what? You're current idea
seems to be COMMIT, Aidan says ROLLBACK, and I say that's to be a GUC
set at the transaction level.

As far as registration goes, I see no harm to have the master maintain a
list of known standby systems, of course, it's just maintaining that
list from the master that I don't understand the use case for.

Regards,
--
dim

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Fri, 2010-09-17 at 21:20 +0900, Fujii Masao wrote:
>> What synchronization level does each combination of sync_replication
>> and sync_replication_service lead to?
>
> There are only 4 possible outcomes. There is no combination, so we don't
> need a table like that above.
>
> The "service" specifies the highest request type available from that
> specific standby. If someone requests a higher service than is currently
> offered by this standby, they will either
> a) get that service from another standby that does offer that level
> b) automatically downgrade the sync rep mode to the highest available.

I like the a) part, I can't say the same about the b) part. There's no
reason to accept to COMMIT a transaction when the requested durability
is known not to have been reached, unless the user said so.

> For example, if you request recv but there is only one standby and it
> only offers async, then you get downgraded to async.

If so you choose, but with a net slowdown as you're now reaching the
timeout for each transaction, with what I have in mind, and I don't see
how you can avoid that. Even if you setup the replication from the
master, you still can mess it up the same way, right?

Regards,
--
dim

Hi,

On 09/17/2010 01:56 PM, Fujii Masao wrote:
> And standby registration is required when we support "wait forever when
> synchronous standby isn't connected at the moment" option that Heikki
> explained upthread.

That requirement can be reduced to say that the master only needs to
known how many synchronous standbys *should* be connected.

IIUC that's pretty much exactly the quorum_commit GUC that Simon
proposed, because it doesn't make sense to have more synchronous
standbys connected than quorum_commit (as Simon pointed out downthread).

I'm unsure about what's better, the full list (giving a good overview,
but more to configure) or the single sum GUC (being very flexible and
closer to how things work internally). But that seems to be a UI
question exclusively.

Regarding the "wait forever" option: I don't think continuing is a
viable alternative, as it silently ignores the requested level of
persistence. The only alternative I can see is to abort with an error.
As far as comparison is allowed, that's what Postgres-R currently does
if there's no majority of nodes. It allows to emit an error message and
helpful hints, as opposed to letting the admin figure out what and where
it's hanging. Not throwing false errors has the same requirements as
"waiting forever", so that's an orthogonal issue, IMO.

Regards

Markus Wanner

On Fri, 2010-09-17 at 21:32 +0200, Dimitri Fontaine wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > On Fri, 2010-09-17 at 21:20 +0900, Fujii Masao wrote:
> >> According to what I heard, some people want to guarantee that all the
> >> transactions are *always* written in *all* the synchronous standbys.
> >
> > You don't need standby registration at all. You can do that with a
> > single parameter, already proposed:
> >
> > quorum_commit = N.
>
> I think you also need another parameter to control the behavior upon
> timeout. You received less than N votes, now what? You're current idea
> seems to be COMMIT, Aidan says ROLLBACK, and I say that's to be a GUC
> set at the transaction level.

I've said COMMIT with no option because I believe that we have only two
choices: commit or wait (perhaps forever), and IMHO waiting is not good.

We can't ABORT, because we sent a commit to the standby. If we abort,
then we're saying the standby can't ever come back because it will have
received and potentially replayed a different transaction history. I had
some further thoughts around that but you end up with the byzantine
generals problem always.

Waiting might sound attractive. In practice, waiting will make all of
your connections lock up and it will look to users as if their master
has stopped working as well. (It has!). I can't imagine why anyone would
ever want an option to select that; its the opposite of high
availability. Just sounds like a serious footgun.

Having said that Oracle offers Maximum Protection mode, which literally
shuts down the master when you lose a standby. I can't say anything
apart from "LOL".

> As far as registration goes, I see no harm to have the master maintain a
> list of known standby systems, of course, it's just maintaining that
> list from the master that I don't understand the use case for.

Yes, the master needs to know about all currently connected standbys.
The only debate is what happens about ones that "ought" to be there.

Given my comments above, I don't see the need.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> I've said COMMIT with no option because I believe that we have only two
> choices: commit or wait (perhaps forever), and IMHO waiting is not good.
>
> We can't ABORT, because we sent a commit to the standby.

Ah yes, I keep forgetting Sync Rep is not about 2PC. Sorry about that.

> Waiting might sound attractive. In practice, waiting will make all of
> your connections lock up and it will look to users as if their master
> has stopped working as well. (It has!). I can't imagine why anyone would
> ever want an option to select that; its the opposite of high
> availability. Just sounds like a serious footgun.

I guess that if there's a timeout GUC it can still be set to infinite
somehow. Unclear as the use case might be.

Regards,
--
dim

On Sat, Sep 18, 2010 at 4:50 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Waiting might sound attractive. In practice, waiting will make all of
> your connections lock up and it will look to users as if their master
> has stopped working as well. (It has!). I can't imagine why anyone would
> ever want an option to select that; its the opposite of high
> availability. Just sounds like a serious footgun.

Nevertheless, it seems that some people do want exactly that behavior,
no matter how crazy it may seem to you. I'm not exactly sure what
we're in disagreement about, TBH. You've previously said that you
don't think standby registration is necessary, but that you don't
object to it if others want it. So it seems like this might be mostly
academic.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

All,

I'm answering this strictly from the perspective of my company's
customers and what they've asked for. It does not reflect on what
features are reflected in whatever patch.

> * Support multiple standbys with various synchronization levels.

Essential. We already have two customers who want to have one synch and
several async standbys.

> * What happens if a synchronous standby isn't connected at the moment?
> Return immediately vs. wait forever.

Essential.
Actually, we need a replication_return_timeout. That is, wait X seconds
on the standby and then give up. Again, in the systems I'm working
with, we'd want to wait 5 seconds and then abort replication.

> * Per-transaction control. Some transactions are important, others are not.

Low priority.
I see this as a 9.2 feature. Nobody I know is asking for it yet, and I
think we need to get the other stuff right first.

> * Quorum commit. Wait until n standbys acknowledge. n=1 and n=all
> servers can be seen as important special cases of this.

Medium priority. This would go together with having a registry of
standbies. The only reason I don't call this low priority is that it
would catapult PostgreSQL into the realm of CAP databases, assuming that
we could deal with the re-mastering issue as well.

> * async, recv, fsync and replay levels of synchronization.

Fsync vs. Replay is low priority (as in, we could live with just one or
the other), but the others are all high priority. Again, this should be
settable *per standby*.

> So what should the user interface be like? Given the 1st and 2nd
> requirement, we need standby registration. If some standbys are
> important and others are not, the master needs to distinguish between
> them to be able to determine that a transaction is safely delivered to
> the important standbys.

There are considerable benefits to having a standby registry with a
table-like interface. Particularly, one where we could change
replication via UPDATE (or ALTER STANDBY) statements.

a) we could eliminate a bunch of GUCs and control standby behavior
instead via the table interface.

b) DBAs and monitoring tools could see at a glance what the status of
their replication network was.

c) we could easily add new features (like quorum groups) without
breaking prior setups.

d) it would become easy rather than a PITA to construct GUI replication
management tools.

e) as previously mentioned, we could use it to have far more intelligent
control over what WAL segments to keep, both on the master and in some
distributed archive.

Note, however, that the data from this pseudo-table would need to be
replicated to the standby servers somehow in order to support re-mastering.

Take all the above with a grain of salt, though. The important thing is
to get *some kind* of synch rep into 9.1, and get 9.1 out on time.

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

On Sat, Sep 18, 2010 at 5:42 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> There are considerable benefits to having a standby registry with a
> table-like interface.  Particularly, one where we could change
> replication via UPDATE (or ALTER STANDBY) statements.

I think that using a system catalog for this is going to be a
non-starter, but we could use a flat file that is designed to be
machine-editable (and thus avoid repeating the mistake we've made with
postgresql.conf).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

> I think that using a system catalog for this is going to be a
> non-starter,

Technically improbable? Darn.

> but we could use a flat file that is designed to be
> machine-editable (and thus avoid repeating the mistake we've made with
> postgresql.conf).

Well, even if we can't update it through the command line, at least the
existing configuration (and node status) ought to be queryable.

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

On Sat, 2010-09-18 at 14:42 -0700, Josh Berkus wrote:

> > * Per-transaction control. Some transactions are important, others are not.
>
> Low priority.
> I see this as a 9.2 feature. Nobody I know is asking for it yet, and I
> think we need to get the other stuff right first.

I understand completely why anybody that has never used sync replication
would think per-transaction control is a small deal. I fully expect your
clients to try sync rep and then 5 minutes later say "Oh Crap, this sync
rep is so slow it's unusable. Isn't there a way to tune it?".

I've designed a way to tune sync rep so it is usable and useful. And
putting that feature into 9.1 costs very little, if anything. My patch
to do this is actually smaller than any other attempt to implement this
and I claim faster too. You don't need to use the per-transaction
controls, but they'll be there if you need them.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

> I've designed a way to tune sync rep so it is usable and useful. And
> putting that feature into 9.1 costs very little, if anything. My patch
> to do this is actually smaller than any other attempt to implement this
> and I claim faster too. You don't need to use the per-transaction
> controls, but they'll be there if you need them.

Well, if you already have the code, that's a different story ...

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

On 18/09/10 22:59, Robert Haas wrote:
> On Sat, Sep 18, 2010 at 4:50 AM, Simon Riggs<simon(at)2ndquadrant(dot)com> wrote:
>> Waiting might sound attractive. In practice, waiting will make all of
>> your connections lock up and it will look to users as if their master
>> has stopped working as well. (It has!). I can't imagine why anyone would
>> ever want an option to select that; its the opposite of high
>> availability. Just sounds like a serious footgun.
>
> Nevertheless, it seems that some people do want exactly that behavior,
> no matter how crazy it may seem to you.

Yeah, I agree with both of you. I have a hard time imaging a situation
where you would actually want that. It's not high availability, it's
high durability. When a transaction is acknowledged as committed, you
know it's never ever going to disappear even if a meteor strikes the
current master server within the next 10 milliseconds. In practice,
people want high availability instead.

That said, the timeout option also feels a bit wishy-washy to me. With a
timeout, acknowledgment of a commit means "your transaction is safely
committed in the master and slave. Or not, if there was some glitch with
the slave". That doesn't seem like a very useful guarantee; if you're
happy with that why not just use async replication?

However, the "wait forever" behavior becomes useful if you have a
monitoring application outside the DB that decides when enough is enough
and tells the DB that the slave can be considered dead. So "wait
forever" actually means "wait until I tell you that you can give up".
The monitoring application can STONITH to ensure that the slave stays
down, before letting the master proceed with the commit.

With that in mind, we have to make sure that a transaction that's
waiting for acknowledgment of the commit from a slave is woken up if the
configuration changes.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On 19/09/10 01:20, Robert Haas wrote:
> On Sat, Sep 18, 2010 at 5:42 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
>> There are considerable benefits to having a standby registry with a
>> table-like interface. Particularly, one where we could change
>> replication via UPDATE (or ALTER STANDBY) statements.
>
> I think that using a system catalog for this is going to be a
> non-starter, but we could use a flat file that is designed to be
> machine-editable (and thus avoid repeating the mistake we've made with
> postgresql.conf).

Yeah, that needs some careful design. We also need to record transient
information about each slave, like how far it has received WAL already.
Ideally that information would survive database restart too, but maybe
we can live without that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Mon, 2010-09-20 at 09:27 +0300, Heikki Linnakangas wrote:
> On 18/09/10 22:59, Robert Haas wrote:
> > On Sat, Sep 18, 2010 at 4:50 AM, Simon Riggs<simon(at)2ndquadrant(dot)com> wrote:
> >> Waiting might sound attractive. In practice, waiting will make all of
> >> your connections lock up and it will look to users as if their master
> >> has stopped working as well. (It has!). I can't imagine why anyone would
> >> ever want an option to select that; its the opposite of high
> >> availability. Just sounds like a serious footgun.
> >
> > Nevertheless, it seems that some people do want exactly that behavior,
> > no matter how crazy it may seem to you.
>
> Yeah, I agree with both of you. I have a hard time imaging a situation
> where you would actually want that. It's not high availability, it's
> high durability. When a transaction is acknowledged as committed, you
> know it's never ever going to disappear even if a meteor strikes the
> current master server within the next 10 milliseconds. In practice,
> people want high availability instead.
>
> That said, the timeout option also feels a bit wishy-washy to me. With a
> timeout, acknowledgment of a commit means "your transaction is safely
> committed in the master and slave. Or not, if there was some glitch with
> the slave". That doesn't seem like a very useful guarantee; if you're
> happy with that why not just use async replication?
>
> However, the "wait forever" behavior becomes useful if you have a
> monitoring application outside the DB that decides when enough is enough
> and tells the DB that the slave can be considered dead. So "wait
> forever" actually means "wait until I tell you that you can give up".
> The monitoring application can STONITH to ensure that the slave stays
> down, before letting the master proceed with the commit.

err... what is the difference between a timeout and stonith? None. We
still proceed without the slave in both cases after the decision point.

In all cases, we would clearly have a user accessible function to stop
particular sessions, or all sessions, from waiting for standby to
return.

You would have 3 choices:
* set automatic timeout
* set wait forever and then wait for manual resolution
* set wait forever and then trust to external clusterware

Many people have asked for timeouts and I agree it's probably the
easiest thing to do if you just have 1 standby.

> With that in mind, we have to make sure that a transaction that's
> waiting for acknowledgment of the commit from a slave is woken up if the
> configuration changes.

There's a misunderstanding here of what I've said and its a subtle one.

My patch supports a timeout of 0, i.e. wait forever. Which means I agree
that functionality is desired and should be included. This operates by
saying that if a currently-connected-standby goes down we will wait
until the timeout. So I agree all 3 choices should be available to
users.

Discussion has been about what happens to ought-to-have-been-connected
standbys. Heikki had argued we need standby registration because if a
server *ought* to have been there, yet isn't currently there when we
wait for sync rep, we would still wait forever for it to return. To do
this you require standby registration.

But there is a hidden issue there: If you care about high availability
AND sync rep you have two standbys. If one goes down, the other is still
there. In general, if you want high availability on N servers then you
have N+1 standbys. If one goes down, the other standbys provide the
required level of durability and we do not wait.

So the only case where standby registration is required is where you
deliberately choose to *not* have N+1 redundancy and then yet still
require all N standbys to acknowledge. That is a suicidal config and
nobody would sanely choose that. It's not a large or useful use case for
standby reg. (But it does raise the question again of whether we need
quorum commit).

My take is that if the above use case occurs it is because one standby
has just gone down and the standby is, for a hopefully short period, in
a degraded state and that the service responds to that. So in my
proposal, if a standby is not there *now* we don't wait for it.

Which cuts out a huge bag of code, specification and such like that
isn't required to support sane use cases. More stuff to get wrong and
regret in later releases. The KISS principle, just like we apply in all
other cases.

If we did have standby registration, then I would implement it in a
table, not in an external config file. That way when we performed a
failover the data would be accessible on the new master. But I don't
suggest we have CREATE/ALTER STANDBY syntax. We already have
CREATE/ALTER SERVER if we wanted to do it in SQL. If we did that, ISTM
we should choose functions.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 20/09/10 12:17, Simon Riggs wrote:
> err... what is the difference between a timeout and stonith?

STONITH ("Shoot The Other Node In The Head") means that the other node
is somehow disabled so that it won't unexpectedly come back alive. A
timeout means that the slave hasn't been seen for a while, but it might
reconnect just after the timeout has expired.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Mon, 2010-09-20 at 15:16 +0300, Heikki Linnakangas wrote:
> On 20/09/10 12:17, Simon Riggs wrote:
> > err... what is the difference between a timeout and stonith?
>
> STONITH ("Shoot The Other Node In The Head") means that the other node
> is somehow disabled so that it won't unexpectedly come back alive. A
> timeout means that the slave hasn't been seen for a while, but it might
> reconnect just after the timeout has expired.

You've edited my reply to change the meaning of what was a rhetorical
question, as well as completely ignoring the main point of my reply.

Please respond to the main point: Following some thought and analysis,
AFAICS there is no sensible use case that requires standby registration.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 20/09/10 15:50, Simon Riggs wrote:
> On Mon, 2010-09-20 at 15:16 +0300, Heikki Linnakangas wrote:
>> On 20/09/10 12:17, Simon Riggs wrote:
>>> err... what is the difference between a timeout and stonith?
>>
>> STONITH ("Shoot The Other Node In The Head") means that the other node
>> is somehow disabled so that it won't unexpectedly come back alive. A
>> timeout means that the slave hasn't been seen for a while, but it might
>> reconnect just after the timeout has expired.
>
> You've edited my reply to change the meaning of what was a rhetorical
> question, as well as completely ignoring the main point of my reply.
>
> Please respond to the main point: Following some thought and analysis,
> AFAICS there is no sensible use case that requires standby registration.

Ok, I had completely missed your point then.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Mon, Sep 20, 2010 at 8:50 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Please respond to the main point: Following some thought and analysis,
> AFAICS there is no sensible use case that requires standby registration.

I disagree. You keep analyzing away the cases that require standby
registration, but I don't believe that they're not real. Aidan Van
Dyk's case upthread of wanting to make sure that the standby is up and
replicating synchronously before the master starts processing
transactions seems perfectly legitimate to me. Sure, it's paranoid,
but so what? We're all about paranoia, at least as far as data loss
is concerned. So the "wait forever" case is, in my opinion,
sufficient to demonstrate that we need it, but it's not even my
primary reason for wanting to have it.

The most important reason why I think we should have standby
registration is for simplicity of configuration. Yes, it adds another
configuration file, but that configuration file contains ALL of the
information about which standbys are synchronous. Without standby
registration, this information will inevitably be split between the
master config and the various slave configs and you'll have to look at
all the configurations to be certain you understand how it's going to
end up working. As a particular manifestation of this, and as
previously argued and +1'd upthread, the ability to change the set of
standbys to which the master is replicating synchronously without
changing the configuration on the master or any of the existing slaves
seems seems dangerous.

Another reason why I think we should have standby registration is to
allow eventually allow the "streaming WAL backwards" configuration
which has previously been discussed. IOW, you could stream the WAL to
the slave in advance of fsync-ing it on the master. After a power
failure, the machines in the cluster can talk to each other and figure
out which one has the furthest-advanced WAL pointer and stream from
that machine to all the others. This is an appealing configuration
for people using sync rep because it would allow the fsyncs to be done
in parallel rather than sequentially as is currently necessary - but
if you're using it, you're certainly not going to want the master to
enter normal running without waiting to hear from the slave.

Just to be clear, that is a list of three independent reasons any one
of which I think is sufficient for wanting standby registration.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Hi,

I'm somewhat sorry to have to play this game, as I sure don't feel
smarter by composing this email. Quite the contrary.

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> So the "wait forever" case is, in my opinion,
> sufficient to demonstrate that we need it, but it's not even my
> primary reason for wanting to have it.

You're talking about standby registration on the master. You can solve
this case without it, because when a slave is not connected it's not
giving any feedback (vote, weight, ack) to the master. All you have to
do is have the quorum setup in a way that disconnecting your slave means
you can't reach the quorum any more. Have it SIGHUP and you can even
choose to fix the setup, rather than fix the standby.

So no need for registration here, it's just another way to solve the
problem. Not saying it's better or worse, just another.

Now we could have a summary function on the master showing all the known
slaves, their last time of activity, their known current setup, etc, all
from the master, but read-only. Would that be useful enough?

> The most important reason why I think we should have standby
> registration is for simplicity of configuration. Yes, it adds another
> configuration file, but that configuration file contains ALL of the
> information about which standbys are synchronous. Without standby
> registration, this information will inevitably be split between the
> master config and the various slave configs and you'll have to look at
> all the configurations to be certain you understand how it's going to
> end up working.

So, here, we have two quite different things to be concerned
about. First is the configuration, and I say that managing a distributed
setup will be easier for the DBA.

Then there's how to obtain a nice view about the distributed system,
which again we can achieve from the master without manually registering
the standbys. After all, the information you want needs to be there.

> As a particular manifestation of this, and as
> previously argued and +1'd upthread, the ability to change the set of
> standbys to which the master is replicating synchronously without
> changing the configuration on the master or any of the existing slaves
> seems seems dangerous.

Well, you still need to open the HBA for the new standby to be able to
connect, and to somehow take a base backup, right? We're not exactly
transparent there, yet, are we?

> Another reason why I think we should have standby registration is to
> allow eventually allow the "streaming WAL backwards" configuration
> which has previously been discussed. IOW, you could stream the WAL to
> the slave in advance of fsync-ing it on the master. After a power
> failure, the machines in the cluster can talk to each other and figure
> out which one has the furthest-advanced WAL pointer and stream from
> that machine to all the others. This is an appealing configuration
> for people using sync rep because it would allow the fsyncs to be done
> in parallel rather than sequentially as is currently necessary - but
> if you're using it, you're certainly not going to want the master to
> enter normal running without waiting to hear from the slave.

I love the idea.

Now it seems to me that all you need here is the master sending one more
information with each WAL "segment", the currently fsync'ed position,
which pre-9.1 is implied as being the current LSN from the stream,
right?

Here I'm not sure to follow you in details, but it seems to me
registering the standbys is just another way of achieving the same. To
be honest, I don't understand a bit how it helps implement your idea.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

On Mon, Sep 20, 2010 at 4:10 PM, Dimitri Fontaine
<dfontaine(at)hi-media(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>   So the "wait forever" case is, in my opinion,
>> sufficient to demonstrate that we need it, but it's not even my
>> primary reason for wanting to have it.
>
> You're talking about standby registration on the master. You can solve
> this case without it, because when a slave is not connected it's not
> giving any feedback (vote, weight, ack) to the master. All you have to
> do is have the quorum setup in a way that disconnecting your slave means
> you can't reach the quorum any more. Have it SIGHUP and you can even
> choose to fix the setup, rather than fix the standby.

I suppose that could work.

>> The most important reason why I think we should have standby
>> registration is for simplicity of configuration.  Yes, it adds another
>> configuration file, but that configuration file contains ALL of the
>> information about which standbys are synchronous.  Without standby
>> registration, this information will inevitably be split between the
>> master config and the various slave configs and you'll have to look at
>> all the configurations to be certain you understand how it's going to
>> end up working.
>
> So, here, we have two quite different things to be concerned
> about. First is the configuration, and I say that managing a distributed
> setup will be easier for the DBA.

Yeah, I disagree with that, but I suppose it's a question of opinion.

> Then there's how to obtain a nice view about the distributed system,
> which again we can achieve from the master without manually registering
> the standbys. After all, the information you want needs to be there.

I think that without standby registration it will be tricky to display
information like "the last time that standby foo was connected".
Yeah, you could set a standby name on the standby server and just have
the master remember details for every standby name it's ever seen, but
then how do you prune the list?

Heikki mentioned another application for having a list of the current
standbys only (rather than "every standby that has ever existed")
upthread: you can compute the exact amount of WAL you need to keep
around.

>>  As a particular manifestation of this, and as
>> previously argued and +1'd upthread, the ability to change the set of
>> standbys to which the master is replicating synchronously without
>> changing the configuration on the master or any of the existing slaves
>> seems seems dangerous.
>
> Well, you still need to open the HBA for the new standby to be able to
> connect, and to somehow take a base backup, right? We're not exactly
> transparent there, yet, are we?

Sure, but you might have that set relatively open on a trusted network.

>> Another reason why I think we should have standby registration is to
>> allow eventually allow the "streaming WAL backwards" configuration
>> which has previously been discussed.  IOW, you could stream the WAL to
>> the slave in advance of fsync-ing it on the master.  After a power
>> failure, the machines in the cluster can talk to each other and figure
>> out which one has the furthest-advanced WAL pointer and stream from
>> that machine to all the others.  This is an appealing configuration
>> for people using sync rep because it would allow the fsyncs to be done
>> in parallel rather than sequentially as is currently necessary - but
>> if you're using it, you're certainly not going to want the master to
>> enter normal running without waiting to hear from the slave.
>
> I love the idea.
>
> Now it seems to me that all you need here is the master sending one more
> information with each WAL "segment", the currently fsync'ed position,
> which pre-9.1 is implied as being the current LSN from the stream,
> right?

I don't see how that would help you.

> Here I'm not sure to follow you in details, but it seems to me
> registering the standbys is just another way of achieving the same. To
> be honest, I don't understand a bit how it helps implement your idea.

Well, if you need to talk to "all the other standbys" and see who has
the furtherst-advanced xlog pointer, it seems like you have to have a
list somewhere of who they all are. Maybe there's some way to get
this to work without standby registration, but I don't really
understand the resistance to the idea, and I fear it's going to do
nothing good for our reputation for ease of use (or lack thereof).
The idea of making this all work without standby registration strikes
me as akin to the notion of having someone decide whether they're
running a three-legged race by checking whether their leg is currently
tied to someone else's leg. You can probably make that work by
patching around the various failure cases, but why isn't simpler to
just tell the poor guy "Hi, Joe. You're running a three-legged race
with Jane today. Hans and Juanita will be following you across the
field, too, but don't worry about whether they're keeping up."?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On 20 September 2010 22:14, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Well, if you need to talk to "all the other standbys" and see who has
> the furtherst-advanced xlog pointer, it seems like you have to have a
> list somewhere of who they all are.

When they connect to the master to get the stream, don't they in
effect, already talk to the primary with the XLogRecPtr being relayed?
Can the connection IP, port, XLogRecPtr and request time of the
standby be stored from this communication to track the states of each
standby? They would in effect be registering upon WAL stream
request... and no doubt this is a horrifically naive view of how it
works.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Mon, Sep 20, 2010 at 5:42 PM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 20 September 2010 22:14, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Well, if you need to talk to "all the other standbys" and see who has
>> the furtherst-advanced xlog pointer, it seems like you have to have a
>> list somewhere of who they all are.
>
> When they connect to the master to get the stream, don't they in
> effect, already talk to the primary with the XLogRecPtr being relayed?
>  Can the connection IP, port, XLogRecPtr and request time of the
> standby be stored from this communication to track the states of each
> standby?  They would in effect be registering upon WAL stream
> request... and no doubt this is a horrifically naive view of how it
> works.

Sure, but the point is that we can want DISCONNECTED slaves to affect
master behavior in a variety of ways (master retains WAL for when they
reconnect, master waits for them to connect before acking commits,
master shuts down if they're not there, master tries to stream WAL
backwards from them before entering normal running). I just work
here, but it seems to me that such things will be easier if the master
has an explicit notion of what's out there. Can we make it all work
without that? Possibly, but I think it will be harder to understand.
With standby registration, you can DECLARE the behavior you want. You
can tell the master "replicate synchronously to Bob". And that's it.
Without standby registration, what's being proposed is basically that
you can tell the master "replicate synchronously to one server" and
you can tell Bob "you are a server to which the master can replicate
synchronously" and you can tell the other servers "you are not a
server to which Bob can replicate synchronously". That works, but to
me it seems less straightforward.

And that's actually a relatively simple example. Suppose you want to
tell the master "keep enough WAL for Bob to catch up when he
reconnects, but if he gets more than 1GB behind, forget about him".
I'm sure someone can devise a way of making that work without standby
registration, too, but I'm not too sure off the top of my head what it
will be. With standby registration, you can just write something like
this in standbys.conf (syntax invented):

[bob]
wal_keep_segments=64

I feel like that's really nice and simple.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Mon, 2010-09-20 at 22:42 +0100, Thom Brown wrote:
> On 20 September 2010 22:14, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > Well, if you need to talk to "all the other standbys" and see who has
> > the furtherst-advanced xlog pointer, it seems like you have to have a
> > list somewhere of who they all are.
>
> When they connect to the master to get the stream, don't they in
> effect, already talk to the primary with the XLogRecPtr being relayed?
> Can the connection IP, port, XLogRecPtr and request time of the
> standby be stored from this communication to track the states of each
> standby? They would in effect be registering upon WAL stream
> request... and no doubt this is a horrifically naive view of how it
> works.

It's not viable to record information at the chunk level in that way.

But the overall idea is fine. We can track who was connected and how to
access their LSNs. They don't need to be registered ahead of time on the
master to do that. They can register and deregister each time they
connect.

This discussion is reminiscent of the discussion we had when Fujii first
suggested that the standby should connect to the master. At first I
though "don't be stupid, the master needs to connect to the standby!".
It stood everything I had thought about on its head and that hurt, but
there was no logical reason to oppose. We could have used standby
registration on the master to handle that, but we didn't. I'm happy that
we have a more flexible system as a result.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On Sat, Sep 18, 2010 at 4:36 AM, Dimitri Fontaine
<dfontaine(at)hi-media(dot)com> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Fri, 2010-09-17 at 21:20 +0900, Fujii Masao wrote:
>>> What synchronization level does each combination of sync_replication
>>> and sync_replication_service lead to?
>>
>> There are only 4 possible outcomes. There is no combination, so we don't
>> need a table like that above.
>>
>> The "service" specifies the highest request type available from that
>> specific standby. If someone requests a higher service than is currently
>> offered by this standby, they will either
>> a) get that service from another standby that does offer that level
>> b) automatically downgrade the sync rep mode to the highest available.
>
> I like the a) part, I can't say the same about the b) part. There's no
> reason to accept to COMMIT a transaction when the requested durability
> is known not to have been reached, unless the user said so.

Yep, I can imagine that some people want to ensure that *all* the
transactions are synchronously replicated to the synchronous standby,
without regard to sync_replication. So I'm not sure if automatic
downgrade/upgrade of the mode makes sense. We should introduce new
parameter specifying whether to allow automatic degrade/upgrade or not?
It seems complicated though.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> So, here, we have two quite different things to be concerned
>> about. First is the configuration, and I say that managing a distributed
>> setup will be easier for the DBA.
>
> Yeah, I disagree with that, but I suppose it's a question of opinion.

I'd be willing to share your thoughts if it was only for the initial
setup. This one is hard enough to sketch on the paper that you prefer an
easy way to implement it afterwards, and in some cases a central setup
would be just that.

The problem is that I'm concerned with upgrading the setup once the
system is live. Not at the best time for that in the project, either,
but when you finally get the budget to expand the number of servers.

From experience with skytools, no manual registering works best. But…

> I think that without standby registration it will be tricky to display
> information like "the last time that standby foo was connected".
> Yeah, you could set a standby name on the standby server and just have
> the master remember details for every standby name it's ever seen, but
> then how do you prune the list?

… I now realize there are 2 parts under the registration bit. What I
don't see helping is manual registration. For some use cases you're
talking about maintaining a list of known servers sounds important, and
that's also what londiste is doing.

Pruning the list would be done with some admin function. You need one to
see the current state already, add some other one to unregister a known
standby.

In londiste, that's how it works, and events are kept in the queues for
all known subscribers. For the ones that won't ever connect again,
that's of course a problem, so you SELECT pgq.unregister_consumer(…);.

> Heikki mentioned another application for having a list of the current
> standbys only (rather than "every standby that has ever existed")
> upthread: you can compute the exact amount of WAL you need to keep
> around.

Well, either way, the system can not decide on its own whether a
currently not available standby is going to join the party again later
on.

>> Now it seems to me that all you need here is the master sending one more
>> information with each WAL "segment", the currently fsync'ed position,
>> which pre-9.1 is implied as being the current LSN from the stream,
>> right?
>
> I don't see how that would help you.

I think you want to refrain to apply any WAL segment you receive at the
standby and instead only advance as far as the master is known to have
reached. And you want this information to be safe against slave restart,
too: don't replay any WAL you have in pg_xlog or in the archive.

The other part of your proposal is another story (having slaves talk to
each-other at master crash).

> Well, if you need to talk to "all the other standbys" and see who has
> the furtherst-advanced xlog pointer, it seems like you have to have a
> list somewhere of who they all are.

Ah sorry I was thinking on the other part of the proposal only (sending
WAL segments that are not been fsync'ed yet on the master). So, yes.

But I thought you were saying that replicating a (shared?) catalog of
standbys is technically hard (or impossible), so how would you go about
it? As it's all about making things simpler for the users, you're not
saying that they should keep the main setup in sync manually on all the
standbys servers, right?

> Maybe there's some way to get
> this to work without standby registration, but I don't really
> understand the resistance to the idea

In fact I'm now realising what I don't like is having to manually do the
registration work: as I already have to setup the slaves, it only
appears like a useless burden on me, giving information the system
already has.

Automatic registration I'm fine with, I now realize.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

On Sun, Sep 19, 2010 at 7:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Sep 18, 2010 at 5:42 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> There are considerable benefits to having a standby registry with a
>> table-like interface.  Particularly, one where we could change
>> replication via UPDATE (or ALTER STANDBY) statements.
>
> I think that using a system catalog for this is going to be a
> non-starter, but we could use a flat file that is designed to be
> machine-editable (and thus avoid repeating the mistake we've made with
> postgresql.conf).

Yep, the standby registration information should be accessible and
changable while the server is not running. So using only system
catalog is not an answer.

My patch has implemented standbys.conf which was proposed before.
This format is the almost same as the pg_hba.conf. Is this
machine-editable, you think? If not, we should the format to
something like xml?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On 21 September 2010 09:29, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Sun, Sep 19, 2010 at 7:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sat, Sep 18, 2010 at 5:42 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> There are considerable benefits to having a standby registry with a
>>> table-like interface.  Particularly, one where we could change
>>> replication via UPDATE (or ALTER STANDBY) statements.
>>
>> I think that using a system catalog for this is going to be a
>> non-starter, but we could use a flat file that is designed to be
>> machine-editable (and thus avoid repeating the mistake we've made with
>> postgresql.conf).
>
> Yep, the standby registration information should be accessible and
> changable while the server is not running. So using only system
> catalog is not an answer.
>
> My patch has implemented standbys.conf which was proposed before.
> This format is the almost same as the pg_hba.conf. Is this
> machine-editable, you think? If not, we should the format to
> something like xml?

I really don't think an XML config would improve anything. In fact it
would just introduce more ways to break the config by the mere fact it
has to be well-formed. I'd be in favour of one similar to
pg_hba.conf, because then, at least, we'd still only have 2 formats of
configuration.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Tue, Sep 21, 2010 at 9:34 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> I really don't think an XML config would improve anything.  In fact it
> would just introduce more ways to break the config by the mere fact it
> has to be well-formed.  I'd be in favour of one similar to
> pg_hba.conf, because then, at least, we'd still only have 2 formats of
> configuration.

Want to spend a few days hacking on a config editor for pgAdmin, and
then re-evaluate that comment?

:-)

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On 21 September 2010 09:37, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Tue, Sep 21, 2010 at 9:34 AM, Thom Brown <thom(at)linux(dot)com> wrote:
>> I really don't think an XML config would improve anything.  In fact it
>> would just introduce more ways to break the config by the mere fact it
>> has to be well-formed.  I'd be in favour of one similar to
>> pg_hba.conf, because then, at least, we'd still only have 2 formats of
>> configuration.
>
> Want to spend a few days hacking on a config editor for pgAdmin, and
> then re-evaluate that comment?

It would be quicker to add in support for a config format we don't use
yet than to duplicate support for a new config in the same format as
an existing one? Plus it's a compromise between user-screw-up-ability
and machine-readability.

My fear would be standby.conf would be edited by users who don't
really know XML and then we'd have 3 different styles of config to
tell the user to edit.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Mon, Sep 20, 2010 at 3:27 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> However, the "wait forever" behavior becomes useful if you have a monitoring
> application outside the DB that decides when enough is enough and tells the
> DB that the slave can be considered dead. So "wait forever" actually means
> "wait until I tell you that you can give up". The monitoring application can
> STONITH to ensure that the slave stays down, before letting the master
> proceed with the commit.

This is also useful for preventing a failover from causing some data loss
by promoting the lagged standby to the master. To avoid any data loss, we
must STONITH the standby before any transactions resume on the master, when
replication connection is terminated or the crash of the standby happens.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On 21/09/10 11:52, Thom Brown wrote:
> My fear would be standby.conf would be edited by users who don't
> really know XML and then we'd have 3 different styles of config to
> tell the user to edit.

I'm not a big fan of XML either. That said, the format could use some
hierarchy. If we add many more per-server options, one server per line
will quickly become unreadable.

Perhaps something like the ini-file syntax Robert Haas just made up
elsewhere in this thread:

-------
globaloption1 = value

[servername1]
synchronization_level = async
option1 = value

[servername2]
synchronization_level = replay
option2 = value1
-------

I'm not sure I like the ini-file style much, but the two-level structure
it provides seems like a perfect match.

Then again, maybe we should go with something like json or yaml that
would allow deeper hierarchies for the sake of future expandability. Oh,
and there Dimitri's idea of "service levels" for per-transaction control
(http://archives.postgresql.org/message-id/m2sk1868hb.fsf@hi-media.com):

> sync_rep_services = {critical: recv=2, fsync=2, replay=1;
> important: fsync=3;
> reporting: recv=2, apply=1}

We'll need to accommodate something like that too.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> On 21/09/10 11:52, Thom Brown wrote:
>> My fear would be standby.conf would be edited by users who don't
>> really know XML and then we'd have 3 different styles of config to
>> tell the user to edit.

> I'm not a big fan of XML either.
> ...
> Then again, maybe we should go with something like json or yaml

The fundamental problem with all those "machine editable" formats is
that they aren't "people editable". If you have to have a tool (other
than a text editor) to change a config file, you're going to be very
unhappy when things are broken at 3AM and you're trying to fix it
while ssh'd in from your phone.

I think the "ini file" format suggestion is probably a good one; it
seems to fit this problem, and it's something that people are used to.
We could probably shoehorn the info into a pg_hba-like format, but
I'm concerned about whether we'd be pushing that format beyond what
it can reasonably handle.

regards, tom lane

On Tue, Sep 21, 2010 at 11:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> On 21/09/10 11:52, Thom Brown wrote:
>>> My fear would be standby.conf would be edited by users who don't
>>> really know XML and then we'd have 3 different styles of config to
>>> tell the user to edit.
>
>> I'm not a big fan of XML either.
>> ...
>> Then again, maybe we should go with something like json or yaml
>
> The fundamental problem with all those "machine editable" formats is
> that they aren't "people editable".  If you have to have a tool (other
> than a text editor) to change a config file, you're going to be very
> unhappy when things are broken at 3AM and you're trying to fix it
> while ssh'd in from your phone.

Agreed. Although, if things are broken at 3AM and I'm trying to fix
it while ssh'd in from my phone, I reserve the right to be VERY
unhappy no matter what format the file is in. :-)

> I think the "ini file" format suggestion is probably a good one; it
> seems to fit this problem, and it's something that people are used to.
> We could probably shoehorn the info into a pg_hba-like format, but
> I'm concerned about whether we'd be pushing that format beyond what
> it can reasonably handle.

It's not clear how many attributes we'll want to associate with a
server. Simon seems to think we can keep it to zero; I think it's
positive but I can't say for sure how many there will eventually be.
It may also be that a lot of the values will be optional things that
are frequently left unspecified. Both of those make me think that a
columnar format is probably not best.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Tue, 2010-09-21 at 16:58 +0900, Fujii Masao wrote:
> On Sat, Sep 18, 2010 at 4:36 AM, Dimitri Fontaine
> <dfontaine(at)hi-media(dot)com> wrote:
> > Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> >> On Fri, 2010-09-17 at 21:20 +0900, Fujii Masao wrote:
> >>> What synchronization level does each combination of sync_replication
> >>> and sync_replication_service lead to?
> >>
> >> There are only 4 possible outcomes. There is no combination, so we don't
> >> need a table like that above.
> >>
> >> The "service" specifies the highest request type available from that
> >> specific standby. If someone requests a higher service than is currently
> >> offered by this standby, they will either
> >> a) get that service from another standby that does offer that level
> >> b) automatically downgrade the sync rep mode to the highest available.
> >
> > I like the a) part, I can't say the same about the b) part. There's no
> > reason to accept to COMMIT a transaction when the requested durability
> > is known not to have been reached, unless the user said so.

Hmm, no reason? The reason is that the alternative is that the session
would hang until a standby arrived that offered that level of service.
Why would you want that behaviour? Would you really request that option?

> Yep, I can imagine that some people want to ensure that *all* the
> transactions are synchronously replicated to the synchronous standby,
> without regard to sync_replication. So I'm not sure if automatic
> downgrade/upgrade of the mode makes sense. We should introduce new
> parameter specifying whether to allow automatic degrade/upgrade or not?
> It seems complicated though.

I agree, but I'm not against any additional parameter if people say they
really want them *after* the consequences of those choices have been
highlighted.

IMHO we should focus on the parameters that deliver key use cases.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

Robert Haas wrote:
> On Tue, Sep 21, 2010 at 11:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> >> On 21/09/10 11:52, Thom Brown wrote:
> >>> My fear would be standby.conf would be edited by users who don't
> >>> really know XML and then we'd have 3 different styles of config to
> >>> tell the user to edit.
> >
> >> I'm not a big fan of XML either.
> >> ...
> >> Then again, maybe we should go with something like json or yaml
> >
> > The fundamental problem with all those "machine editable" formats is
> > that they aren't "people editable". ?If you have to have a tool (other
> > than a text editor) to change a config file, you're going to be very
> > unhappy when things are broken at 3AM and you're trying to fix it
> > while ssh'd in from your phone.
>
> Agreed. Although, if things are broken at 3AM and I'm trying to fix
> it while ssh'd in from my phone, I reserve the right to be VERY
> unhappy no matter what format the file is in. :-)
>
> > I think the "ini file" format suggestion is probably a good one; it
> > seems to fit this problem, and it's something that people are used to.
> > We could probably shoehorn the info into a pg_hba-like format, but
> > I'm concerned about whether we'd be pushing that format beyond what
> > it can reasonably handle.
>
> It's not clear how many attributes we'll want to associate with a
> server. Simon seems to think we can keep it to zero; I think it's
> positive but I can't say for sure how many there will eventually be.
> It may also be that a lot of the values will be optional things that
> are frequently left unspecified. Both of those make me think that a
> columnar format is probably not best.

Crazy idea, but could we use format like postgresql.conf by extending
postgresql.conf syntax, e.g.:

server1.failover = false
server1.keep_connect = true

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

> That said, the timeout option also feels a bit wishy-washy to me. With a
> timeout, acknowledgment of a commit means "your transaction is safely
> committed in the master and slave. Or not, if there was some glitch with
> the slave". That doesn't seem like a very useful guarantee; if you're
> happy with that why not just use async replication?

Ah, I wasn't clear. My thought was that a standby which exceeds the
timeout would be marked as "nonresponsive" and no longer included in the
list of standbys which needed to be synchronized. That is, the timeout
would be a timeout which says "this standby is down".

> So the only case where standby registration is required is where you
> deliberately choose to *not* have N+1 redundancy and then yet still
> require all N standbys to acknowledge. That is a suicidal config and
> nobody would sanely choose that. It's not a large or useful use case for
> standby reg. (But it does raise the question again of whether we need
> quorum commit).

Thinking of this as a sysadmin, what I want is to have *one place* I can
go an troubleshoot my standby setup. If I have 12 synch standbys and
they're creating too much load on the master, and I want to change half
of them to async, I don't want to have to ssh into 6 different machines
to do so. If one standby needs to be taken out of the network because
it's too slow, I want to be able to log in to the master and instantly
identify which standby is lagging and remove it there.

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

> Crazy idea, but could we use format like postgresql.conf by extending
> postgresql.conf syntax, e.g.:
>
> server1.failover = false
> server1.keep_connect = true
>

Why is this in the config file at all. It should be:

synchronous_replication = TRUE/FALSE

then

ALTER CLUSTER ENABLE REPLICATION FOR FOO;
ALTER CLUSTER SET keep_connect ON FOO TO TRUE;

Or some such thing.

Sincerely,

Joshua D. Drake

> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +

--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On 22/09/10 03:25, Joshua D. Drake wrote:
> Why is this in the config file at all. It should be:
>
> synchronous_replication = TRUE/FALSE

Umm, what does this do?

> then
>
> ALTER CLUSTER ENABLE REPLICATION FOR FOO;
> ALTER CLUSTER SET keep_connect ON FOO TO TRUE;
>
> Or some such thing.

I like a configuration file more because you can easily add comments,
comment out lines, etc. It also makes it easier to have a different
configuration in master and standby. We don't support cascading slaves,
yet, but you might still want a different configuration in master and
slave, waiting for the moment that the slave is promoted to a new master.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On 21/09/10 18:12, Tom Lane wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> On 21/09/10 11:52, Thom Brown wrote:
>>> My fear would be standby.conf would be edited by users who don't
>>> really know XML and then we'd have 3 different styles of config to
>>> tell the user to edit.
>
>> I'm not a big fan of XML either.
>> ...
>> Then again, maybe we should go with something like json or yaml
>
> The fundamental problem with all those "machine editable" formats is
> that they aren't "people editable". If you have to have a tool (other
> than a text editor) to change a config file, you're going to be very
> unhappy when things are broken at 3AM and you're trying to fix it
> while ssh'd in from your phone.

I'm not very familiar with any of those formats, but I agree it needs to
be easy to edit by hand first and foremost.

> I think the "ini file" format suggestion is probably a good one; it
> seems to fit this problem, and it's something that people are used to.
> We could probably shoehorn the info into a pg_hba-like format, but
> I'm concerned about whether we'd be pushing that format beyond what
> it can reasonably handle.

The ini file format seems to be enough for the features proposed this
far, but I'm a bit concerned that even that might not be flexible enough
for future features. I guess we'll cross the bridge when we get there
and go with an ini file for now. It should be possible to extend it in
various ways, and in the worst case that we have to change to a
completely different format, we can provide a how to guide on converting
existing config files to the new format.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Hi,

On 09/21/2010 08:05 PM, Simon Riggs wrote:
> Hmm, no reason? The reason is that the alternative is that the session
> would hang until a standby arrived that offered that level of service.
> Why would you want that behaviour? Would you really request that option?

I think I now agree with Simon on that point. It's only an issue in
multi-master replication, where continued operation would lead to a
split-brain situation.

With master-slave, you only need to make sure your master stays the
master even if the standby crash(es) are followed by a master crash. If
your cluster-ware is too clever and tries a fail-over on a slave that's
quicker to come up, you get the same split-brain situation.

Put another way: if you let your master continue, don't ever try a
fail-over after a full-cluster crash.

Regards

Markus Wanner

On 09/22/2010 04:18 AM, Heikki Linnakangas wrote:
> On 21/09/10 18:12, Tom Lane wrote:
>> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>>> On 21/09/10 11:52, Thom Brown wrote:
>>>> My fear would be standby.conf would be edited by users who don't
>>>> really know XML and then we'd have 3 different styles of config to
>>>> tell the user to edit.
>>
>>> I'm not a big fan of XML either.
>>> ...
>>> Then again, maybe we should go with something like json or yaml
>>
>> The fundamental problem with all those "machine editable" formats is
>> that they aren't "people editable". If you have to have a tool (other
>> than a text editor) to change a config file, you're going to be very
>> unhappy when things are broken at 3AM and you're trying to fix it
>> while ssh'd in from your phone.
>
> I'm not very familiar with any of those formats, but I agree it needs
> to be easy to edit by hand first and foremost.
>
>> I think the "ini file" format suggestion is probably a good one; it
>> seems to fit this problem, and it's something that people are used to.
>> We could probably shoehorn the info into a pg_hba-like format, but
>> I'm concerned about whether we'd be pushing that format beyond what
>> it can reasonably handle.
>
> The ini file format seems to be enough for the features proposed this
> far, but I'm a bit concerned that even that might not be flexible
> enough for future features. I guess we'll cross the bridge when we get
> there and go with an ini file for now. It should be possible to extend
> it in various ways, and in the worst case that we have to change to a
> completely different format, we can provide a how to guide on
> converting existing config files to the new format.

The ini file format is not flexible enough, IMNSHO. If we're going to
adopt a new config file format it should have these characteristics,
among others:

* well known (let's not invent a new one)
* supports hierarchical structure
* reasonably readable

I realize that the last is very subjective. Personally, I'm very
comfortable with XML, but then I do a *lot* of work with it, and have
for many years. I know I'm in a minority on that, and some people just
go bananas when they see it. Since we're just about to add a JSON parser
to the backend, by the look of it, that looks like a reasonable bet.
Maybe it uses a few too many quotes, but that's not really so hard to
get your head around, even if it offends you a bit aesthetically. And it
is certainly fairly widely known.

cheers

andrew

On Wed, Sep 22, 2010 at 9:47 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> The ini file format is not flexible enough, IMNSHO. If we're going to adopt
> a new config file format it should have these characteristics, among others:
>
> well known (let's not invent a new one)
> supports hierarchical structure
> reasonably readable

The ini format meets all of those requirements - and it's certainly
far more readable/editable than XML and friends.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On 09/22/2010 04:54 AM, Dave Page wrote:
> On Wed, Sep 22, 2010 at 9:47 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> The ini file format is not flexible enough, IMNSHO. If we're going to adopt
>> a new config file format it should have these characteristics, among others:
>>
>> well known (let's not invent a new one)
>> supports hierarchical structure
>> reasonably readable
> The ini format meets all of those requirements - and it's certainly
> far more readable/editable than XML and friends.
>

No, it's really not hierarchical. It only has goes one level deep.

cheers

andrew

On Wed, Sep 22, 2010 at 12:07 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 09/22/2010 04:54 AM, Dave Page wrote:
>>
>> On Wed, Sep 22, 2010 at 9:47 AM, Andrew Dunstan<andrew(at)dunslane(dot)net>
>>  wrote:
>>>
>>> The ini file format is not flexible enough, IMNSHO. If we're going to
>>> adopt
>>> a new config file format it should have these characteristics, among
>>> others:
>>>
>>> well known (let's not invent a new one)
>>> supports hierarchical structure
>>> reasonably readable
>>
>> The ini format meets all of those requirements - and it's certainly
>> far more readable/editable than XML and friends.
>>
>
> No, it's really not hierarchical. It only has goes one level deep.

I guess pgAdmin/wxWidgets are broken then :-)

[Servers]
Count=5
[Servers/1]
Server=localhost
Description=PostgreSQL 8.3
ServiceID=
DiscoveryID=/PostgreSQL/8.3
Port=5432
StorePwd=true
Restore=false
Database=postgres
Username=postgres
LastDatabase=postgres
LastSchema=public
DbRestriction=
Colour=#FFFFFF
SSL=0
Group=PPAS
Rolename=
[Servers/1/Databases]
[Servers/1/Databases/postgres]
SchemaRestriction=
[Servers/1/Databases/pphq]
SchemaRestriction=
[Servers/1/Databases/template_postgis]
SchemaRestriction=
[Servers/2]
...
...

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On ons, 2010-09-22 at 12:20 +0100, Dave Page wrote:
> > No, it's really not hierarchical. It only has goes one level deep.
>
> I guess pgAdmin/wxWidgets are broken then :-)
>
> [Servers]
> Count=5
> [Servers/1]
> Server=localhost

Well, by that logic, even what we have now for postgresql.conf is
hierarchical.

I think the criterion was rather meant to be

- can represent hierarchies without repeating intermediate node names

(Note: no opinion on which format is better for the task at hand)

On 09/22/2010 07:20 AM, Dave Page wrote:
> On Wed, Sep 22, 2010 at 12:07 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>>
>> On 09/22/2010 04:54 AM, Dave Page wrote:
>>> On Wed, Sep 22, 2010 at 9:47 AM, Andrew Dunstan<andrew(at)dunslane(dot)net>
>>> wrote:
>>>> The ini file format is not flexible enough, IMNSHO. If we're going to
>>>> adopt
>>>> a new config file format it should have these characteristics, among
>>>> others:
>>>>
>>>> well known (let's not invent a new one)
>>>> supports hierarchical structure
>>>> reasonably readable
>>> The ini format meets all of those requirements - and it's certainly
>>> far more readable/editable than XML and friends.
>>>
>> No, it's really not hierarchical. It only has goes one level deep.
> I guess pgAdmin/wxWidgets are broken then :-)
>
> [Servers]
> Count=5
> [Servers/1]
> Server=localhost
> Description=PostgreSQL 8.3
> ServiceID=
> DiscoveryID=/PostgreSQL/8.3
> Port=5432
> StorePwd=true
> Restore=false
> Database=postgres
> Username=postgres
> LastDatabase=postgres
> LastSchema=public
> DbRestriction=
> Colour=#FFFFFF
> SSL=0
> Group=PPAS
> Rolename=
> [Servers/1/Databases]
> [Servers/1/Databases/postgres]
> SchemaRestriction=
> [Servers/1/Databases/pphq]
> SchemaRestriction=
> [Servers/1/Databases/template_postgis]
> SchemaRestriction=
> [Servers/2]
> ...
> ...

Well, that's not what I'd call a hierarchy, in any sane sense. I've
often had to dig all over the place in ini files to find related bits of
information in disparate parts of the file. Compared to a meaningful
tree structure this is utterly woeful. In a sensible hierarchical
format, all the information relating to, say, Servers/1 above, wopuld be
under a stanza with that heading, instead of having separate and
unnested stanzas like Servers/1/Databases/template_postgis.

If you could nest stanzas in ini file format it would probably do, but
you can't, leading to the above major ugliness.

cheers

andrew

On Wed, Sep 22, 2010 at 12:50 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On ons, 2010-09-22 at 12:20 +0100, Dave Page wrote:
>> > No, it's really not hierarchical. It only has goes one level deep.
>>
>> I guess pgAdmin/wxWidgets are broken then :-)
>>
>> [Servers]
>> Count=5
>> [Servers/1]
>> Server=localhost
>
> Well, by that logic, even what we have now for postgresql.conf is
> hierarchical.

Well, yes - if you consider add-in GUCs which use prefixing like foo.setting=...

> I think the criterion was rather meant to be
>
> - can represent hierarchies without repeating intermediate node names

If this were data, I could understand that as it could lead to
tremendous bloat, but as a config file, I'd rather have the
readability of the ini format, despite the repeated node names, than
have to hack XML files by hand.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On Tue, 2010-09-21 at 17:04 -0700, Josh Berkus wrote:

> > That said, the timeout option also feels a bit wishy-washy to me. With a
> > timeout, acknowledgment of a commit means "your transaction is safely
> > committed in the master and slave. Or not, if there was some glitch with
> > the slave". That doesn't seem like a very useful guarantee; if you're
> > happy with that why not just use async replication?
>
> Ah, I wasn't clear. My thought was that a standby which exceeds the
> timeout would be marked as "nonresponsive" and no longer included in the
> list of standbys which needed to be synchronized. That is, the timeout
> would be a timeout which says "this standby is down".
>
> > So the only case where standby registration is required is where you
> > deliberately choose to *not* have N+1 redundancy and then yet still
> > require all N standbys to acknowledge. That is a suicidal config and
> > nobody would sanely choose that. It's not a large or useful use case for
> > standby reg. (But it does raise the question again of whether we need
> > quorum commit).

This is becoming very confusing. Some people advocating "standby
registration" have claimed it allows capabilities which aren't possible
any other way; all but one of those claims has so far been wrong - the
remaining case is described above. If I'm the one that is wrong, please
tell me where I erred.

> Thinking of this as a sysadmin, what I want is to have *one place* I can
> go an troubleshoot my standby setup. If I have 12 synch standbys and
> they're creating too much load on the master, and I want to change half
> of them to async, I don't want to have to ssh into 6 different machines
> to do so. If one standby needs to be taken out of the network because
> it's too slow, I want to be able to log in to the master and instantly
> identify which standby is lagging and remove it there.

The above case is one where I can see your point and it does sound
easier in that case. But I then think: "What happens after failover?".
We would then need to have 12 different standby.conf files, one on each
standby that describes what the setup would look like if that standby
became the master. And guess what, every time we made a change on the
master, you'd need to re-edit all 12 standby.conf files to reflect the
new configuration. So we're still back to having to edit in multiple
places, ISTM.

Please, please, somebody write down what the design proposal is *before*
we make a decision on whether it is a sensible way to proceed. It would
be good to see a few options written down and some objective analysis of
which way is best to let people decide.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

On 09/22/2010 07:57 AM, Dave Page wrote:
> On Wed, Sep 22, 2010 at 12:50 PM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
>> On ons, 2010-09-22 at 12:20 +0100, Dave Page wrote:
>>>> No, it's really not hierarchical. It only has goes one level deep.
>>> I guess pgAdmin/wxWidgets are broken then :-)
>>>
>>> [Servers]
>>> Count=5
>>> [Servers/1]
>>> Server=localhost
>> Well, by that logic, even what we have now for postgresql.conf is
>> hierarchical.
> Well, yes - if you consider add-in GUCs which use prefixing like foo.setting=...
>
>> I think the criterion was rather meant to be
>>
>> - can represent hierarchies without repeating intermediate node names
> If this were data, I could understand that as it could lead to
> tremendous bloat, but as a config file, I'd rather have the
> readability of the ini format, despite the repeated node names, than
> have to hack XML files by hand.
>

XML is not the only alternative - please don't use it as a straw man.
For example, here is a fragment from the Bacula docs using their
hierarchical format:

FileSet {
Name = Test
Include {
File = /home/xxx/test
Options {
regex = ".*\.c$"
}
}
}

Or here is a piece from the buildfarm client config (which is in fact
perl, but could also be JSON or similar fairly easily):

mail_events =>
{
all => [],
fail => [],
change => ['foo(at)bar(dot)com', 'baz(at)blurfl(dot)org' ],
green => [],
},
build_env =>
{
CCACHE_DIR => "/home/andrew/pgfarmbuild/ccache/$branch",
},

cheers

andrew

On Wed, Sep 22, 2010 at 1:25 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> XML is not the only alternative - please don't use it as a straw man. For
> example, here is a fragment from the Bacula docs using their hierarchical
> format:
>
> FileSet {
> Name = Test
> Include {
> File = /home/xxx/test
> Options {
> regex = ".*\.c$"
> }
> }
> }
>
> Or here is a piece from the buildfarm client config (which is in fact perl,
> but could also be JSON or similar fairly easily):
>
> mail_events =>
> {
>     all => [],
>     fail => [],
>     change => ['foo(at)bar(dot)com', 'baz(at)blurfl(dot)org' ],
>     green => [],
> },
> build_env =>
> {
>     CCACHE_DIR => "/home/andrew/pgfarmbuild/ccache/$branch",
> },

Both of which I've also used in the past, and also find uncomfortable
and awkward for configuration files.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

On 09/22/2010 08:32 AM, Dave Page wrote:
> On Wed, Sep 22, 2010 at 1:25 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> XML is not the only alternative - please don't use it as a straw man. For
>> example, here is a fragment from the Bacula docs using their hierarchical
>> format:
>>
>> FileSet {
>> Name = Test
>> Include {
>> File = /home/xxx/test
>> Options {
>> regex = ".*\.c$"
>> }
>> }
>> }
>>
>> Or here is a piece from the buildfarm client config (which is in fact perl,
>> but could also be JSON or similar fairly easily):
>>
>> mail_events =>
>> {
>> all => [],
>> fail => [],
>> change => ['foo(at)bar(dot)com', 'baz(at)blurfl(dot)org' ],
>> green => [],
>> },
>> build_env =>
>> {
>> CCACHE_DIR => "/home/andrew/pgfarmbuild/ccache/$branch",
>> },
> Both of which I've also used in the past, and also find uncomfortable
> and awkward for configuration files.
>
>

I can't imagine trying to configure Bacula using ini file format - the
mind just boggles. Frankly, I'd rather stick with our current config
format than change to something as inadequate as ini file format.

cheers

andrew

On Wed, Sep 22, 2010 at 9:01 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> I can't imagine trying to configure Bacula using ini file format - the mind
> just boggles. Frankly, I'd rather stick with our current config format than
> change to something as inadequate as ini file format.

Perhaps we need to define a little better what information we think we
might eventually need to represent in the config file. With one
exception, nobody has suggested anything that would actually require
hierarchical structure. The exception is defining the policy for
deciding when a commit has been sufficiently acknowledged by an
adequate quorum of standbys, and it seems to me that doing that in its
full generality is going to require not so much a hierarchical
structure as a small programming language. The efforts so far have
centered around reducing the use cases that $AUTHOR cares about to a
set of GUCs which would satisfy that person's needs, but not
necessarily everyone else's needs. I think efforts to encode
arbitrary algorithms using configuration settings are doomed to
failure, so I'm unimpressed by the argument that we should design the
config file to support our attempts to do so. For everything else, no
one has suggested that we need anything more complex than,
essentially, a group of GUCs per server. So we could do:

[server]
guc=value

or

server.guc=value

...or something else. Designing this to support:

server.hypothesis.experimental.unproven.imaginary.what-in-the-world-could-this-possibly-be
= 42