Re: Sync Rep: First Thoughts on Code

Breaking down of patch into sections works very well for review. Should
allow us to get different reviewers on different parts of the code -
review wranglers please take note: Dave, Josh.

Can you confirm that all the docs on the Wiki page are up to date? There
are a few minor discrepancies that make me think it isn't.

Examples: "For example, to make a single multi-statement transaction
replication asynchronously when the default is the opposite, issue SET
LOCAL synchronous_commit TO OFF within the transaction."
Do we mean synchronous_replication in this sentence? I think you've
copied the text and not changed all of the necessary parts - please
re-read the whole section (probably the whole Wiki, actually).

"wal_writer_delay" - do we mean wal_sender_delay? Is there some ability
to measure the amount of data to be sent and avoid the delay altogether,
when the server is sufficiently busy?

The reaction to replication_timeout may need to be configurable. I might
not want to keep on processing if the information didn't reach the
standby. I would prefer in many cases that the transactions that were
waiting for walsender would abort, but the walsender kept processing.
How can we restart the walsender if it shuts down? Do we want a maximum
wait for a transaction and a maximum wait for the server? Do we report
stats on how long the replication has been taking? If the average rep
time is close to rep timeout then we will be fragile, so we need some
way to notice this and produce warnings. Or at least provide info to an
external monitoring system.

How do we specify the user we use to connect to primary?

Definitely need more explanatory comments/README-style docs.

For example, 03_libpq seems simple and self-contained. I'm not sure why
we have a state called PGASYNC_REPLICATION; I was hoping that would be
dynamic, but I'm not sure where to look for that. It would be useful to
have a very long comment within the code to explain how the replication
messages work, and note on each function who the intended client and
server is.

02_pqcomm: What does HAVE_POLL mean? Do we need to worry about periodic
renegotiation of keys in be-secure.c? Not sure I understand why so many
new functions in there.

04_recovery_conf is a change I agree with, though I think it may not
work with EXEC_BACKEND for Windows.

05... I need dome commentary to explain this better.

06 and 07 are large and will take substantial review time. So we must
get the overall architecture done first and then check the code that
implements that.

08 - I think I get this, but some docs will help to confirm.

09 pg_standby changes: so more changes are coming there? OK. Can we
refer to those two options as failover and switchover? There's no need
to change definitions that many Postgres people already use. This change
can be done without making any change to server behaviour, so this
change can have benefit to 8.2 and 8,3 people also.

01_signal_handling: I've looked at the LWlock acquires and releases in
the patch and am fairly happy, except for the ProcArrayLock acquire
during this sub-patch. Do we really need to do things this way? Is the
actual state important? Could we just do this with a counter which
cycles? So callers increment counter atomically and the reader just
polls to see if anybody has incremented? Or could we protect that part
of the proc with a different lock? Touching ProcArrayLock is bad news.

Anyway, feeling very positive about this. Hope we can get this reviewed
and committed in next 3-4 weeks.

I have many clues as to how to structure my own work also. Thanks.

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

Hi, Simon.

Thanks for taking many hours to review the code!!

On Mon, Dec 1, 2008 at 8:42 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Can you confirm that all the docs on the Wiki page are up to date? There
> are a few minor discrepancies that make me think it isn't.

Documentation is ongoing. Sorry for my slow progress.

BTW, I'm going to add and change the sgml files listed on wiki.
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Documentation_Plan

>
> Examples: "For example, to make a single multi-statement transaction
> replication asynchronously when the default is the opposite, issue SET
> LOCAL synchronous_commit TO OFF within the transaction."
> Do we mean synchronous_replication in this sentence? I think you've
> copied the text and not changed all of the necessary parts - please
> re-read the whole section (probably the whole Wiki, actually).

Oops! It's just typo. Sorry for the confusion.
I will revise this section.

>
> "wal_writer_delay" - do we mean wal_sender_delay?

Yes. I will fix it.

> Is there some ability
> to measure the amount of data to be sent and avoid the delay altogether,
> when the server is sufficiently busy?

Why is the former ability required?

The latter is possible, I think. We can guarantee that the WAL is sent (in
more detail, called send(2)) once at least per wal_sender_delay. Of course,
it's dependent on the scheduler of a kernel.

>
> The reaction to replication_timeout may need to be configurable. I might
> not want to keep on processing if the information didn't reach the
> standby.

OK. I will add new GUC variable (PGC_SIGHUP) to specify the reaction for
the timeout.

> I would prefer in many cases that the transactions that were
> waiting for walsender would abort, but the walsender kept processing.

Is it dangerous to abort the transaction with replication continued when
the timeout occurs? I think that the WAL consistency between two servers
might be broken. Because the WAL writing and sending are done concurrently,
and the backend might already write the WAL to disk on the primary when
waiting for walsender.

> How can we restart the walsender if it shuts down?

Only restart the standby (with walreceiver). The standby connects to
the postmaster on the primary, then the postmaster forks new walsender.

> Do we want a maximum
> wait for a transaction and a maximum wait for the server?

ISTM that these feature are too much.

> Do we report
> stats on how long the replication has been taking? If the average rep
> time is close to rep timeout then we will be fragile, so we need some
> way to notice this and produce warnings. Or at least provide info to an
> external monitoring system.

Sounds good. How about log_min_duration_replication? If the rep time
is greater than it, we produce warning (or log) like log_min_duration_xx.

>
> How do we specify the user we use to connect to primary?

Yes, I need to add new option to specify the user name into
recovery.conf. Thanks for reminding me!

>
> Definitely need more explanatory comments/README-style docs.

Completely agreed ;-)
I will write README together with other documents.

>
> For example, 03_libpq seems simple and self-contained. I'm not sure why
> we have a state called PGASYNC_REPLICATION; I was hoping that would be
> dynamic, but I'm not sure where to look for that. It would be useful to
> have a very long comment within the code to explain how the replication
> messages work, and note on each function who the intended client and
> server is.
>

OK. I will re-consider whether PGASYNC_REPLICATION is removable, and
write the comment about it.

> 02_pqcomm: What does HAVE_POLL mean?

It identifies whether poll(2) is available or not on the platform. We
use poll(2)
if it's defined, otherwise select(2). There is similar code at pqSocketPoll() in
fe-misc.c.

> Do we need to worry about periodic
> renegotiation of keys in be-secure.c?

What is "keys" you mean?

> Not sure I understand why so many
> new functions in there.

It's because walsender waits for the reply from the standby and the
request from the backend concurrently. So, we need poll(2) or select(2)
to make walsender wait for them, and some functions for non-blocking
receiving.

>
> 04_recovery_conf is a change I agree with, though I think it may not
> work with EXEC_BACKEND for Windows.

OK. I will examine and fix it.

>
> 05... I need dome commentary to explain this better.
>
> 06 and 07 are large and will take substantial review time. So we must
> get the overall architecture done first and then check the code that
> implements that.
>
> 08 - I think I get this, but some docs will help to confirm.

Yes. I need more documentation.

>
> 09 pg_standby changes: so more changes are coming there? OK. Can we
> refer to those two options as failover and switchover?

You mean failover trigger and switchover one? ISTM that those names
and features might not suit.

Naming always bother me, and the current name "commit/abort trigger"
might tend to cause confusion. Is there any other suitable name?

> There's no need
> to change definitions that many Postgres people already use. This change
> can be done without making any change to server behaviour, so this
> change can have benefit to 8.2 and 8,3 people also.

Agreed.

>
> 01_signal_handling: I've looked at the LWlock acquires and releases in
> the patch and am fairly happy, except for the ProcArrayLock acquire
> during this sub-patch. Do we really need to do things this way? Is the
> actual state important? Could we just do this with a counter which
> cycles? So callers increment counter atomically and the reader just
> polls to see if anybody has incremented? Or could we protect that part
> of the proc with a different lock? Touching ProcArrayLock is bad news.

Agreed. I will add new lock for proc.signalFlags.

>
> Anyway, feeling very positive about this. Hope we can get this reviewed
> and committed in next 3-4 weeks.
>
> I have many clues as to how to structure my own work also. Thanks.

Thanks again!

Regards,

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

On Tue, 2008-12-02 at 21:37 +0900, Fujii Masao wrote:

> Thanks for taking many hours to review the code!!
>
> On Mon, Dec 1, 2008 at 8:42 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > Can you confirm that all the docs on the Wiki page are up to date? There
> > are a few minor discrepancies that make me think it isn't.
>
> Documentation is ongoing. Sorry for my slow progress.
>
> BTW, I'm going to add and change the sgml files listed on wiki.
> http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Documentation_Plan

I'm patient, I know it takes time. Happy to spend hours on the review,
but I want to do that knowing I agree with the higher level features and
architecture first.

This was just a first review, I expect to spend more time on it yet.

> > The reaction to replication_timeout may need to be configurable. I might
> > not want to keep on processing if the information didn't reach the
> > standby.
>
> OK. I will add new GUC variable (PGC_SIGHUP) to specify the reaction for
> the timeout.
>
> > I would prefer in many cases that the transactions that were
> > waiting for walsender would abort, but the walsender kept processing.
>
> Is it dangerous to abort the transaction with replication continued when
> the timeout occurs? I think that the WAL consistency between two servers
> might be broken. Because the WAL writing and sending are done concurrently,
> and the backend might already write the WAL to disk on the primary when
> waiting for walsender.

The issue I see is that we might want to keep wal_sender_delay small so
that transaction times are not increased. But we also want
wal_sender_delay high so that replication never breaks. It seems better
to have the action on wal_sender_delay configurable if we have an
unsteady network (like the internet). Marcus made some comments on line
dropping that seem relevant here; we should listen to his experience.

Hmmm, dangerous? Well assuming we're linking commits with replication
sends then it sounds it. We might end up committing to disk and then
deciding to abort instead. But remember we don't remove the xid from
procarray or mark the result in clog until the flush is over, so it is
possible. But I think we should discuss this in more detail when the
main patch is committed.

> > Do we report
> > stats on how long the replication has been taking? If the average rep
> > time is close to rep timeout then we will be fragile, so we need some
> > way to notice this and produce warnings. Or at least provide info to an
> > external monitoring system.
>
> Sounds good. How about log_min_duration_replication? If the rep time
> is greater than it, we produce warning (or log) like log_min_duration_xx.

Maybe, lets put in something that logs if >50% (?) of timeout. Make that
configurable with a #define and see if we need that to be configurable
with a GUC later.

> > Do we need to worry about periodic
> > renegotiation of keys in be-secure.c?
>
> What is "keys" you mean?

See the notes in that file for explanation.

I wondered whether it might be a perf problem for us?

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

On Tue, 2008-12-02 at 13:09 +0000, Simon Riggs wrote:
> > Is it dangerous to abort the transaction with replication continued when
> > the timeout occurs? I think that the WAL consistency between two servers
> > might be broken. Because the WAL writing and sending are done concurrently,
> > and the backend might already write the WAL to disk on the primary when
> > waiting for walsender.
>
> The issue I see is that we might want to keep wal_sender_delay small so
> that transaction times are not increased. But we also want
> wal_sender_delay high so that replication never breaks. It seems better
> to have the action on wal_sender_delay configurable if we have an
> unsteady network (like the internet). Marcus made some comments on line
> dropping that seem relevant here; we should listen to his experience.
>
> Hmmm, dangerous? Well assuming we're linking commits with replication
> sends then it sounds it. We might end up committing to disk and then
> deciding to abort instead. But remember we don't remove the xid from
> procarray or mark the result in clog until the flush is over, so it is
> possible. But I think we should discuss this in more detail when the
> main patch is committed.
>

What is the "it" in "it is possible"? It seems like there's still a
problem window in there.

Even if that could be made safe, in the event of a real network failure,
you'd just wait the full timeout every transaction, because it still
thinks it's replicating.

If the timeout is exceeded, it seems more reasonable to abandon the
slave until you could re-sync it and continue processing as normal. As
you pointed out, that's not necessarily an expensive operation because
you can use something like rsync. The process of re-syncing might be
made easier (or perhaps less costly), of course.

If we want to still allow processing to happen after a timeout, it seems
reasonable to have a configurable option to allow/disallow non-read-only
transactions when out of sync.

Regards,
Jeff Davis

On Tue, 2008-12-02 at 11:08 -0800, Jeff Davis wrote:
> On Tue, 2008-12-02 at 13:09 +0000, Simon Riggs wrote:
> > > Is it dangerous to abort the transaction with replication continued when
> > > the timeout occurs? I think that the WAL consistency between two servers
> > > might be broken. Because the WAL writing and sending are done concurrently,
> > > and the backend might already write the WAL to disk on the primary when
> > > waiting for walsender.
> >
> > The issue I see is that we might want to keep wal_sender_delay small so
> > that transaction times are not increased. But we also want
> > wal_sender_delay high so that replication never breaks. It seems better
> > to have the action on wal_sender_delay configurable if we have an
> > unsteady network (like the internet). Marcus made some comments on line
> > dropping that seem relevant here; we should listen to his experience.
> >
> > Hmmm, dangerous? Well assuming we're linking commits with replication
> > sends then it sounds it. We might end up committing to disk and then
> > deciding to abort instead. But remember we don't remove the xid from
> > procarray or mark the result in clog until the flush is over, so it is
> > possible. But I think we should discuss this in more detail when the
> > main patch is committed.
> >
>
> What is the "it" in "it is possible"? It seems like there's still a
> problem window in there.

Marking a transaction aborted after we have written a commit record, but
before we have removed it from proc array and marked in clog. We'd need
a special kind of WAL record to do that.

> Even if that could be made safe, in the event of a real network failure,
> you'd just wait the full timeout every transaction, because it still
> thinks it's replicating.

True, but I did suggest having two timeouts.

There is considerable reason to reduce the timeout as well as reason to
increase it - at the same time.

Anyway, lets wait for some user experience following commit.

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

> Breaking down of patch into sections works very well for review. Should
> allow us to get different reviewers on different parts of the code -
> review wranglers please take note: Dave, Josh.

Fujii-san, could you break the patch up into several parts? We have quite
a few junior reviewers who are idle right now.

--
--Josh

Josh Berkus
PostgreSQL
San Francisco

Jeff,

> Even if that could be made safe, in the event of a real network failure,
> you'd just wait the full timeout every transaction, because it still
> thinks it's replicating.

Hmmm. I'd suggest that if we get timeouts for more than 10xTimeout value
in a row, that replication stops. Unfortunatley, we should probably make
that *another* configuration setting.

--
--Josh

Josh Berkus
PostgreSQL
San Francisco

Hi,

On Wed, Dec 3, 2008 at 6:03 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> Breaking down of patch into sections works very well for review. Should
>> allow us to get different reviewers on different parts of the code -
>> review wranglers please take note: Dave, Josh.
>
> Fujii-san, could you break the patch up into several parts? We have quite
> a few junior reviewers who are idle right now.

Yes, I divided the patch into 9 pieces. Do I need to divide it further?

Regards,

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

Fujii-san,

> Yes, I divided the patch into 9 pieces. Do I need to divide it further?

That's plenty. Where do reviews find the 9 pieces?

--
Josh Berkus
PostgreSQL
San Francisco

Hi,

On Wed, Dec 3, 2008 at 3:21 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Fujii-san,
>
>> Yes, I divided the patch into 9 pieces. Do I need to divide it further?
>
> That's plenty. Where do reviews find the 9 pieces?

The latest patch set (v4) is on wiki.
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Patch_set

Regards,

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

Hello,

On Tue, Dec 2, 2008 at 10:09 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> > The reaction to replication_timeout may need to be configurable. I might
>> > not want to keep on processing if the information didn't reach the
>> > standby.
>>
>> OK. I will add new GUC variable (PGC_SIGHUP) to specify the reaction for
>> the timeout.
>>
>> > I would prefer in many cases that the transactions that were
>> > waiting for walsender would abort, but the walsender kept processing.
>>
>> Is it dangerous to abort the transaction with replication continued when
>> the timeout occurs? I think that the WAL consistency between two servers
>> might be broken. Because the WAL writing and sending are done concurrently,
>> and the backend might already write the WAL to disk on the primary when
>> waiting for walsender.
>
> The issue I see is that we might want to keep wal_sender_delay small so
> that transaction times are not increased. But we also want
> wal_sender_delay high so that replication never breaks.

Are you assuming only asynch case? In synch case, since walsender is
awoken by the signal from the backend, we don't need to keep the delay
so small. And, wal_sender_delay has no relation with the mis-termination
of replication.

> It seems better
> to have the action on wal_sender_delay configurable if we have an
> unsteady network (like the internet). Marcus made some comments on line
> dropping that seem relevant here; we should listen to his experience.

OK, I would look for his comments. Please let me know which thread has
the comments if you know.

>
> Hmmm, dangerous? Well assuming we're linking commits with replication
> sends then it sounds it. We might end up committing to disk and then
> deciding to abort instead. But remember we don't remove the xid from
> procarray or mark the result in clog until the flush is over, so it is
> possible. But I think we should discuss this in more detail when the
> main patch is committed.

If the transaction is aborted while the backend is waiting for replication,
the transaction commit command returns "false" indication to the client.
But the transaction commit record might be written in the primary and
standby. As you say, it may not be dangerous as long as the primary is
alive. But, when we recover the failed primary, clog of the transaction
is marked with "success" because of the commit record. Is it safe?

And, in that case, the transaction is treated as "sucess" on the standby,
and visible for the read-only query. On the other hand, it's invisible on
the primary. Isn't it dangerous?

>
>> > Do we need to worry about periodic
>> > renegotiation of keys in be-secure.c?
>>
>> What is "keys" you mean?
>
> See the notes in that file for explanation.

Thanks! I would check it.

Regards,

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

Hi,

On Wed, Dec 3, 2008 at 4:08 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Even if that could be made safe, in the event of a real network failure,
> you'd just wait the full timeout every transaction, because it still
> thinks it's replicating.

If walsender detects a real network failure, the transaction doesn't need to
wait for the timeout. Configuring keepalive options would help walsender to
detect it. Of course, though keepalive on linux might not work as expected.

Regards,

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

Hi,

On Tue, Dec 2, 2008 at 10:09 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Tue, 2008-12-02 at 21:37 +0900, Fujii Masao wrote:
>
>> Thanks for taking many hours to review the code!!
>>
>> On Mon, Dec 1, 2008 at 8:42 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> > Can you confirm that all the docs on the Wiki page are up to date? There
>> > are a few minor discrepancies that make me think it isn't.
>>
>> Documentation is ongoing. Sorry for my slow progress.
>>
>> BTW, I'm going to add and change the sgml files listed on wiki.
>> http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Documentation_Plan
>
> I'm patient, I know it takes time. Happy to spend hours on the review,
> but I want to do that knowing I agree with the higher level features and
> architecture first.

Since I thought that the figure was more intelligible for some people
than my poor English, I illustrated the architecture first.
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design

Are there any other parts which should be illustrated for review?

Regards,

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

On Wed, 2008-12-03 at 21:37 +0900, Fujii Masao wrote:

> Since I thought that the figure was more intelligible for some people
> than my poor English, I illustrated the architecture first.
> http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design
>
> Are there any other parts which should be illustrated for review?

Those are very useful, thanks.

Some questions to check my understanding (expected answers in brackets)

* Diagram on p.2 has two Archives. We have just one (yes)

* We send data continuously, whether or not we are in sync/async? (yes)
So the only difference between sync/async is whether we wait when we
flush the commit? (yes)

* If we have synchronous_commit = off do we ignore
synchronous_replication = on (yes)

* If two transactions commit almost simultaneously and one is sync and
the other async then only the sync backend will wait? (Yes)

Do we definitely need the archiver to move the files written by
walreceiver to archive and then move them back out again? Seems like we
can streamline that part in many (all?) cases.

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

Hi,

On Wed, Dec 3, 2008 at 11:33 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I'm patient, I know it takes time. Happy to spend hours on the review,
> but I want to do that knowing I agree with the higher level features and
> architecture first.

I wrote the features and restrictions of Synch Rep. Please also check
it together with the figures of architecture.
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#User_Overview

> Some questions to check my understanding (expected answers in brackets)
>
> * Diagram on p.2 has two Archives. We have just one (yes)

No, we need archive in both the primary and standby. The primary needs
archive because a base backup is required when starting the standby.
Meanwhile, the standby needs archive for cooperating with pg_standby.

If the directory where pg_standby checks is the same as the directory
where walreceiver writes the WAL, the halfway WAL file might be
restored by pg_standby, and continuous recovery would fail. So, we have
to separate the directories, and I assigned pg_xlog and archive to them.

Another idea; walreceiver writes the WAL to the file with temporary name,
and rename it to the formal name when it fills. So, pg_standby doesn't
restore a halfway WAL file. But it's more difficult to perform the failover
because the unrenamed WAL file remains.

Do you have any other good idea?

>
> * We send data continuously, whether or not we are in sync/async? (yes)

Yes.

> So the only difference between sync/async is whether we wait when we
> flush the commit? (yes)

Yes.
And, in asynch case, the backend basically doesn't send the wakeup-signal
to walsender.

>
> * If we have synchronous_commit = off do we ignore
> synchronous_replication = on (yes)

No, we can configure them independently. synchronous_commit covers
only local writing of the WAL. If synch_*commit* should cover both local
writing and replication, I'd like to add new GUC which covers only local
writing (synchronous_local_write?).

>
> * If two transactions commit almost simultaneously and one is sync and
> the other async then only the sync backend will wait? (Yes)

Yes.

>
>
> Do we definitely need the archiver to move the files written by
> walreceiver to archive and then move them back out again?

Yes, it's because of cooperating with pg_standby.

> Seems like we
> can streamline that part in many (all?) cases.

Agreed. But I thought that such streaming was TODO of next time.

Regards,

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

Hi,

On Wed, Dec 3, 2008 at 3:38 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>> > Do we need to worry about periodic
>>> > renegotiation of keys in be-secure.c?
>>>
>>> What is "keys" you mean?
>>
>> See the notes in that file for explanation.
>
> Thanks! I would check it.

The key is used only when we use SSL for the connection of
replication. As far as I examined, secure_write() renegotiates
the key if needed. Since walsender calls secure_write() when
sending the WAL to the standby, the key is renegotiated
periodically. So, I think that we don't need to worry about the
obsolescence of the key. Am I missing something?

Regards,

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

On Thu, 2008-12-04 at 17:57 +0900, Fujii Masao wrote:

> On Wed, Dec 3, 2008 at 3:38 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> >>> > Do we need to worry about periodic
> >>> > renegotiation of keys in be-secure.c?
> >>>
> >>> What is "keys" you mean?
> >>
> >> See the notes in that file for explanation.
> >
> > Thanks! I would check it.
>
> The key is used only when we use SSL for the connection of
> replication. As far as I examined, secure_write() renegotiates
> the key if needed. Since walsender calls secure_write() when
> sending the WAL to the standby, the key is renegotiated
> periodically. So, I think that we don't need to worry about the
> obsolescence of the key.

Understood. Is the periodic renegotiation of keys something that would
interfere with the performance or robustness of replication? Is the
delay likely to effect sync rep? I'm just checking we've thought about
it.

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

On Thu, 2008-12-04 at 16:10 +0900, Fujii Masao wrote:

> > * Diagram on p.2 has two Archives. We have just one (yes)
>
> No, we need archive in both the primary and standby. The primary needs
> archive because a base backup is required when starting the standby.
> Meanwhile, the standby needs archive for cooperating with pg_standby.
>
> If the directory where pg_standby checks is the same as the directory
> where walreceiver writes the WAL, the halfway WAL file might be
> restored by pg_standby, and continuous recovery would fail. So, we have
> to separate the directories, and I assigned pg_xlog and archive to them.
>
> Another idea; walreceiver writes the WAL to the file with temporary name,
> and rename it to the formal name when it fills. So, pg_standby doesn't
> restore a halfway WAL file. But it's more difficult to perform the failover
> because the unrenamed WAL file remains.

WAL sending is either via archiver or via streaming. We must switch
cleanly from one mode to the other and not half-way through a WAL file.

When WAL sending is about to begin, issue xlog switch. Then tell
archiver to shutdown once it has got to the last file. All files after
that point are streamed. So there need be no conflict in filename.

We must avoid having two archives, because people will configure this
incorrectly.

> > * If we have synchronous_commit = off do we ignore
> > synchronous_replication = on (yes)
>
> No, we can configure them independently. synchronous_commit covers
> only local writing of the WAL. If synch_*commit* should cover both local
> writing and replication, I'd like to add new GUC which covers only local
> writing (synchronous_local_write?).

The only sensible settings are
synchronous_commit = on, synchronous_replication = on
synchronous_commit = on, synchronous_replication = off
synchronous_commit = off, synchronous_replication = off

This doesn't make any sense: (does it??)
synchronous_commit = off, synchronous_replication = on

> > Do we definitely need the archiver to move the files written by
> > walreceiver to archive and then move them back out again?
>
> Yes, it's because of cooperating with pg_standby.

It seems very easy to make this happen the way we want. We could make
pg_standby look into pg_xlog also, for example.

I was expecting you to have walreceiver and startup share an end of WAL
address via shared memory, so that startup never tries to read past end.
That way we would be able to begin reading a WAL file *before* it was
filled. Waiting until a file fills means we still have to have
archive_timeout set to ensure we switch regularly.

We need the existing mechanisms for the start of replication (base
backup etc..) but we don't need them after that point.

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

Simon Riggs wrote:
> On Thu, 2008-12-04 at 17:57 +0900, Fujii Masao wrote:
>
>> On Wed, Dec 3, 2008 at 3:38 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>>>>> Do we need to worry about periodic
>>>>>> renegotiation of keys in be-secure.c?
>>>>> What is "keys" you mean?
>>>> See the notes in that file for explanation.
>>> Thanks! I would check it.
>> The key is used only when we use SSL for the connection of
>> replication. As far as I examined, secure_write() renegotiates
>> the key if needed. Since walsender calls secure_write() when
>> sending the WAL to the standby, the key is renegotiated
>> periodically. So, I think that we don't need to worry about the
>> obsolescence of the key.
>
> Understood. Is the periodic renegotiation of keys something that would
> interfere with the performance or robustness of replication? Is the
> delay likely to effect sync rep? I'm just checking we've thought about
> it.

It will certainly add an extra piece of delay. But if you are worried
about performance for it, you are likely not running SSL. Plus, if you
don't renegotiate the key, you gamble with security.

If it does have a negative effect on the robustness of the replication,
we should just recommend against using it - or refuse to use - not
disable renegotiation.

/Magnus

On Thu, 2008-12-04 at 12:41 +0100, Magnus Hagander wrote:

> > Understood. Is the periodic renegotiation of keys something that would
> > interfere with the performance or robustness of replication? Is the
> > delay likely to effect sync rep? I'm just checking we've thought about
> > it.
>
> It will certainly add an extra piece of delay. But if you are worried
> about performance for it, you are likely not running SSL. Plus, if you
> don't renegotiate the key, you gamble with security.
>
> If it does have a negative effect on the robustness of the replication,
> we should just recommend against using it - or refuse to use - not
> disable renegotiation.

I didn't mean to imply renegotiation might optional. I just wanted to
check whether there is anything to worry about as a result of it, there
may not be. *If* it took a long time, I would not want sync commits to
wait for it.

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

Hi,

On Thu, Dec 4, 2008 at 6:29 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> The only sensible settings are
> synchronous_commit = on, synchronous_replication = on
> synchronous_commit = on, synchronous_replication = off
> synchronous_commit = off, synchronous_replication = off
>
> This doesn't make any sense: (does it??)
> synchronous_commit = off, synchronous_replication = on

If the standby replies before writing the WAL, that strategy can improve
the performance with moderate reliability, and sounds sensible.
IIRC, MySQL Cluster might use that strategy.

> I was expecting you to have walreceiver and startup share an end of WAL
> address via shared memory, so that startup never tries to read past end.
> That way we would be able to begin reading a WAL file *before* it was
> filled. Waiting until a file fills means we still have to have
> archive_timeout set to ensure we switch regularly.

You mean that not pg_standby but startup process waits for the next
WAL available? If so, I agree with you in the future. That is, I just think
that this is next TODO because there are many problems which we
should resolve carefully to achieve it. But, if it's essential for 8.4, I will
tackle it. What is your opinion? I'd like to clear up the goal for 8.4.

Regards,

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

Hello,

On Fri, Dec 5, 2008 at 12:09 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> I was expecting you to have walreceiver and startup share an end of WAL
>> address via shared memory, so that startup never tries to read past end.
>> That way we would be able to begin reading a WAL file *before* it was
>> filled. Waiting until a file fills means we still have to have
>> archive_timeout set to ensure we switch regularly.
>
> You mean that not pg_standby but startup process waits for the next
> WAL available? If so, I agree with you in the future. That is, I just think
> that this is next TODO because there are many problems which we
> should resolve carefully to achieve it. But, if it's essential for 8.4, I will
> tackle it. What is your opinion? I'd like to clear up the goal for 8.4.

Umm.. on second thought, this feature (continuous recovery without
pg_standby) seems to be essential for 8.4. So, I will try it.

Development plan:
- Share the end of WAL address via shared memory <--- Done!
- Change ReadRecord() to wait for the next WAL *record* available.
- Change ReadRecord() to restore the WAL from archive by using
pg_standby before reaching the replication starting position, then
read the half-streaming WAL from pg_xlog.
- Add new trigger for promoting the standby to the primary. As the
trigger, when fast shudown (SIGINT) is requested during recovery,
the standby would recover the WAL up to end and become the
primary.

What system call does walreceiver have to call against the WAL
before startup process reads it? Probably we need to call write(2),
and don't need fsync(2) in Linux. How about other platform?

Regards,

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

Hi, sorry for my consecutive posting.

On Fri, Dec 5, 2008 at 4:00 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> Hello,
>
> On Fri, Dec 5, 2008 at 12:09 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>> I was expecting you to have walreceiver and startup share an end of WAL
>>> address via shared memory, so that startup never tries to read past end.
>>> That way we would be able to begin reading a WAL file *before* it was
>>> filled. Waiting until a file fills means we still have to have
>>> archive_timeout set to ensure we switch regularly.
>>
>> You mean that not pg_standby but startup process waits for the next
>> WAL available? If so, I agree with you in the future. That is, I just think
>> that this is next TODO because there are many problems which we
>> should resolve carefully to achieve it. But, if it's essential for 8.4, I will
>> tackle it. What is your opinion? I'd like to clear up the goal for 8.4.
>
> Umm.. on second thought, this feature (continuous recovery without
> pg_standby) seems to be essential for 8.4. So, I will try it.
>
> Development plan:
> - Share the end of WAL address via shared memory <--- Done!
> - Change ReadRecord() to wait for the next WAL *record* available.
> - Change ReadRecord() to restore the WAL from archive by using
> pg_standby before reaching the replication starting position, then
> read the half-streaming WAL from pg_xlog.
> - Add new trigger for promoting the standby to the primary. As the
> trigger, when fast shudown (SIGINT) is requested during recovery,
> the standby would recover the WAL up to end and become the
> primary.
>
> What system call does walreceiver have to call against the WAL
> before startup process reads it? Probably we need to call write(2),
> and don't need fsync(2) in Linux. How about other platform?

I added the figures about the latest architecture into PDF file.
Please check P6, 7. Is this architecture close to your imege?
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design

Regards,

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

On Fri, 2008-12-05 at 16:00 +0900, Fujii Masao wrote:

> On Fri, Dec 5, 2008 at 12:09 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> >> I was expecting you to have walreceiver and startup share an end of WAL
> >> address via shared memory, so that startup never tries to read past end.
> >> That way we would be able to begin reading a WAL file *before* it was
> >> filled. Waiting until a file fills means we still have to have
> >> archive_timeout set to ensure we switch regularly.
> >
> > You mean that not pg_standby but startup process waits for the next
> > WAL available? If so, I agree with you in the future. That is, I just think
> > that this is next TODO because there are many problems which we
> > should resolve carefully to achieve it. But, if it's essential for 8.4, I will
> > tackle it. What is your opinion? I'd like to clear up the goal for 8.4.
>
> Umm.. on second thought, this feature (continuous recovery without
> pg_standby) seems to be essential for 8.4. So, I will try it.

Sounds good. Perhaps you can share what changed your mind in those 4
hours...

Could we start with pictures and some descriptions first, so we know
we're on the right track? I foresee no coding issues.

My understanding is that we start with a normal log shipping
architecture, then we switch into continuous recovery mode. So we do use
pg_standby at beginning, but then it gets turned off.

Let's look at all of the corner cases also:
* standby keeps pace with primary (desired state)
* standby falls behind primary
* standby restarts to change shmmem settings
etc

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

On Fri, 2008-12-05 at 12:09 +0900, Fujii Masao wrote:

> On Thu, Dec 4, 2008 at 6:29 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > The only sensible settings are
> > synchronous_commit = on, synchronous_replication = on
> > synchronous_commit = on, synchronous_replication = off
> > synchronous_commit = off, synchronous_replication = off
> >
> > This doesn't make any sense: (does it??)
> > synchronous_commit = off, synchronous_replication = on
>
> If the standby replies before writing the WAL, that strategy can improve
> the performance with moderate reliability, and sounds sensible.

Do you think it likely that your replication time is consistently and
noticeably less than your time-to-disk? If not, you'll wait just as long
but be less robust. I guess its possible.

On a related thought: presumably we force a sync rep if forceSyncCommit
is set?

> IIRC, MySQL Cluster might use that strategy.

Not the most convincing argument I've heard.

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

Hi,

On Fri, Dec 5, 2008 at 7:09 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Fri, 2008-12-05 at 12:09 +0900, Fujii Masao wrote:
>
>> On Thu, Dec 4, 2008 at 6:29 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> > The only sensible settings are
>> > synchronous_commit = on, synchronous_replication = on
>> > synchronous_commit = on, synchronous_replication = off
>> > synchronous_commit = off, synchronous_replication = off
>> >
>> > This doesn't make any sense: (does it??)
>> > synchronous_commit = off, synchronous_replication = on
>>
>> If the standby replies before writing the WAL, that strategy can improve
>> the performance with moderate reliability, and sounds sensible.
>
> Do you think it likely that your replication time is consistently and
> noticeably less than your time-to-disk?

It depends on a system environment.
- How many miles two servers? same rack? separate continent?
- Does system have high-end storage? cheap one?
... etc

>
> On a related thought: presumably we force a sync rep if forceSyncCommit
> is set?

Yes!
Please see RecordTransactionCommit() in xact.c (in my patch).

Regards,

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

Greetings!

On Fri, Dec 5, 2008 at 6:59 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Fri, 2008-12-05 at 16:00 +0900, Fujii Masao wrote:
>
>> On Fri, Dec 5, 2008 at 12:09 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> >> I was expecting you to have walreceiver and startup share an end of WAL
>> >> address via shared memory, so that startup never tries to read past end.
>> >> That way we would be able to begin reading a WAL file *before* it was
>> >> filled. Waiting until a file fills means we still have to have
>> >> archive_timeout set to ensure we switch regularly.
>> >
>> > You mean that not pg_standby but startup process waits for the next
>> > WAL available? If so, I agree with you in the future. That is, I just think
>> > that this is next TODO because there are many problems which we
>> > should resolve carefully to achieve it. But, if it's essential for 8.4, I will
>> > tackle it. What is your opinion? I'd like to clear up the goal for 8.4.
>>
>> Umm.. on second thought, this feature (continuous recovery without
>> pg_standby) seems to be essential for 8.4. So, I will try it.
>
> Sounds good. Perhaps you can share what changed your mind in those 4
> hours...

Yeah, it's my imagination about the real situation after 8.4 release,
especially I considered about the future conjugal life of Synch Rep and
Hot Standby ;) Waiting to redo until the file fills might lead to marital
breakdown.

>
> Could we start with pictures and some descriptions first, so we know
> we're on the right track? I foresee no coding issues.
>
> My understanding is that we start with a normal log shipping
> architecture, then we switch into continuous recovery mode. So we do use
> pg_standby at beginning, but then it gets turned off.

Yes, I also understand so. Updated sequence pictures are on wiki
as per usual. Please see P3, 4.
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design

>
> Let's look at all of the corner cases also:
> * standby keeps pace with primary (desired state)
> * standby falls behind primary
> * standby restarts to change shmmem settings
> etc

Yes, I will examine such cases!

Regards,

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

On Sat, 2008-12-06 at 17:55 +0900, Fujii Masao wrote:

> Yeah, it's my imagination about the real situation after 8.4 release,
> especially I considered about the future conjugal life of Synch Rep and
> Hot Standby ;) Waiting to redo until the file fills might lead to marital
> breakdown.

You're obviously working with some comedians now. ;-)

> > Could we start with pictures and some descriptions first, so we know
> > we're on the right track? I foresee no coding issues.
> >
> > My understanding is that we start with a normal log shipping
> > architecture, then we switch into continuous recovery mode. So we do use
> > pg_standby at beginning, but then it gets turned off.
>
> Yes, I also understand so. Updated sequence pictures are on wiki
> as per usual. Please see P3, 4.
> http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design

p.6 looks good.

But what is p.7? It's even more complex than the original. Forgive me,
but I don't understand that. Can you explain?

What is the procedure if the standby shuts down, for example if we wish
to restart server to change a parameter? Or to reboot the system it is
on. Does the primary switch back to writing files to archive?

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

Hi, thanks for the comment!

On Mon, Dec 8, 2008 at 11:04 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> > Could we start with pictures and some descriptions first, so we know
>> > we're on the right track? I foresee no coding issues.
>> >
>> > My understanding is that we start with a normal log shipping
>> > architecture, then we switch into continuous recovery mode. So we do use
>> > pg_standby at beginning, but then it gets turned off.
>>
>> Yes, I also understand so. Updated sequence pictures are on wiki
>> as per usual. Please see P3, 4.
>> http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design
>
> p.6 looks good.
>
> But what is p.7? It's even more complex than the original. Forgive me,
> but I don't understand that. Can you explain?

p.7 shows one of the system configuration examples. Some people don't
want to share an archive between two servers would probably choose
this configuration, I think.

If archive is not shared, some WAL files before replication starts would not
be copied automatically from the primary to standby. So, we have to copy
them by hand or using clusterware ..etc. This is what p.7 shows. If archive
is shared, archiver on the primary would copy them automatically (p.6).

>
> What is the procedure if the standby shuts down, for example if we wish
> to restart server to change a parameter?

Stop postgres by using immediate shutdown, and start postgres from an
existing database cluster directory. When restarting postgres, if there are
one or more archives, we also need to copy the WAL files after stopping
replication before restarting replication.

> Or to reboot the system it is
> on. Does the primary switch back to writing files to archive?

I assume that the primary always writes files to archive, that is, basically
the primary doesn't switch to non-archiving mode. Of course, if archiving
is disabled on the primary in any reason when restarting standby, the
primary need to switch back.

Regards,

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

On Tue, 2008-12-09 at 17:15 +0900, Fujii Masao wrote:
> >
> > But what is p.7? It's even more complex than the original. Forgive me,
> > but I don't understand that. Can you explain?
>
> p.7 shows one of the system configuration examples. Some people don't
> want to share an archive between two servers would probably choose
> this configuration, I think.
>
> If archive is not shared, some WAL files before replication starts would not
> be copied automatically from the primary to standby. So, we have to copy
> them by hand or using clusterware ..etc. This is what p.7 shows. If archive
> is shared, archiver on the primary would copy them automatically (p.6).

I agree that is the way to do it *if* the archive is not shared. But why
would you want to *not* share the archive??

> > What is the procedure if the standby shuts down, for example if we wish
> > to restart server to change a parameter?
>
> Stop postgres by using immediate shutdown, and start postgres from an
> existing database cluster directory. When restarting postgres, if there are
> one or more archives, we also need to copy the WAL files after stopping
> replication before restarting replication.
>
> > Or to reboot the system it is
> > on. Does the primary switch back to writing files to archive?
>
> I assume that the primary always writes files to archive, that is, basically
> the primary doesn't switch to non-archiving mode.

OK, I think that clears up what I was seeing in the code. i.e. I didn't
understand the modes of operation.

I really like most of what you've done, though you must forgive me for
saying I still don't like this. I really am with you on how tiresome
that sounds.

For clarity: I don't think its acceptable to have the archiver send
files to the archive at the same time as we're streaming data. In normal
running we should not duplicate the data paths - its just too much data
volume and/or bandwidth.

The cleanest way I can see is to have two modes of operation:
* First mode is file-based log shipping (FLS) (i.e. "warm standby")
* Second mode is streaming log shipping (SLS) (wal sender to wal
receiver)

When we start we are in FLS mode, then we catch up to the cross-over
point and we switch to SLS mode. If streaming stops, we just switch back
to FLS mode. If they reconnect, we follow same procedure again. So the
two modes are compatible, but are never simultaneously active except for
a short period when we switch modes.

If SLS mode is active then the archiver doesn't send files. If FLS mode
is active, we send files. All of the places in code that currently are
not optimised when XLogArchivingActive() must remain unoptimised for
either FLS or SLS mode, so we need a new name for that.

This makes least number of changes to existing architecture. People
currently use FLS mode and understand it (!), they just add
understanding of SLS mode. It's also a very straightforward
architecture, which means fewer code paths and less weird bugs. (There's
been enough already, as you know).

So just for clarity, let me rephrase it:

We set up FLS mode as we do currently. Then we initiate SLS mode. At the
end of the next WAL file on primary we archive it, then turn off
archiving on primary. (So for up to one WAL file we operate two modes
together).

If SLS mode ends, we send next WAL file via archiver. Some part of that
file has already been streamed across, but that doesn't matter. (If SLS
mode ends because primary is down, we obviously do nothing. If we have a
split brain situation then we rely on clusterware to kill us (STONITH).

So AFAICS p.6 of the architecture is all we really need. Nice, simple.

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

Simon Riggs wrote:
> For clarity: I don't think its acceptable to have the archiver send
> files to the archive at the same time as we're streaming data. In normal
> running we should not duplicate the data paths - its just too much data
> volume and/or bandwidth.

What if you want to run archiving for backup purposes, and also have a
standby server?

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

On Tue, 2008-12-09 at 14:42 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > For clarity: I don't think its acceptable to have the archiver send
> > files to the archive at the same time as we're streaming data. In normal
> > running we should not duplicate the data paths - its just too much data
> > volume and/or bandwidth.
>
> What if you want to run archiving for backup purposes, and also have a
> standby server?

If we want to include that as an option, yes. If it is "always on" then
no, not everybody wants that.

The best way to implement that is to archive from the standby, not to
send the data twice. By definition the archive is more closely
associated with the standby node than the primary.

Maybe I misunderstood the diagrams? The additional flows to the archive
are actually all optional?

Anyway, I enclose a slightly simplified version of p.6 to allow us to
see the progression of file mode through to streaming mode. This is an
in-my-understanding version.

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

Hi,

Thanks for explaining the architecture in detail!

> If we want to include that as an option, yes. If it is "always on" then
> no, not everybody wants that.

Yes. I also think that archiving should be optional on each servers.

> The best way to implement that is to archive from the standby, not to
> send the data twice. By definition the archive is more closely
> associated with the standby node than the primary.
>
> Maybe I misunderstood the diagrams? The additional flows to the archive
> are actually all optional?
>
> Anyway, I enclose a slightly simplified version of p.6 to allow us to
> see the progression of file mode through to streaming mode. This is an
> in-my-understanding version.

Yes, I basically agree with you! The only difference between us is
whether the primary also has to switch two modes (FLS <-> SLS).
I think that the primary don't need to stop archiving forcibly when
replication starts, which should be optional for the user. The user
who doesn't want to archive can disable archiving by using existing
mechanism (change archive_command & pg_ctl reload). It's more
complicated to switch the modes on each servers.

For clarity: the user can choose the strategy of archiving from the
following.

1) each primary and standby archives
2) only primary archives
3) only standby archives
4) no server archives

The user who don't want to share an archive would choose 1).
The user who want to share an archive and cannot accept any
increase of bandwidth would choose 4). On the other hand,
the user who can accept it would choose 2) or 3). I prefer 2) to
3), for multiple standby in the future. And, if 3) is adopted,
I wonder if we can get a base backup. Can we get it from the
standby during recovery?

> I agree that is the way to do it *if* the archive is not shared. But why
> would you want to *not* share the archive??

First of all, I'd not like to buy a machine only for an archive other than
the primary and standby. Meanwhile, if an archive is located on either
the primary or standby (which should we locate it on?), post-failure
processing is complicated.

Regards,

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

On Wed, 2008-12-10 at 14:51 +0900, Fujii Masao wrote:

> Yes, I basically agree with you! The only difference between us is
> whether the primary also has to switch two modes (FLS <-> SLS).
> I think that the primary don't need to stop archiving forcibly when
> replication starts, which should be optional for the user. The user
> who doesn't want to archive can disable archiving by using existing
> mechanism (change archive_command & pg_ctl reload). It's more
> complicated to switch the modes on each servers.

Yes, I see that a manual change of parameter is possible. But it is
difficult to get the timing of the manual change correct and yet
important not to get that wrong. I don't want to spend the next year
answering questions on list about how that works and agreeing that it
isn't ideal.

We should have an optional mechanism that will turn archiving on the
primary off *automatically* when the mode changes. Maybe a third mode on
archive_mode to cater for this, but other ways possible also.

> For clarity: the user can choose the strategy of archiving from the
> following.
>
> 1) each primary and standby archives
> 2) only primary archives
> 3) only standby archives
> 4) no server archives

Those are all possible, but they aren't all equally usable as it stands.

In my experience most people do things very simply, so (4) is the common
use case. So it needs to Just Work.

We need to cater for a range of use cases, from simple implementations
through to complex multi-node cases. I don't think its right to assume
that everybody is implementing a complex use case and so we mostly cater
for that.

> The user who don't want to share an archive would choose 1).

If we include a feature you need to explain why its there. Asking the
question doesn't mean that I'm opposed, just that I'm checking why you
think its important to have that option.

So, why would you want to run with multiple archives?

> The user who want to share an archive and cannot accept any
> increase of bandwidth would choose 4). On the other hand,
> the user who can accept it would choose 2) or 3). I prefer 2) to
> 3), for multiple standby in the future. And, if 3) is adopted,

> I wonder if we can get a base backup. Can we get it from the
> standby during recovery?

That's an important feature, so we should make it "yes". (Can't
understand why you've built this with the archiver active on standby
node if this isn't possible).

People I talk to consider "low impact on primary" to be an important
aspect of this feature. Though if you forced me to prioritise I would
say making (4) automatic is more important than (3).

> > I agree that is the way to do it *if* the archive is not shared. But why
> > would you want to *not* share the archive??
>
> First of all, I'd not like to buy a machine only for an archive other than
> the primary and standby. Meanwhile, if an archive is located on either
> the primary or standby (which should we locate it on?), post-failure
> processing is complicated.

Are you saying that putting the archive on the primary is an option?

What is complicated about having the archive on the standby server?

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

Simon Riggs wrote:
> On Wed, 2008-12-10 at 14:51 +0900, Fujii Masao wrote:
>> For clarity: the user can choose the strategy of archiving from the
>> following.
>>
>> 1) each primary and standby archives
>> 2) only primary archives
>> 3) only standby archives
>> 4) no server archives
>
> Those are all possible, but they aren't all equally usable as it stands.
>
> In my experience most people do things very simply, so (4) is the common
> use case. So it needs to Just Work.

Agreed. All this talk about archiving and streaming working at the same
time is very confusing.

AFAICS, the patch as submitted doesn't work if archiving is disabled in
the primary. Which means that strategies (2) and (4) in your list are
not possible. The standby relies on the archiving and file-based log
shipping to work correctly. The streaming is just an extra thing,
shortcutting the normal file-based log shipping path to keep the latest
WAL segment up-to-date in the standby.

In the current form, is there any reason why walreceiver needs to be an
integrated server process? Couldn't it just be a stand-alone program
that connects to the primary and writes the received records to the
right WAL file? The only reason I can see is to reliably kill it when
the standby server is promoted to primary.

For a solution that doesn't depend on the file-based log shipping, I
think we'll need a way for the standby to request a certain starting
point for the streaming when it connects. When the standby starts, it
would first recover all the log segments it can obtain using
recovery_command, and then connect to the primary and request to start
streaming from where recovery_command stopped.

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

On Wed, 2008-12-10 at 14:39 +0200, Heikki Linnakangas wrote:

> For a solution that doesn't depend on the file-based log shipping, I
> think we'll need a way for the standby to request a certain starting
> point for the streaming when it connects. When the standby starts, it
> would first recover all the log segments it can obtain using
> recovery_command, and then connect to the primary and request to
> start
> streaming from where recovery_command stopped.

That was already suggested and rejected because it introduces a
potentially unacceptable delay in the start of synch replication - for
large databases this could be hours. (I should add it was suggested by
me and I now accept that it should be rejected.)

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

On Wed, 2008-12-10 at 14:39 +0200, Heikki Linnakangas wrote:

> In the current form, is there any reason why walreceiver needs to be
> an integrated server process? Couldn't it just be a stand-alone
> program that connects to the primary and writes the received records
> to the right WAL file? The only reason I can see is to reliably kill
> it when the standby server is promoted to primary.

Reasons:

* integration: we have one service we stop and start, not two. We want
one log, one set of commands, one set of parameters etc

* cooperation: if wal receiver is a server process we can reasonably
communicate the current WAL limit via shared memory. That gives us
smooth flow of WAL between receiver and replay (startup process) rather
than a burst of activity each time a file arrives. That helps smooth
performance and minimises failover time. Without this we would need to
retain the concept of archive_timeout on the primary even when
streaming, which is fairly strange.

* code management

Other than that there isn't that much in it...

We've all read the stuff about how other RDBMS come with integrated
replication. We *can* make this integrated, robust and very very easy to
use, yet with flexibility for a variety of purposes.

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

Simon Riggs wrote:
> On Wed, 2008-12-10 at 14:39 +0200, Heikki Linnakangas wrote:
>
>> For a solution that doesn't depend on the file-based log shipping, I
>> think we'll need a way for the standby to request a certain starting
>> point for the streaming when it connects. When the standby starts, it
>> would first recover all the log segments it can obtain using
>> recovery_command, and then connect to the primary and request to
>> start
>> streaming from where recovery_command stopped.
>
> That was already suggested and rejected because it introduces a
> potentially unacceptable delay in the start of synch replication - for
> large databases this could be hours. (I should add it was suggested by
> me and I now accept that it should be rejected.)

I don't understand that argument. If the standby is missing say 100 log
files, it's not up-to-date with the primary until it has somehow
obtained and replayed all those log file. It doesn't make any difference
whether it obtains them over the wire via walreceiver, or via an
archive. Until it has obtained and replayed all those files, it's not
up-to-date, and a failover would lead to data loss.

Or did I misunderstand what "start of synch replication" means? Got a
pointer to the previous discussion?

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

Simon Riggs wrote:
> * cooperation: if wal receiver is a server process we can reasonably
> communicate the current WAL limit via shared memory. That gives us
> smooth flow of WAL between receiver and replay (startup process) rather
> than a burst of activity each time a file arrives. That helps smooth
> performance and minimises failover time. Without this we would need to
> retain the concept of archive_timeout on the primary even when
> streaming, which is fairly strange.

Does it actually do that? I can see comments suggesting that in
walreceiver, but I can't find the place in xlog.c where the startup
process does the waiting.

> * code management
>
> Other than that there isn't that much in it...

Ok, just making sure I wasn't missing something crucial. I agree it
should be integrated. What I'm actually worried about is that this
system isn't integrated enough, and having to set up the archiving,
pg_standby, and the synchronous repliation itself, correctly, makes it
too complex to be practical.

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

On Wed, 2008-12-10 at 09:48 +0000, Simon Riggs wrote:
> What is complicated about having the archive on the standby server?
>

If the storage on the standby fails, you would lose the archive, right?

I think there's a use case for having two identical servers, and just
setting them up to replicate synchronously. Many of these use-cases
might not even care much about write performance or the duplicity of
maintaining two copies of the archive. They might care a lot about PITR
though, and that would be impossible if you lose the archive.

Do you see a cost to allowing all of the options listed by Fujii Masao?

Regards,
Jeff Davis

On Wed, 2008-12-10 at 20:52 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Wed, 2008-12-10 at 14:39 +0200, Heikki Linnakangas wrote:
> >
> >> For a solution that doesn't depend on the file-based log shipping, I
> >> think we'll need a way for the standby to request a certain starting
> >> point for the streaming when it connects. When the standby starts, it
> >> would first recover all the log segments it can obtain using
> >> recovery_command, and then connect to the primary and request to
> >> start
> >> streaming from where recovery_command stopped.
> >
> > That was already suggested and rejected because it introduces a
> > potentially unacceptable delay in the start of synch replication - for
> > large databases this could be hours. (I should add it was suggested by
> > me and I now accept that it should be rejected.)
>
> I don't understand that argument. If the standby is missing say 100 log
> files, it's not up-to-date with the primary until it has somehow
> obtained and replayed all those log file. It doesn't make any difference
> whether it obtains them over the wire via walreceiver, or via an
> archive. Until it has obtained and replayed all those files, it's not
> up-to-date, and a failover would lead to data loss.
>
> Or did I misunderstand what "start of synch replication" means? Got a
> pointer to the previous discussion?

I think you just went down the same path I did before. (That's a good
sign).

When the WAL starts streaming the *primary* can immediately perform
synchronous replication, i.e. commit waits for transfer. The *standby*
has an initial lag before it catches up, whatever we do (as you say).

I suggested that way initially because it simplifies the mode change.
The mode change isn't really that complex, so I agreed we should change
it.

The two ways of doing this are/were:

1. (Initial suggestion)
* allow standby to catchup
* then connect and allow sync rep

2. Preferred Choice
* connect to primary and allow sync rep
* catch up

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

On Wed, 2008-12-10 at 11:52 -0800, Jeff Davis wrote:
> On Wed, 2008-12-10 at 09:48 +0000, Simon Riggs wrote:
> > What is complicated about having the archive on the standby server?
> >
>
> If the storage on the standby fails, you would lose the archive, right?

As well as the standby itself presumably. Either way you need to restart
from a base backup.

> I think there's a use case for having two identical servers, and just
> setting them up to replicate synchronously. Many of these use-cases
> might not even care much about write performance or the duplicity of
> maintaining two copies of the archive.

Yes, that's what I've said also.

> They might care a lot about PITR
> though, and that would be impossible if you lose the archive.

Agreed, yes we need it as an option.

> Do you see a cost to allowing all of the options listed by Fujii Masao?

I haven't argued in favour of removing any options, so not sure what you
mean. I have asked for an explanation of why certain features are needed
so we can judge whether there is a simpler way of providing everything
required. It may not exist.

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

* Simon Riggs <simon(at)2ndQuadrant(dot)com> [081210 14:58]:

> I think you just went down the same path I did before. (That's a good
> sign).
>
> When the WAL starts streaming the *primary* can immediately perform
> synchronous replication, i.e. commit waits for transfer. The *standby*
> has an initial lag before it catches up, whatever we do (as you say).
>
> I suggested that way initially because it simplifies the mode change.
> The mode change isn't really that complex, so I agreed we should change
> it.
>
> The two ways of doing this are/were:
>
> 1. (Initial suggestion)
> * allow standby to catchup
> * then connect and allow sync rep
>
> 2. Preferred Choice
> * connect to primary and allow sync rep
> * catch up

Call me think, but I'm confused... In sync rep, there *can't be* any
catchign up do do... i.e. if the "slave" isn't accepting the WAL the
master "stops" doing *anything*...

--
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 Wed, 2008-12-10 at 21:02 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > * cooperation: if wal receiver is a server process we can reasonably
> > communicate the current WAL limit via shared memory. That gives us
> > smooth flow of WAL between receiver and replay (startup process) rather
> > than a burst of activity each time a file arrives. That helps smooth
> > performance and minimises failover time. Without this we would need to
> > retain the concept of archive_timeout on the primary even when
> > streaming, which is fairly strange.
>
> Does it actually do that? I can see comments suggesting that in
> walreceiver, but I can't find the place in xlog.c where the startup
> process does the waiting.

Not yet... we agreed it would do that a few days ago. This thread, Fri 5
Dec.

> > * code management
> >
> > Other than that there isn't that much in it...
>
> Ok, just making sure I wasn't missing something crucial. I agree it
> should be integrated. What I'm actually worried about is that this
> system isn't integrated enough, and having to set up the archiving,
> pg_standby, and the synchronous replication itself, correctly, makes it
> too complex to be practical.

I'm worried about the complexity also. If we didn't use the existing
archiving mechanism we'd need to invent something that looks just like
it.

If I could get rid of pg_standby as well, I would. I've got no qualms
about chopping stuff I wrote, as long as we do it for a good reason.
Keeping the parts of the old model that make sense means less code and
less process change for existing users.

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

On Wed, 2008-12-10 at 20:04 +0000, Simon Riggs wrote:
> > They might care a lot about PITR
> > though, and that would be impossible if you lose the archive.
>
> Agreed, yes we need it as an option.
>
> > Do you see a cost to allowing all of the options listed by Fujii Masao?
>
> I haven't argued in favour of removing any options, so not sure what you
> mean. I have asked for an explanation of why certain features are needed
> so we can judge whether there is a simpler way of providing everything
> required. It may not exist.

I was trying to provide a use-case for maintaining the archive on both
primary and standby, i.e. option (1). My understanding was that you were
asking for such a use case with this question:

"So, why would you want to run with multiple archives?"

Regards,
Jeff Davis

Simon Riggs wrote:
> When the WAL starts streaming the *primary* can immediately perform
> synchronous replication, i.e. commit waits for transfer.

Until the standby has obtained all the missing log files, it's not
up-to-date, and there's no guarantee that it can finish the replay. For
example, imagine that your archive_command is an scp from the primary to
the standby. If a lightning strikes the primary before some WAL file has
been copied over to the archive directory in the standby, the standby
can't catch up. In the primary then, what's the point for a commit to
wait for transfer, if the reply from the standby doesn't guarantee that
the transaction is safe in the standby?

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

On Thu, 2008-12-11 at 09:44 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > When the WAL starts streaming the *primary* can immediately perform
> > synchronous replication, i.e. commit waits for transfer.
>
> Until the standby has obtained all the missing log files, it's not
> up-to-date, and there's no guarantee that it can finish the replay. For
> example, imagine that your archive_command is an scp from the primary to
> the standby. If a lightning strikes the primary before some WAL file has
> been copied over to the archive directory in the standby, the standby
> can't catch up. In the primary then, what's the point for a commit to
> wait for transfer, if the reply from the standby doesn't guarantee that
> the transaction is safe in the standby?

The WAL files will have already left the primary.

Timeline is this in my understanding
1 [Primary] Set up continuous archiving
2 [Primary] Take base backup
3 [Standby] Connect to primary to initiate streaming
4 [Primary] Log switch and, optionally, turn off archiving
5 [Standby] Begin replaying files, initially from archive
6 [Standby] Switch to replaying WAL records immediately after streaming

So sync rep would turn on after step 4, so that all intermediate WAL
files have been sent to the archive. If we lose the Primary after this
point then all transactions are accessible to standby. If we lose the
Standby or Archive, then we need to replace them and re-run the above.

The above was outlined on thread "Synchronous Log Shipping Replication"
and pretty much all agreed on 18 Sep.

Recent changes I have requested in the architecture are:
* making archiving optional on primary, so we don't need to send WAL
data *twice*.
* allowing streaming/startup process to work together via shared memory,
to reduce average replication delay and improve performance
* skip archiving/de-archiving step on standby because it's superfluous
(all on this thread)

All of those are fairly minor code changes, but reduce complexity of
solution and significantly reduce the amount of copying of WAL files (3
copy actions to/from archive removed without loss of robustness). I
would have made the suggestions earlier but it wasn't until I saw the
architecture diagrams that I understood the intention of the code.

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

Simon Riggs wrote:
> On Thu, 2008-12-11 at 09:44 +0200, Heikki Linnakangas wrote:
>> Simon Riggs wrote:
>>> When the WAL starts streaming the *primary* can immediately perform
>>> synchronous replication, i.e. commit waits for transfer.
>> Until the standby has obtained all the missing log files, it's not
>> up-to-date, and there's no guarantee that it can finish the replay. For
>> example, imagine that your archive_command is an scp from the primary to
>> the standby. If a lightning strikes the primary before some WAL file has
>> been copied over to the archive directory in the standby, the standby
>> can't catch up. In the primary then, what's the point for a commit to
>> wait for transfer, if the reply from the standby doesn't guarantee that
>> the transaction is safe in the standby?
>
> The WAL files will have already left the primary.
>
> Timeline is this in my understanding
> 1 [Primary] Set up continuous archiving
> 2 [Primary] Take base backup
> 3 [Standby] Connect to primary to initiate streaming
> 4 [Primary] Log switch and, optionally, turn off archiving
> 5 [Standby] Begin replaying files, initially from archive
> 6 [Standby] Switch to replaying WAL records immediately after streaming
>
> So sync rep would turn on after step 4, so that all intermediate WAL
> files have been sent to the archive. If we lose the Primary after this
> point then all transactions are accessible to standby. If we lose the
> Standby or Archive, then we need to replace them and re-run the above.

Between steps 4 and 5, there's no guarantee that all WAL files generated
after step 3 and the start of streaming have already been archived.
There's a delay between writing a WAL file and when the file has been
safely archived. If you lose the primary during that window, the standby
will have old WAL files in the archive, the most recent ones in received
by walreceiver, but it's missing the WAL files generated just before the
switch to streaming mode.

> Recent changes I have requested in the architecture are:
> * making archiving optional on primary, so we don't need to send WAL
> data *twice*.

Agreed. I'm not so much worried about the bandwidth, but it's a lot of
extra work from administration point of view. It's very hard to get it
right, so that you eliminate windows like the above.

As the patch stands, if you turn off archiving in the primary, and the
standby ever disconnects, even for only a few seconds, the standby will
miss any WAL generated until it reconnects, and without archiving
there's no way for the standby to get hold of the missed WAL.

> * allowing streaming/startup process to work together via shared memory,
> to reduce average replication delay and improve performance
> * skip archiving/de-archiving step on standby because it's superfluous
> (all on this thread)
>
> All of those are fairly minor code changes, but reduce complexity of
> solution and significantly reduce the amount of copying of WAL files (3
> copy actions to/from archive removed without loss of robustness). I
> would have made the suggestions earlier but it wasn't until I saw the
> architecture diagrams that I understood the intention of the code.

To make archiving optional in the primary, I don't see any other choice
than adding the capability for the standby to request arbitrary WAL
files from the primary, over the wire. That seems like a pretty
significant change to walsender: it needs to be able to read WAL not
only from wal_buffers, but from files. That would be a good idea for
performance reasons, too: currently if there's a network glitch and the
primary doesn't get acknowledgements from the standby for a short while,
XLogInserts in the primary will block waiting for the standby after
wal_buffers fills up. That's not a big deal for synchronous replication,
but in asynchronous mode you don't want network glitches like that to
stall the primary.

And of course it means changes in the startup code as well. And we'll
need bookkeeping in the primary of what WAL the standby has already
received, so that it doesn't recycle the WAL segments until they've been
sent to the standby. Or alternatively, the primary needs to be able to
retrieve segments from the archive, but then we're dependent on
archiving again.

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

On Thu, 2008-12-11 at 11:29 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Thu, 2008-12-11 at 09:44 +0200, Heikki Linnakangas wrote:
> >> Simon Riggs wrote:
> >>> When the WAL starts streaming the *primary* can immediately perform
> >>> synchronous replication, i.e. commit waits for transfer.
> >> Until the standby has obtained all the missing log files, it's not
> >> up-to-date, and there's no guarantee that it can finish the replay. For
> >> example, imagine that your archive_command is an scp from the primary to
> >> the standby. If a lightning strikes the primary before some WAL file has
> >> been copied over to the archive directory in the standby, the standby
> >> can't catch up. In the primary then, what's the point for a commit to
> >> wait for transfer, if the reply from the standby doesn't guarantee that
> >> the transaction is safe in the standby?
> >
> > The WAL files will have already left the primary.
> >
> > Timeline is this in my understanding
> > 1 [Primary] Set up continuous archiving
> > 2 [Primary] Take base backup
> > 3 [Standby] Connect to primary to initiate streaming
> > 4 [Primary] Log switch and, optionally, turn off archiving
> > 5 [Standby] Begin replaying files, initially from archive
> > 6 [Standby] Switch to replaying WAL records immediately after streaming
> >
> > So sync rep would turn on after step 4, so that all intermediate WAL
> > files have been sent to the archive. If we lose the Primary after this
> > point then all transactions are accessible to standby. If we lose the
> > Standby or Archive, then we need to replace them and re-run the above.
>
> Between steps 4 and 5, there's no guarantee that all WAL files generated
> after step 3 and the start of streaming have already been archived.
> There's a delay between writing a WAL file and when the file has been
> safely archived. If you lose the primary during that window, the standby
> will have old WAL files in the archive, the most recent ones in received
> by walreceiver, but it's missing the WAL files generated just before the
> switch to streaming mode.

I was presuming that the synchronisation was clear, but I'm sorry it
wasn't. Sync rep would begin only *after* the last WAL file was
archived.

> > Recent changes I have requested in the architecture are:
> > * making archiving optional on primary, so we don't need to send WAL
> > data *twice*.
>
> Agreed. I'm not so much worried about the bandwidth, but it's a lot of
> extra work from administration point of view. It's very hard to get it
> right, so that you eliminate windows like the above.
>
> As the patch stands, if you turn off archiving in the primary, and the
> standby ever disconnects, even for only a few seconds, the standby will
> miss any WAL generated until it reconnects, and without archiving
> there's no way for the standby to get hold of the missed WAL.

I described earlier that archiving would turn back on again if the
replication ever failed (with correct synchronisation).

All I've asked for is the ability to turn on and turn back on archiving,
yes, with synchronisation so its safe.

Personally, I think people will laugh if we tell them we decided to ship
all the data twice and couldn't see another way. That's the kind of
thing people give presentations at PGcon about...

> > * allowing streaming/startup process to work together via shared memory,
> > to reduce average replication delay and improve performance
> > * skip archiving/de-archiving step on standby because it's superfluous
> > (all on this thread)
> >
> > All of those are fairly minor code changes, but reduce complexity of
> > solution and significantly reduce the amount of copying of WAL files (3
> > copy actions to/from archive removed without loss of robustness). I
> > would have made the suggestions earlier but it wasn't until I saw the
> > architecture diagrams that I understood the intention of the code.
>
> To make archiving optional in the primary, I don't see any other choice
> than adding the capability for the standby to request arbitrary WAL
> files from the primary, over the wire.

I don't think that's the only or even a desirable way. We cannot allow a
build up of WAL files to occur on the primary.

Making archiving optional isn't the big deal you're saying it is.

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

Hi,

On Thu, Dec 11, 2008 at 7:09 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> > Recent changes I have requested in the architecture are:
>> > * making archiving optional on primary, so we don't need to send WAL
>> > data *twice*.
>>
>> Agreed. I'm not so much worried about the bandwidth, but it's a lot of
>> extra work from administration point of view. It's very hard to get it
>> right, so that you eliminate windows like the above.
>>
>> As the patch stands, if you turn off archiving in the primary, and the
>> standby ever disconnects, even for only a few seconds, the standby will
>> miss any WAL generated until it reconnects, and without archiving
>> there's no way for the standby to get hold of the missed WAL.
>
> I described earlier that archiving would turn back on again if the
> replication ever failed (with correct synchronisation).
>
> All I've asked for is the ability to turn on and turn back on archiving,
> yes, with synchronisation so its safe.
>
> Personally, I think people will laugh if we tell them we decided to ship
> all the data twice and couldn't see another way. That's the kind of
> thing people give presentations at PGcon about...

OK, I will add such archiving feature. My new design of archiving is as follows.

Primary
----------
I extend archive_mode as follows and make the user be able to choose the
archiving strategy on the primary.

- always
The primary always archives the WAL. This is compatible with current (<=8.3)
archive_mode = on.

- none
The primary always doesn't archive the WAL. This is compatible with current
archive_mode = off.

- standalone
The primary doesn't archive the WAL only during replication. If replication is
not in progress, the primary archives the WAL. That is, the primary switches
the modes whenever replication starts / ends.

[FLS->SLS]
When replication starts, the primary disable archiving *after* the switched
WAL file is archived. WAL streaming doesn't need to wait for disablement
of archiving, so the processing on the primary isn't blocked by starting of
replication. But, both WAL streaming and archiving would be in progress
for a while (until the switched WAL file is archived) after
replication starts.

[SLS->FLS]
When replication starts, the primary restarts archiving immediately. This
also doesn't block the processing on the primary. But, this might cause
loss of some files from an archive if archiving is slow on the standby.
The primary should look for the last archived file (by the standby) from
an archive and restart archiving from the subsequent file? Of course,
the primary cannot archive it if it's already removed on the primary.

Standby
-----------
I would add new option for achiving during recovery into recovery.conf
(recovery_archive_mode). Though this option is similar to archive_mode,
merging them would confuse the user more, I think. Or, I should merge?
And, do you want to configure the archive command only for recovery?
If so, I would add new option to specify the archive command during
recovery (recovery_archive_command).

Regards,

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

Hi,

On Thu, Dec 11, 2008 at 7:09 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Thu, 2008-12-11 at 11:29 +0200, Heikki Linnakangas wrote:
>> Simon Riggs wrote:
>> > On Thu, 2008-12-11 at 09:44 +0200, Heikki Linnakangas wrote:
>> >> Simon Riggs wrote:
>> >>> When the WAL starts streaming the *primary* can immediately perform
>> >>> synchronous replication, i.e. commit waits for transfer.
>> >> Until the standby has obtained all the missing log files, it's not
>> >> up-to-date, and there's no guarantee that it can finish the replay. For
>> >> example, imagine that your archive_command is an scp from the primary to
>> >> the standby. If a lightning strikes the primary before some WAL file has
>> >> been copied over to the archive directory in the standby, the standby
>> >> can't catch up. In the primary then, what's the point for a commit to
>> >> wait for transfer, if the reply from the standby doesn't guarantee that
>> >> the transaction is safe in the standby?
>> >
>> > The WAL files will have already left the primary.
>> >
>> > Timeline is this in my understanding
>> > 1 [Primary] Set up continuous archiving
>> > 2 [Primary] Take base backup
>> > 3 [Standby] Connect to primary to initiate streaming
>> > 4 [Primary] Log switch and, optionally, turn off archiving
>> > 5 [Standby] Begin replaying files, initially from archive
>> > 6 [Standby] Switch to replaying WAL records immediately after streaming
>> >
>> > So sync rep would turn on after step 4, so that all intermediate WAL
>> > files have been sent to the archive. If we lose the Primary after this
>> > point then all transactions are accessible to standby. If we lose the
>> > Standby or Archive, then we need to replace them and re-run the above.
>>
>> Between steps 4 and 5, there's no guarantee that all WAL files generated
>> after step 3 and the start of streaming have already been archived.
>> There's a delay between writing a WAL file and when the file has been
>> safely archived. If you lose the primary during that window, the standby
>> will have old WAL files in the archive, the most recent ones in received
>> by walreceiver, but it's missing the WAL files generated just before the
>> switch to streaming mode.

Yes, since such standby is unsafe, the user must not promote it to the primary.
Then, the user has to stop the standby (don't complete recovery), restart the
primary and restart the standby.

>
> I was presuming that the synchronisation was clear, but I'm sorry it
> wasn't. Sync rep would begin only *after* the last WAL file was
> archived.

Agreed. In order for the user to confirm whether replication began or
not, we might need to log the name of the switched WAL file.

Regards,

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

On Wed, 2008-12-10 at 15:06 -0500, Aidan Van Dyk wrote:

> Call me think, but I'm confused... In sync rep, there *can't be* any
> catchign up do do... i.e. if the "slave" isn't accepting the WAL the
> master "stops" doing *anything*...

In normal/steady state, yes, you are correct. But there is more...

The simplest way to configure standby would be to freeze the primary
while we setup the standby and then go straight into normal/steady
state. That could mean hours of downtime for large databases, which is
unacceptable in a feature aimed at increasing availability. So we need
to allow the primary to continue working while the standby is setup.
That then creates a log gap between the LSN of the primary and the LSN
of the standby, which must be resolved.

So the catchup occurs during the transient initial phase when standby is
catching up with primary before they continue together in normal/steady
state.

Most of the architectural discussion over last few months has been about
the need for the initial state and how to handle it. Most of the code
complexity also.

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

On Thu, 2008-12-11 at 19:19 +0900, Fujii Masao wrote:
> >
> > All I've asked for is the ability to turn on and turn back on archiving,
> > yes, with synchronisation so its safe.
> >(snip)

> OK, I will add such archiving feature. My new design of archiving is as follows.
>
> Primary
> ----------
> I extend archive_mode as follows and make the user be able to choose the
> archiving strategy on the primary.
>
> - always
> The primary always archives the WAL. This is compatible with current (<=8.3)
> archive_mode = on.
>
> - none
> The primary always doesn't archive the WAL. This is compatible with current
> archive_mode = off.
>
> - standalone
> The primary doesn't archive the WAL only during replication. If replication is
> not in progress, the primary archives the WAL. That is, the primary switches
> the modes whenever replication starts / ends.
>
> [FLS->SLS]
> When replication starts, the primary disable archiving *after* the switched
> WAL file is archived. WAL streaming doesn't need to wait for disablement
> of archiving, so the processing on the primary isn't blocked by starting of
> replication. But, both WAL streaming and archiving would be in progress
> for a while (until the switched WAL file is archived) after
> replication starts.

I'm OK with that, but that is slightly different from what Heikki had
said in relation to the point at which sync rep begins on primary, so he
may have a different view.

synchronous_replication means "if we a standby server has connected to
us we will wait for all WAL associated with a transaction to be
transferred prior to commit". So there is never a 100% guarantee that
the transaction is safe, just an "if possible, 100%".

So this implements the equivalent of DRBD Protocol A and B. Do we have
an option to allow the WALreceiver to fsync the WAL file after a commit
is received, which would make it equivalent to Protocol C? If we don't,
I'm OK with that since it reduces performance so much it isn't a
practical option in many cases.
http://www.drbd.org/users-guide/s-replication-protocols.html

> [SLS->FLS]
> When replication starts, the primary restarts archiving immediately. This
> also doesn't block the processing on the primary. But, this might cause
> loss of some files from an archive if archiving is slow on the standby.
> The primary should look for the last archived file (by the standby) from
> an archive and restart archiving from the subsequent file? Of course,
> the primary cannot archive it if it's already removed on the primary.

Standby will always have kept enough files to allow it to restart from
the last restartpoint, so a gap in the file sequence is unlikely. As
long as we archive the WAL file that contains the last LSN we
transferred before streaming failed. That conceivably might mean we need
to write a .ready message after a WAL file filled, which might mean we
have problems if the replication timeout is longer than the checkpoint
timeout, but that seems an unlikely configuration. And if anybody has a
problem with that we just recommend they use the "always" mode.

> Standby
> -----------
> I would add new option for achiving during recovery into recovery.conf
> (recovery_archive_mode). Though this option is similar to archive_mode,
> merging them would confuse the user more, I think. Or, I should merge?
> And, do you want to configure the archive command only for recovery?
> If so, I would add new option to specify the archive command during
> recovery (recovery_archive_command).

I think if you really want two archives or archiving during recovery
then this is desirable to avoid confusion.

Explaining all this in the docs will be fun. :-)

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

* Simon Riggs <simon(at)2ndQuadrant(dot)com> [081211 05:45]:
>
> On Wed, 2008-12-10 at 15:06 -0500, Aidan Van Dyk wrote:
>
> > Call me think, but I'm confused... In sync rep, there *can't be* any
> > catchign up do do... i.e. if the "slave" isn't accepting the WAL the
> > master "stops" doing *anything*...
>
> In normal/steady state, yes, you are correct. But there is more...
>
> The simplest way to configure standby would be to freeze the primary
> while we setup the standby and then go straight into normal/steady
> state. That could mean hours of downtime for large databases, which is
> unacceptable in a feature aimed at increasing availability. So we need
> to allow the primary to continue working while the standby is setup.
> That then creates a log gap between the LSN of the primary and the LSN
> of the standby, which must be resolved.
>
> So the catchup occurs during the transient initial phase when standby is
> catching up with primary before they continue together in normal/steady
> state.

But "catchup" *has* to be *done* before PostgreSQL can enter "sync rep".

So, if I start PostgreSQL in sync rep mode, without any capable clients
to rep with.... But I'ld rather be buggered there then find out tonight
at 3am that it was in sync rep mode but wasn't really doing sync rep,
becus I'ld messed up something somewhere (firewall, config, password,
anything) and ther ewas not "caught up" client at the time, and I've
just lost a days' worth of my $$$$$ transactions...

> Most of the architectural discussion over last few months has been about
> the need for the initial state and how to handle it. Most of the code
> complexity also.

Well, for me, I'm quite happy with a "restart/stop&start" being a
necessary "downtime" to move to synchronous replication. This way, I
could see a "setup" routing that looks like:
1) Current "production" DB does normal backups/PITR/WAL archiving
2) I setup new "slave", which involves
- restore from backup + wal recover (pg_standby type)
- Could take days+++
- Oh well....
3) Stop production
4) so, now slave is caught up...
5) Start "production" now in sync rep mode as master
6) start slave in sync-rep mode as slave...

So downtime would be limited to the time from the old postmaster
shutdown to the time the slave has replayed the last WAL and connected
to the restarted postmaster as a sync rep slave...

Or am I way too naive to think that a small downtime to "switch" from
non-sync-rep to sync-rep is acceptable...

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.

* Fujii Masao <masao(dot)fujii(at)gmail(dot)com> [081211 05:25]:

> - standalone
> The primary doesn't archive the WAL only during replication. If replication is
> not in progress, the primary archives the WAL. That is, the primary switches
> the modes whenever replication starts / ends.

That scares the hebegebies out of me... I'm doing sync-rep because I
*really* *want* *my* *data* .... *always* ...

I want sync-rep because I'm going to get even *stonger* guarentees on my
data (and, if hot-standby works out, load balancing too, but thats not
*my* primary desire for sync-rep)...

But I'm sure as hell *not* going to throw all my eggs into that slave's
basket and do away with my WAL archive... Would anyone actually use
that "standby" mode, and if not, why compilcate the code for it?

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 Thu, 2008-12-11 at 09:27 -0500, Aidan Van Dyk wrote:

> But "catchup" *has* to be *done* before PostgreSQL can enter "sync rep".

Not true. Please reread the thread where Heikki questions that and I
reply. This was Fujii-san's idea, which I now agree with.

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

On Thu, 2008-12-11 at 09:37 -0500, Aidan Van Dyk wrote:
> * Fujii Masao <masao(dot)fujii(at)gmail(dot)com> [081211 05:25]:
>
> > - standalone
> > The primary doesn't archive the WAL only during replication. If
> replication is
> > not in progress, the primary archives the WAL. That is, the
> primary switches
> > the modes whenever replication starts / ends.

> But I'm sure as hell *not* going to throw all my eggs into that
> slave's
> basket and do away with my WAL archive... Would anyone actually use
> that "standby" mode, and if not, why compilcate the code for it?

Sending data twice is not a requirement I ever heard expressed, nor has
the lack of ability to send it twice been voiced as a criticism for any
form of replication I'm familiar with. Ask the DRBD guys if sending data
twice is necessary or required to make replication work.

If multiple people think its a good idea then I respect your choice of
option.

But I also think that many or perhaps most people will choose not to
send data twice and I respect that choice of option also.

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

Simon Riggs wrote:
> On Thu, 2008-12-11 at 09:27 -0500, Aidan Van Dyk wrote:
>
>> But "catchup" *has* to be *done* before PostgreSQL can enter "sync rep".
>
> Not true. Please reread the thread where Heikki questions that and I
> reply. This was Fujii-san's idea, which I now agree with.

I think the confusion here is about what exactly "sync rep" means in
this situation. It's true that you can start streaming the WAL before
the standby has fully caught up. But from the client's point of view,
there's not much point in streaming the log *synchronously* and making
the client to wait for the acknowledment from the standby, if the
acknowledgment from the standby that WAL has be streamed up to point X,
doesn't actually guarantee that the slave can recover all the way to
that point.

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

* Simon Riggs <simon(at)2ndQuadrant(dot)com> [081211 10:03]:

> Sending data twice is not a requirement I ever heard expressed, nor has
> the lack of ability to send it twice been voiced as a criticism for any
> form of replication I'm familiar with. Ask the DRBD guys if sending data
> twice is necessary or required to make replication work.
>
> If multiple people think its a good idea then I respect your choice of
> option.
>
> But I also think that many or perhaps most people will choose not to
> send data twice and I respect that choice of option also.

Well, PostgreSQL has WAL, so we've already accepted the notion of "send
data twice" being useful sometimes...

But I would note that the "archive" and "streaming" are both sending the
data *different* places... or at least, in my case would be...

And, also, I know WAL archiving isn't necessary for replication to work.
but it's necessary for me to sleep comfortably at night ;-)

I'm just suprised that people are willing to throw away their
backup/PITR archiving once they have a singl "live slave" up.

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.

* Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> [081211 10:09]:
> Simon Riggs wrote:
>> On Thu, 2008-12-11 at 09:27 -0500, Aidan Van Dyk wrote:
>>
>>> But "catchup" *has* to be *done* before PostgreSQL can enter "sync rep".
>>
>> Not true. Please reread the thread where Heikki questions that and I
>> reply. This was Fujii-san's idea, which I now agree with.
>
> I think the confusion here is about what exactly "sync rep" means in
> this situation. It's true that you can start streaming the WAL before
> the standby has fully caught up. But from the client's point of view,
> there's not much point in streaming the log *synchronously* and making
> the client to wait for the acknowledment from the standby, if the
> acknowledgment from the standby that WAL has be streamed up to point X,
> doesn't actually guarantee that the slave can recover all the way to
> that point.

Quite possibly a terminology problem.. I my case I said "sync rep"
meaning the mode such that the transaction doesn't commit successfully
for my PG client until the xlog record has been "streamed" to the
client... and I understand that at his presentation at PGcon, Fujii-san
there could be possible variants on when the "streamed" is considered
done based on network, slave ram, disk, application, etc.

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 Thu, 2008-12-11 at 17:07 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Thu, 2008-12-11 at 09:27 -0500, Aidan Van Dyk wrote:
> >
> >> But "catchup" *has* to be *done* before PostgreSQL can enter "sync rep".
> >
> > Not true. Please reread the thread where Heikki questions that and I
> > reply. This was Fujii-san's idea, which I now agree with.
>
> I think the confusion here is about what exactly "sync rep" means in
> this situation. It's true that you can start streaming the WAL before
> the standby has fully caught up.

Yep.

> But from the client's point of view,
> there's not much point in streaming the log *synchronously* and making
> the client to wait for the acknowledment from the standby, if the
> acknowledgment from the standby that WAL has be streamed up to point X,
> doesn't actually guarantee that the slave can recover all the way to
> that point.

I disagree. This morning I showed it was possible, given the
synchronisation I outlined.

There is a slight relaxation of that in the current proposal, so you
need to take that up if you see any problem there.

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

On Thu, 2008-12-11 at 19:19 +0900, Fujii Masao wrote:

> My new design of archiving is as follows.

So far I haven't asked about running multiple standby servers and don't
recall having seen this mentioned anywhere. Forgive me if this was
already mentioned.

The idea is that we would be able to have multiple standby servers
connecting to one primary, yes? It would be useful to have sync
replication work that it must get an acknowledgement from at least one
standby before it continues.

Or do you think we would stream to just one standby, then use the
archiver (primary or standby) to keep sending files to allow multiple
additional standby nodes?

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

Hi,

On Fri, Dec 12, 2008 at 12:15 AM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
> * Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> [081211 10:09]:
>> Simon Riggs wrote:
>>> On Thu, 2008-12-11 at 09:27 -0500, Aidan Van Dyk wrote:
>>>
>>>> But "catchup" *has* to be *done* before PostgreSQL can enter "sync rep".
>>>
>>> Not true. Please reread the thread where Heikki questions that and I
>>> reply. This was Fujii-san's idea, which I now agree with.
>>
>> I think the confusion here is about what exactly "sync rep" means in
>> this situation. It's true that you can start streaming the WAL before
>> the standby has fully caught up. But from the client's point of view,
>> there's not much point in streaming the log *synchronously* and making
>> the client to wait for the acknowledment from the standby, if the
>> acknowledgment from the standby that WAL has be streamed up to point X,
>> doesn't actually guarantee that the slave can recover all the way to
>> that point.
>
> Quite possibly a terminology problem.. I my case I said "sync rep"
> meaning the mode such that the transaction doesn't commit successfully
> for my PG client until the xlog record has been "streamed" to the
> client... and I understand that at his presentation at PGcon, Fujii-san
> there could be possible variants on when the "streamed" is considered
> done based on network, slave ram, disk, application, etc.

I'd like to define the meaning of "synch rep" again. "synch rep" means:

(1) Transaction commit waits for WAL records to be replicated to the standby
before the command returns a "success" indication to the client.

(2) The standby has (can read) all WAL files indispensable for recovery.

If both are true, your system is in "synch rep"; you can perform failover safely
without any transaction loss whenever the primary falls down. On the other
hand, if either is false, your system is in not "synch rep" but "standalone";
the failure of the primary might cause a certain transaction loss. Starting the
standby doesn't mean "synch rep" directly. We have to wait for (1) *and* (2)
after starting the standby. (1) is reported as a server log message, so we can
wait for (1). (2) is somewhat complicated; if an archive is shared, the server
log message for achiving indicates (2). otherwise, The copy operation (copy
indispensable WAL files from the primary to the standby) by the user or
clusterware indicates (2). But, as Simon pointed out, since many people share
an archive, they should monitor only the server log messages. Or, should I
create the feature for the user to confirm whether it's in "synch rep" via SQL?

Since there is a little delay between (1) and (2), we can do WAL streaming
asynchronously only in the delay, as Heikki pointed out. But I'm not sure if
it's worth trying it.

Regards,

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

Hi,

> The idea is that we would be able to have multiple standby servers
> connecting to one primary, yes? It would be useful to have sync
> replication work that it must get an acknowledgement from at least one
> standby before it continues.

No, in my current patch, only one standby can perform WAL streaming.
Of course, Yes in the future (8.5?).

>
> Or do you think we would stream to just one standby, then use the
> archiver (primary or standby) to keep sending files to allow multiple
> additional standby nodes?

Interesting! and Yes, we can.

Regards,

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

* Fujii Masao <masao(dot)fujii(at)gmail(dot)com> [081211 23:00]:
> Hi,

> Or, should I
> create the feature for the user to confirm whether it's in "synch rep" via SQL?

I don't need a way to check via SQL, but I'ld love a postgresql.conf
option that when set would make sure that all connections pretty much
just hang until a slave has connected and everything is setup for "sync
rep". I think I saw that youre using "normal" connection setup to start
the wal streaming to the slave, so you have to allow connections, but
I'ld really not want any of my pg-clients able to do anything if
sync-rep isn't happenning...

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.

Hi,

On Fri, Dec 12, 2008 at 1:34 PM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
> * Fujii Masao <masao(dot)fujii(at)gmail(dot)com> [081211 23:00]:
>> Hi,
>
>> Or, should I
>> create the feature for the user to confirm whether it's in "synch rep" via SQL?
>
> I don't need a way to check via SQL, but I'ld love a postgresql.conf
> option that when set would make sure that all connections pretty much
> just hang until a slave has connected and everything is setup for "sync
> rep". I think I saw that youre using "normal" connection setup to start
> the wal streaming to the slave, so you have to allow connections, but
> I'ld really not want any of my pg-clients able to do anything if
> sync-rep isn't happenning...

How about stopping the request / connection from a client in front of
postgres (e.g. connection pooling software)? Or, we should develop
the feature like OFFLINE of Oracle apart from Synch Rep at first.

Regards,

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

On Fri, 2008-12-12 at 12:53 +0900, Fujii Masao wrote:
> >
> > Quite possibly a terminology problem.. I my case I said "sync rep"
> > meaning the mode such that the transaction doesn't commit successfully
> > for my PG client until the xlog record has been "streamed" to the
> > client... and I understand that at his presentation at PGcon, Fujii-san
> > there could be possible variants on when the "streamed" is considered
> > done based on network, slave ram, disk, application, etc.
>
> I'd like to define the meaning of "synch rep" again. "synch rep" means:
>
> (1) Transaction commit waits for WAL records to be replicated to the standby
> before the command returns a "success" indication to the client.

> (2) The standby has (can read) all WAL files indispensable for recovery.

I would change "can read" in (2) to "has access to". "Can read" implies
we have read all files and checked CRCs of individual records.

The crux of this is what we mean by "synchronous_replication = on".
There are two possible meanings:

1. Commit will wait only if streaming is available and has waited for
all necessary startup conditions.
This provides "Highest Availability"

2. Commit will wait *until* full sync rep is available. So we don't
allow it until standby fails and also don't allow it if standby goes
down.
This provides "Highest Transaction Durability", though is fairly
fragile. Other systems recommend use of multiple standby nodes if this
option is selected.

Perhaps we should add this as a third option to synchronous_replication,
so we have either off, on, only

So far I realise I've been talking exclusively about (1). In that mode
synchronous_replication = on would wait for streaming to complete even
if last WAL file not fully transferred.

For (2) we need a full interlock. Given that we don't currently support
multiple streamed standby servers, it seems not much point in
implementing the interlock (2) would require. Should we leave that part
for 8.5, or do it now?

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

* Simon Riggs <simon(at)2ndQuadrant(dot)com> [081212 08:20]:
>
> 2. Commit will wait *until* full sync rep is available. So we don't
> allow it until standby fails and also don't allow it if standby goes
> down.
> This provides "Highest Transaction Durability", though is fairly
> fragile. Other systems recommend use of multiple standby nodes if this
> option is selected.

yes please!

> Perhaps we should add this as a third option to synchronous_replication,
> so we have either off, on, only
>
> So far I realise I've been talking exclusively about (1). In that mode
> synchronous_replication = on would wait for streaming to complete even
> if last WAL file not fully transferred.

Seems reasonable...

> For (2) we need a full interlock. Given that we don't currently support
> multiple streamed standby servers, it seems not much point in
> implementing the interlock (2) would require. Should we leave that part
> for 8.5, or do it now?

Ugh... If all sync-rep is gong to give is "if it's working, the commit
made it the slaves, but it might not be working [anymore|yet], but you
(the app using pg) have no way of knowing...", that sort of defeats the
point ;-)

I'ld love multiple slaves, but I understand that's not in the current
work, and I understand that it might be hard with the accept & become
wall-sender approach. It should be very easy to make a walsender handle
"multiple" slaves, and voting of quorum/etc as "successfully on slave",
except that we need to get the multiple connections to the walsender
backend...

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, 2008-12-12 at 12:53 +0900, Fujii Masao wrote:
> Or, should I create the feature for the user to confirm whether it's in
> "synch rep" via SQL?
>

I think this would be useful.

Regards,
Jeff Davis

On Fri, 2008-12-12 at 08:57 -0500, Aidan Van Dyk wrote:
> > For (2) we need a full interlock. Given that we don't currently support
> > multiple streamed standby servers, it seems not much point in
> > implementing the interlock (2) would require. Should we leave that part
> > for 8.5, or do it now?
>
> Ugh... If all sync-rep is gong to give is "if it's working, the commit
> made it the slaves, but it might not be working [anymore|yet], but you
> (the app using pg) have no way of knowing...", that sort of defeats the
> point ;-)

http://archives.postgresql.org/pgsql-hackers/2008-12/msg00865.php

Fujii Masao offers to provide a SQL function that will tell you
definitively whether you are in full sync rep, or some degraded mode. I
assume that there will also be server log messages to identify whether
you ever left sync rep mode.

Regards,
Jeff Davis

* Jeff Davis <pgsql(at)j-davis(dot)com> [081212 13:41]:
> On Fri, 2008-12-12 at 08:57 -0500, Aidan Van Dyk wrote:
> > > For (2) we need a full interlock. Given that we don't currently support
> > > multiple streamed standby servers, it seems not much point in
> > > implementing the interlock (2) would require. Should we leave that part
> > > for 8.5, or do it now?
> >
> > Ugh... If all sync-rep is gong to give is "if it's working, the commit
> > made it the slaves, but it might not be working [anymore|yet], but you
> > (the app using pg) have no way of knowing...", that sort of defeats the
> > point ;-)
>
> http://archives.postgresql.org/pgsql-hackers/2008-12/msg00865.php
>
> Fujii Masao offers to provide a SQL function that will tell you
> definitively whether you are in full sync rep, or some degraded mode. I
> assume that there will also be server log messages to identify whether
> you ever left sync rep mode.

So when would I have to call that function? Before begin, after begin,
before commit, or all, to guarentee that know that my application is
suppose to "delay" calling commit until when sync-mode is actualyl
synchronous? And then afterwards, I have to call it again t omake sure
it didn't fall "out of" mode between my previous call and the commit
actually working?

Bugger it, then I'll have to to patch every single app/query that writes
transactions to the database to be "sync rep" aware... And if I miss
one...

Some might say that if the data's that important, that audit/patching to
be "sync rep" aware is worth it, but then I guess they say that then you
might as well do application level replication as well ;-)

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, 2008-12-12 at 14:23 -0500, Aidan Van Dyk wrote:
> So when would I have to call that function? Before begin, after begin,
> before commit, or all, to guarentee that know that my application is
> suppose to "delay" calling commit until when sync-mode is actualyl
> synchronous? And then afterwards, I have to call it again t omake sure
> it didn't fall "out of" mode between my previous call and the commit
> actually working?

I'm not suggesting that applications call the function. It's a way for a
monitoring system to know that you're in a degraded state and notify
you.

I'm not sure I entirely understand the use case you're advocating:

Let's say the standby has a major failure. Now you have a single point
of failure (the primary), so _all_ of your transactions are in jeopardy
anyway -- at least until you get back into sync rep. Rejecting new
transactions won't save your old ones.

The only time it helps is when the failure is temporary, i.e. you didn't
really lose the storage on the standby. But you would need to rely on
some guarantee that the storage is still intact on the standby system
even though the standby is unresponsive.

Is that the use case?

Regards,
Jeff Davis

Hi,

Fujii Masao wrote:
> I'd like to define the meaning of "synch rep" again. "synch rep" means:
>
> (1) Transaction commit waits for WAL records to be replicated to the standby
> before the command returns a "success" indication to the client.
>
> (2) The standby has (can read) all WAL files indispensable for recovery.

Let me point out that - very much like the original Postgres-R algorithm
- this guarantees committed transactions to be durable and consistent
(no late aborts of conflicting transactions), but it does not guarantee
that a transaction committed on one node is immediately visible on the
other node. In that sense, it is not synchronous as commonly understood,
because it does not "operate with all their parts in synchrony" [1], as
implied by the term "synchronous". This might (and often has in the
past) lead to confusion.

It's certainly enough of a reason for me to rather use the term "eager
replication". See [2] for a more in-depth explanation. I might also
point out, that Jan Wieck called this very same approch "an asynchronous
replication system by all means" [3].

Regards

Markus Wanner

[1]: Wikipedia on Synchronization
http://en.wikipedia.org/wiki/Synchronization

[2]: Postgres-R general mailing list, by Markus Wanner, subject:
terms for database replication: synchronous vs eager
http://lists.pgfoundry.org/pipermail/postgres-r-general/2008-September/000014.html

[3]: Postgres General mailing list, by Jan Wieck, subject:
terms for database replication: synchronous vs eager
http://archives.postgresql.org/pgsql-hackers/2007-09/msg00631.php

On Sat, 2008-12-13 at 00:00 +0100, Markus Wanner wrote:
> Hi,
>
> Fujii Masao wrote:
> > I'd like to define the meaning of "synch rep" again. "synch rep" means:
> >
> > (1) Transaction commit waits for WAL records to be replicated to the standby
> > before the command returns a "success" indication to the client.
> >
> > (2) The standby has (can read) all WAL files indispensable for recovery.
>
> Let me point out that - very much like the original Postgres-R algorithm
> - this guarantees committed transactions to be durable and consistent
> (no late aborts of conflicting transactions), but it does not guarantee
> that a transaction committed on one node is immediately visible on the
> other node. In that sense, it is not synchronous as commonly understood,
> because it does not "operate with all their parts in synchrony" [1], as
> implied by the term "synchronous". This might (and often has in the
> past) lead to confusion.

You're right that neither the data transfer nor data availability is
entirely synchronous, but data transfer is synchronous at time of
*commit*: it is recorded on multiple nodes at the same time.

The term "synchronous replication" is already well used in the industry
to mean synchronous commit, so I don't think we should change the name
now. The project here is also known to everybody as "synch rep".

* Oracle Data Guard calls it "synchronous redo transport"
* MS Exchange calls it "synchronous replication"
* MS SQL Server has "Database Mirroring", "Log Shipping" and
"Replication". "Database Mirroring" provides synchronous mechanism, with
"Replication" meaning data transfer to other databases,
publish&subscribe.
* DB2 HADR provides "synchronous replication"
* MySQL call it "synchronous replication"

What is confusing is that "replication" itself is a much abused term and
is used to describe technologies for HA, DR and data movement.

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

Hi,

Simon Riggs wrote:
> You're right that neither the data transfer nor data availability is
> entirely synchronous, but data transfer is synchronous at time of
> *commit*: it is recorded on multiple nodes at the same time.

I'm unsure what you mean by a "data transfer being synchronous". To what
other process or state should the data transfer be synchronous to?

> The term "synchronous replication" is already well used in the industry
> to mean synchronous commit, so I don't think we should change the name
> now. The project here is also known to everybody as "synch rep".

I understand very well, that you don't want to change the name. I've
been hesitant to "relabel" Postgres-R from synchronous to asynchronous
to eager.

However, that is a marketing decision [1], which should not be mixed
with the technical discussion here. Speaking of a "synchronous commit"
is utterly misleading, because the commit itself is exactly the thing
that's *not* synchronous.

It *is* an optimization to fully synchronous replication to defer commit
on the "slave" and only make sure that the transaction *can* be applied
at some time in the future.

However, this *does* have the drawback of transactions not being
immediately visible on the slave. Often enough, this is acceptable. But
it certainly matters to some applications developers.

> What is confusing is that "replication" itself is a much abused term and
> is used to describe technologies for HA, DR and data movement.

I absolutely agree to that. And I'm thus recommending to at least be
consistent and honest with the term "synchronous" and point out that WAL
writing is synchronous for the log shipping approach here (AFAIK). But
that the commit is asynchronous for performance reasons. In other words:
this approach is certainly (and hopefully, for performance reasons)
different from a fully synchronous approach. Even for marketing reasons,
it might make sense to point out that difference (.. "no, we are faster
than fully sync rep.").

Regards

Markus Wanner

[1]: Some people like the term "virtually synchronous" for marketing
purposes. That's at least half-ways technically correct.

On 2008-12-13, at 13:07, Markus Wanner wrote:
>
>
> However, that is a marketing decision [1], which should not be mixed
> with the technical discussion here. Speaking of a "synchronous commit"
> is utterly misleading, because the commit itself is exactly the thing
> that's *not* synchronous.
>

> [1]: Some people like the term "virtually synchronous" for marketing
> purposes. That's at least half-ways technically correct.

Marketing people are virtually trustworthy, from my life experience.
If you ask me, this is just preposterous.

On Sat, 2008-12-13 at 14:07 +0100, Markus Wanner wrote:

> Speaking of a "synchronous commit"
> is utterly misleading, because the commit itself is exactly the thing
> that's *not* synchronous.

Not really sure where you're going here. "synchronous replication" is
used exactly as described in the Wikipedia entry here:
http://en.wikipedia.org/wiki/Database_replication

No two word phrase is going to accurately sum up the complexity and
potential for data loss in these situations. DRBD saw that too and just
called them A, B and C and then describe them more accurately.

But I don't think we should say "PostgreSQL just implemented algorithm
B" which is just unhelpful. I don't think its "marketing" to refer to it
by the phrase most commonly used for the technology we are building.
Nobody suggested we call it "wizrep" or suchlike...

The docs can contain the exact description of data loss and timing
windows.

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

Hi,

Simon Riggs wrote:
> On Sat, 2008-12-13 at 14:07 +0100, Markus Wanner wrote:
>> Speaking of a "synchronous commit"
>> is utterly misleading, because the commit itself is exactly the thing
>> that's *not* synchronous.
>
> Not really sure where you're going here.

I'm pointing to a potential misunderstanding, trying to help to prevent
you from running into the same issues and discussions as I did.

I've learned the hard way, that the Postgres-R algorithm is not fully
synchronous (in the strict sense). This caused confusion for people who
take the word "synchronous" by its original meaning. The algorithm
proposed here seems similar enough to potentially cause the same confusion.

As I see it now, I think it's well worth to point out the difference,
from both, the technical as well as from the marketing perspective. The
former for better understanding, the later to prevent users from
thinking it must be slow per definition. Arguing that your approach is
not fully synchronous definitely helps defending that concern.

However, I'm just now realizing, that the difference is only relevant as
soon as you begin to allow read-only access on the slave. AFAIK that's
among the goals of this effort, no?

> "synchronous replication" is
> used exactly as described in the Wikipedia entry here:
> http://en.wikipedia.org/wiki/Database_replication

That article describes pretty much all variants of replication, what
exactly are you referring to?

Under "Database Replication > Multi-Master replication" it describes
eager vs lazy variants, which is IMO a more appropriate and useful
distinction than sync vs async. (But that's admittedly a sentence I've
contributed myself, IIRC).

Under "Storage Replication > Synchronous Replication" one can read:
"Write is not considered complete until acknowledgement by both local
and remote storage." For the proposed approach this might hold true for
WAL writing. However, the user certainly doesn't care how synchronous
the log is shipped nor written, is as long as she doesn't see the
changes on the slave.

That's the difference between fully synchronous and eager (or virtually
or approximately synchronous) algorithms. You seem to refer to both as
"synchronous". Phrases like "synchronous commit" or "synchronous data
transfer" do not help me to understand what exactly you are talking about.

Explaining that the slave commits (and therefore makes the transactions
visible) asynchronously would help. And it would prevent disappointment
for users who expect changes to be immediately visible on the slave.

> No two word phrase is going to accurately sum up the complexity and
> potential for data loss in these situations. DRBD saw that too and just
> called them A, B and C and then describe them more accurately.

Agreed. I've chosen lazy, eager and sync, so far. I'm open for better
terms, and I leave it up to you to call your variants whatever you like.
But to understand what you are talking about, I'd prefer to get to know
these distinctions crisp and clear.

> But I don't think we should say "PostgreSQL just implemented algorithm
> B" which is just unhelpful. I don't think its "marketing" to refer to it
> by the phrase most commonly used for the technology we are building.

I certainly agree to using such terms. Unfortunately, in my experience,
synchronous replication is commonly used to mean that transactions are
guaranteed to be immediately visible on remote nodes after the client
got commit acknowledgment. That's the cause for confusion I'm envisioning.

I'm hoping to be somewhat helpful to this effort of getting a log
shipping replication variant into Postgres. It can only be beneficial
for Postgres-R in that we gain field experience with ..uhm.. this
special kind of replication, however we name it.

I'm already on xmas vacation, so I won't bother you any further on this
issue. Have fun coding and make sure to enjoy this time of the year.

All the best.

Markus Wanner

> I certainly agree to using such terms. Unfortunately, in my experience,
> synchronous replication is commonly used to mean that transactions are
> guaranteed to be immediately visible on remote nodes after the client
> got commit acknowledgment. That's the cause for confusion I'm envisioning.

I think that's a very important point. It's very possible that 8.4
may support both this feature and Hot Standby (although the latter
seems to have stalled a bit...). That makes me think "oh, great, I
can offload any subset of my read-only queries to the standby". Not
so fast.

I think we need to reserve the term "synchronous replication" for a
system where transactions that begin at the same time on the primary
and standby see the same tuples. Clearly that is "more" synchronous
than what is being proposed here; if we call this "synchronous
replication", what will we call that? "Really Synchronous, Honest, No
Kidding"? Admittedly, we may never implement that feature, but that
seems irrelevant.

It would be useful to have names for all the different possibilities.
Random ideas:

Log Shipping. After each log switch, the previous WAL log is copied
to the standby in its entirety.

WAL Streaming - Asynchronous. The WAL log is streamed from master to
standby as it is written, but transactions on the master never wait.

WAL Streaming - Synchronous Receive. The WAL log is streamed from
master to standby as it is written, and transactions on the master
wait until the standby acknowledges receipt of the WAL.

WAL Streaming - Synchronous Write. The WAL log is streamed from
master to standby as it is written, and transactions on the master
wait until the standby acknowledges that the WAL has been written to
disk.

WAL Streaming - Synchronous Apply. The WAL log is streamed from
master to standby as it is written, and transactions on the master
wait until the standby acknowledges that WAL has been written to disk
and applied.

...Robert

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
> I think we need to reserve the term "synchronous replication" for a
> system where transactions that begin at the same time on the primary
> and standby see the same tuples. Clearly that is "more" synchronous
> than what is being proposed here; if we call this "synchronous
> replication", what will we call that? "Really Synchronous, Honest, No
> Kidding"? Admittedly, we may never implement that feature, but that
> seems irrelevant.

We won't call it anything, because we never will or can implement that.
See the theory of relativity: the notion of exactly simultaneous events
at distinct locations isn't even well-defined, because observers at yet
other locations will disagree about what is "simultaneous". And I'm
not just making a joke here --- speed-of-light delays in a WAN are
meaningful compared to current computer speeds. In practice, the
slave and the master will never commit at exactly the same time.

I agree with the point made upthread that we should use the term
"synchronous replication" the way it's commonly used in the industry.
Inventing our own terminology might be fun but it's not really going
to result in less confusion.

regards, tom lane

Synchronous replication, "sync rep" is *not* intersted in the "slave's
visiblity of the commit", because PostgreSQL doesn't "serve" requests
when in recovery (wal receiving) mode *now*.

This sync rep patch/proposal/discution is *strictly* (at this point yet,
hot standby may eventually or hopefully soon change that) the means to
get the data "safely in 2 seperate places", before the COMMIT returns,
by means of wal streaming. That "safely in 2 places" can have various
implementation options (like received, on disk, or applied), and
Fujii-san explained some of the options as to what to consider "safe"
and their trade-offs at his presentation at last year.

Once both sync-rep (the wal-streaming get changes in two places) and
hot-standby (run queries while WAL is being applied) are available in
PostgreSQL, at that point we might need to start "other client
visibility", but even then, we still don't need to worry about
multi-master options...

a.

* Markus Wanner <markus(at)bluegap(dot)ch> [081213 12:17]:
> Hi,
>
> Simon Riggs wrote:
> > On Sat, 2008-12-13 at 14:07 +0100, Markus Wanner wrote:
> >> Speaking of a "synchronous commit"
> >> is utterly misleading, because the commit itself is exactly the thing
> >> that's *not* synchronous.
> >
> > Not really sure where you're going here.
>
> I'm pointing to a potential misunderstanding, trying to help to prevent
> you from running into the same issues and discussions as I did.
>
> I've learned the hard way, that the Postgres-R algorithm is not fully
> synchronous (in the strict sense). This caused confusion for people who
> take the word "synchronous" by its original meaning. The algorithm
> proposed here seems similar enough to potentially cause the same confusion.
>
> As I see it now, I think it's well worth to point out the difference,
> from both, the technical as well as from the marketing perspective. The
> former for better understanding, the later to prevent users from
> thinking it must be slow per definition. Arguing that your approach is
> not fully synchronous definitely helps defending that concern.
>
> However, I'm just now realizing, that the difference is only relevant as
> soon as you begin to allow read-only access on the slave. AFAIK that's
> among the goals of this effort, no?
>
> > "synchronous replication" is
> > used exactly as described in the Wikipedia entry here:
> > http://en.wikipedia.org/wiki/Database_replication
>
> That article describes pretty much all variants of replication, what
> exactly are you referring to?
>
> Under "Database Replication > Multi-Master replication" it describes
> eager vs lazy variants, which is IMO a more appropriate and useful
> distinction than sync vs async. (But that's admittedly a sentence I've
> contributed myself, IIRC).
>
> Under "Storage Replication > Synchronous Replication" one can read:
> "Write is not considered complete until acknowledgement by both local
> and remote storage." For the proposed approach this might hold true for
> WAL writing. However, the user certainly doesn't care how synchronous
> the log is shipped nor written, is as long as she doesn't see the
> changes on the slave.
>
> That's the difference between fully synchronous and eager (or virtually
> or approximately synchronous) algorithms. You seem to refer to both as
> "synchronous". Phrases like "synchronous commit" or "synchronous data
> transfer" do not help me to understand what exactly you are talking about.
>
> Explaining that the slave commits (and therefore makes the transactions
> visible) asynchronously would help. And it would prevent disappointment
> for users who expect changes to be immediately visible on the slave.
>
> > No two word phrase is going to accurately sum up the complexity and
> > potential for data loss in these situations. DRBD saw that too and just
> > called them A, B and C and then describe them more accurately.
>
> Agreed. I've chosen lazy, eager and sync, so far. I'm open for better
> terms, and I leave it up to you to call your variants whatever you like.
> But to understand what you are talking about, I'd prefer to get to know
> these distinctions crisp and clear.
>
> > But I don't think we should say "PostgreSQL just implemented algorithm
> > B" which is just unhelpful. I don't think its "marketing" to refer to it
> > by the phrase most commonly used for the technology we are building.
>
> I certainly agree to using such terms. Unfortunately, in my experience,
> synchronous replication is commonly used to mean that transactions are
> guaranteed to be immediately visible on remote nodes after the client
> got commit acknowledgment. That's the cause for confusion I'm envisioning.
>
>
> I'm hoping to be somewhat helpful to this effort of getting a log
> shipping replication variant into Postgres. It can only be beneficial
> for Postgres-R in that we gain field experience with ..uhm.. this
> special kind of replication, however we name it.
>
> I'm already on xmas vacation, so I won't bother you any further on this
> issue. Have fun coding and make sure to enjoy this time of the year.
>
> All the best.
>
> Markus Wanner
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
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 Sat, 2008-12-13 at 13:05 -0500, Robert Haas wrote:

> Hot Standby (although the latter
> seems to have stalled a bit...)

It's just being worked on asynchronously. ;-)

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

On Sat, 2008-12-13 at 13:05 -0500, Robert Haas wrote:
> > I certainly agree to using such terms. Unfortunately, in my experience,
> > synchronous replication is commonly used to mean that transactions are
> > guaranteed to be immediately visible on remote nodes after the client
> > got commit acknowledgment. That's the cause for confusion I'm envisioning.
>
> I think that's a very important point. It's very possible that 8.4
> may support both this feature and Hot Standby (although the latter
> seems to have stalled a bit...). That makes me think "oh, great, I
> can offload any subset of my read-only queries to the standby". Not
> so fast.
>
> I think we need to reserve the term "synchronous replication" for a
> system where transactions that begin at the same time on the primary
> and standby see the same tuples.

Define "same time".

You can have a variantof sync rep + hot standby where the master does
not return committed before the slave has both synced the data and
replied the transaction so that it is visible on slave but in that case
you may have a usecase, where it is actually visible on slave _before_
it is visible on master.

actually you can't have that "same time" guarantee even on single
system, that is, if you start two transactions connections "at the same
time", you still cant be sure there is not third transaction which has
committed between those two and which makes the visible data on those
two different.

> Clearly that is "more" synchronous
> than what is being proposed here; if we call this "synchronous
> replication", what will we call that? "Really Synchronous, Honest, No
> Kidding"? Admittedly, we may never implement that feature, but that
> seems irrelevant.
>
> It would be useful to have names for all the different possibilities.
> Random ideas:
>
> Log Shipping. After each log switch, the previous WAL log is copied
> to the standby in its entirety.
>
> WAL Streaming - Asynchronous. The WAL log is streamed from master to
> standby as it is written, but transactions on the master never wait.
>
> WAL Streaming - Synchronous Receive. The WAL log is streamed from
> master to standby as it is written, and transactions on the master
> wait until the standby acknowledges receipt of the WAL.
>
> WAL Streaming - Synchronous Write. The WAL log is streamed from
> master to standby as it is written, and transactions on the master
> wait until the standby acknowledges that the WAL has been written to
> disk.
>
> WAL Streaming - Synchronous Apply. The WAL log is streamed from
> master to standby as it is written, and transactions on the master
> wait until the standby acknowledges that WAL has been written to disk
> and applied.

We still could call Sync Rep as a feature "synchronous replication" on
basis that "WAL Streaming - Synchronous Write" is the highest security
level achievable using the feature.

And maybe have Sync Hot Standby as a feature on top of that which
provides "WAL Streaming - Synchronous Apply"

------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

On Sat, 2008-12-13 at 21:35 +0200, Hannu Krosing wrote:

> We still could call Sync Rep as a feature "synchronous replication" on
> basis that "WAL Streaming - Synchronous Write" is the highest security
> level achievable using the feature.
>
> And maybe have Sync Hot Standby as a feature on top of that which
> provides "WAL Streaming - Synchronous Apply"

Or maybe better call it Serializable Hot Standby, as the actual
guarantee that can be achieved is that when one client does something on
master and after committing on master starts another transaction on
slave, then the effects of query on master are visible on slave.

--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

Hi,

Tom Lane wrote:
> We won't call it anything, because we never will or can implement that.
> See the theory of relativity: the notion of exactly simultaneous events
> at distinct locations isn't even well-defined

That has never been the point of the discussion. It's rather about the
question if changes from transactions are guaranteed to be visible on
remote nodes immediately after commit acknowledgment. Whether or not
this is guaranteed, in both cases the term "synchronous replication" is
commonly used, which is causing confusion.

Regards

Markus Wanner

Hi,

Simon Riggs wrote:
>> Hot Standby (although the latter
>> seems to have stalled a bit...)
>
> It's just being worked on asynchronously. ;-)

LOL, thanks for bringing humor into this discussion :-)

Regards

Markus Wanner

Hi,

Hannu Krosing wrote:
> You can have a variantof sync rep + hot standby where the master does
> not return committed before the slave has both synced the data and
> replied the transaction so that it is visible on slave but in that case
> you may have a usecase, where it is actually visible on slave _before_
> it is visible on master.

As long as it's not visible *before* the client requests a COMMIT, that
certainly doesn't matter (because the application cannot check that).

What matters is, that an application might expect a node to show the
changes of a transaction which has previously (seen from the application
itself) been committed and acknowledged by another node.

AFAICT the common understanding of synchronous replication is, that all
nodes confirm to have committed the changes of a transaction *before*
acknowledging COMMIT to the application (and obviously only *after* the
application requested to COMMIT the transaction, so the guarantee is
that all nodes commit *sometime* within that time frame, which is
certainly possible to guarantee, see 2PC approaches).

This guarantee is not provided by the Postgres-R algorithm, nor by the
approach presented. Both only guarantee, that the transaction *will* get
committed (and thus get visible) on all nodes *sometime* *after* the
application requested to commit it (even in case of various failures,
that is) [1]. As cited before, that has been enough of a reason for Jan
Wieck to call Postgres-R asynchronous, and I certainly see his point.

Note that the amount of time that passes between the commit
acknowledgment and the actual commit on remote nodes may theoretically
be infinitely long. And in practice certainly long enough for an
application to notice the difference. However, it still is a practical
optimization, because most applications should cope with it just fine.
But not all...

Do you consider the proposed log shipping approach to be synchronous?
How about the Postgres-R algorithm?

Regards

Markus Wanner

[1]: of course these approaches also guarantee that the transaction is
committed on the local node *before* acknowledging commit, so that
subsequent (seen from the application) queries are guaranteed to see the
changes. But that guarantee only holds true for the local node.

* Markus Wanner <markus(at)bluegap(dot)ch> [081213 16:33]:
> Hi,
>
> Hannu Krosing wrote:
> > You can have a variantof sync rep + hot standby where the master does
> > not return committed before the slave has both synced the data and
> > replied the transaction so that it is visible on slave but in that case
> > you may have a usecase, where it is actually visible on slave _before_
> > it is visible on master.
>
> As long as it's not visible *before* the client requests a COMMIT, that
> certainly doesn't matter (because the application cannot check that).

Well, I think the PG MVCC (which wal-streaming just ships across
somewhere else) will save that. So with hot-standby you could have
another client could see the result *after* the COMMIT has been
requested, but *before* the COMMIT returns... But we have this
situation in a single current PG instance anyways, so it's nothing
new....

But with hot-standby, I could also see that it could be done such that
the wal-stream is fsynced to disk (i.e. xlog) and acknowledged, but
because of a current running query, application of it is delayed... But
this is hot-standby's problem of describing itself, not sync-rep.

IMHO, sync-rep is about getting the change "durrably to a slave" before
acknoledging the COMMIT. That slave could be any number of things:
- A "WAL archive" type system having the ability to be used for
recover
- A PG with special "recovery mode" that reads the stream and applies it
- A full hot-standby recovery

I could see any and all of those (and probably other) being usefull and
used.

But in the current patch, it focusses on the streaming (sending), and
and a receiver "recovery" mode that can accept/apply them, again,
without worrying about acutally running queries (yet) ...

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.

Markus Wanner wrote:
> Tom Lane wrote:
>
>> We won't call it anything, because we never will or can implement that.
>> See the theory of relativity: the notion of exactly simultaneous events
>> at distinct locations isn't even well-defined
>>
>
> That has never been the point of the discussion. It's rather about the
> question if changes from transactions are guaranteed to be visible on
> remote nodes immediately after commit acknowledgment. Whether or not
> this is guaranteed, in both cases the term "synchronous replication" is
> commonly used, which is causing confusion.
>

Might it not be true that anybody unfamiliar would be confused and that
this is a bit of a straw man?

I don't think synchronous replication guarantees that it will be
immediately visible. Even if it did push the change to the other
machine, and the other machine had committed it, that doesn't guarantee
that any reader sees it any more than if I commit to the same machine
(no replication), I am guaranteed to see the change from another
session. Synchronous replication only means that I can be assured that
my change has been saved permanently by the time my commit completes. It
doesn't mean anybody else can see my change or is guaranteed to see my
change if the query from another session.

If my application assumes that it can commit to one server, and then
read back the commit from another server, and my application breaks as a
result, it's because I didn't understand the problem. Even if PostgreSQL
didn't use the word "synchronous replication", I could still be
confused. I need to understand the problem no matter what words are used.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

Hi,

Aidan Van Dyk wrote:
> Well, I think the PG MVCC (which wal-streaming just ships across
> somewhere else) will save that. So with hot-standby you could have
> another client could see the result *after* the COMMIT has been
> requested, but *before* the COMMIT returns... But we have this
> situation in a single current PG instance anyways, so it's nothing
> new....

AFAIU the proposed algorithm only waits until WAL is written on the
slave before acknowledging COMMIT. Application of the changes may be
deferred, so it's not necessarily immediately visible on the slave.

> But with hot-standby, I could also see that it could be done such that
> the wal-stream is fsynced to disk (i.e. xlog) and acknowledged, but
> because of a current running query, application of it is delayed... But
> this is hot-standby's problem of describing itself, not sync-rep.

I'm thinking of the overall system and don't care much if it's
hot-standby's or sync-rep's problem. But it's certainly the master which
needs to await certain acknowledgments of the slaves. That has so far
been discussed within this sync-rep thread.

> IMHO, sync-rep is about getting the change "durrably to a slave" before
> acknoledging the COMMIT. That slave could be any number of things:
> - A "WAL archive" type system having the ability to be used for
> recover
> - A PG with special "recovery mode" that reads the stream and applies it
> - A full hot-standby recovery
>
> I could see any and all of those (and probably other) being usefull and
> used.

I certainly agree to that.

Regards

Markus Wanner

Hi,

Mark Mielke wrote:
> Might it not be true that anybody unfamiliar would be confused and that
> this is a bit of a straw man?

Might be. I've neglected the issue myself for a while.

> I don't think synchronous replication guarantees that it will be
> immediately visible. Even if it did push the change to the other
> machine, and the other machine had committed it, that doesn't guarantee
> that any reader sees it any more than if I commit to the same machine
> (no replication), I am guaranteed to see the change from another
> session.

AFAIK every snapshot taken after a transaction has acknowledged its
commit is guaranteed to see changes from that transaction. Isn't that a
pretty frequent and obvious user expectation?

> Synchronous replication only means that I can be assured that
> my change has been saved permanently by the time my commit completes. It
> doesn't mean anybody else can see my change or is guaranteed to see my
> change if the query from another session.

So you wouldn't be surprised if a transaction from two hours ago isn't
visible on another node, just because that node happens to be rather
busy with lots of other readers and maintenance tasks?

> If my application assumes that it can commit to one server, and then
> read back the commit from another server, and my application breaks as a
> result, it's because I didn't understand the problem.

Well, yeah, depends on user expectations. I'm surprised to hear that you
have that understanding of synchronous replication.

> Even if PostgreSQL
> didn't use the word "synchronous replication", I could still be
> confused. I need to understand the problem no matter what words are used.

As said, it depends on what the common understanding of "synchronous
replication" is. I've so far been under the impression, that these
potential lags are unexpected and confusing. Several people pointed me
at that problem and I've thus "relabeled" Postgres-R as not being
synchronous. I'm at least surprised to suddenly get pushed into the
other direction. :-)

However, I absolutely agree that it's not that important how we name it.
What is important, is that users and developers understand the difference.

Regards

Markus Wanner

Markus Wanner wrote:
>> I don't think synchronous replication guarantees that it will be
>> immediately visible. Even if it did push the change to the other
>> machine, and the other machine had committed it, that doesn't guarantee
>> that any reader sees it any more than if I commit to the same machine
>> (no replication), I am guaranteed to see the change from another
>> session.
>>
>
> AFAIK every snapshot taken after a transaction has acknowledged its
> commit is guaranteed to see changes from that transaction. Isn't that a
> pretty frequent and obvious user expectation?
>

Yes - but that's only really true while the session continues. From
another session? I've never assumed that I could reconnect and be
guaranteed to get the latest snapshot that includes absolutely
everything that has been committed.

Any system that guaranteed this even when involving multiple machines
would be guaranteed to be inefficient and difficult to scale in my
opinion. How could any system promise to have reasonable commit times
while also guaranteeing that once a commit completes, any session to any
other server will be able to see the commit? I think this forces some
sort of serialization between multiple machines and defeats the purpose
of having multiple machines. Where before it was indeterminate to know
when the commit would take effect at each replica, it's not
indeterminate when my commit will succeed. That is, my commit cannot
succeed until every single server acknowledge that it is has fully
received and committed my transaction. What happens if there are network
problems, or what happens if I am replicating over a slower link? What
if I am committing to 100 servers? Is it reasonable to expect 100 server
negotiations to complete in full before my own commit will return?

>> Synchronous replication only means that I can be assured that
>> my change has been saved permanently by the time my commit completes. It
>> doesn't mean anybody else can see my change or is guaranteed to see my
>> change if the query from another session.
>>
> So you wouldn't be surprised if a transaction from two hours ago isn't
> visible on another node, just because that node happens to be rather
> busy with lots of other readers and maintenance tasks?
>

Any system that is two hours behind should fall out of the pool used to
satisfy reads from. So, if there was a surprise, it would be this. I
don't believe ACID requires that a commit on one server is immediately
visible on another server. Any work I do on the "behind" server would
still be safe from a transaction and referential integrity perspective.
However, if I executed 'commit' on this "behind" server, I would expect
the commit to wait until it catches up, or in the case of a 2 hour
behind, I would expect the commit to fail. Look at the alternative - all
commits to any server in the pool would be locked up waiting for this
one machine to catch up on 2 hours of transaction. This emphasizes that
the problem is that a server two hours of date is still in the pool,
rather than the problem being keeping things up-to-date.

>> If my application assumes that it can commit to one server, and then
>> read back the commit from another server, and my application breaks as a
>> result, it's because I didn't understand the problem.
>>
> Well, yeah, depends on user expectations. I'm surprised to hear that you
> have that understanding of synchronous replication.
>

I've seen people face it in the past. Most recently we had a
presentation from the developer of digg.com, and he described how he had
this problem with MySQL and that he had to work around it.

On a smaller scale and slightly unrelated, I had this problem frequently
between memcache and PostgreSQL. That is, memcache would always be
latest, but PostgreSQL might not be latest, because the commit had not
occurred.

It seems like a standard enough problem to me. I don't expect Postgres-R
to do the impossible. As with my previous paragraph, I don't expect
Postgres-R to wait 2-hours to commit just because one server is falling
behind.

>> Even if PostgreSQL
>> didn't use the word "synchronous replication", I could still be
>> confused. I need to understand the problem no matter what words are used.
>>
>
> As said, it depends on what the common understanding of "synchronous
> replication" is. I've so far been under the impression, that these
> potential lags are unexpected and confusing. Several people pointed me
> at that problem and I've thus "relabeled" Postgres-R as not being
> synchronous. I'm at least surprised to suddenly get pushed into the
> other direction. :-)
>
> However, I absolutely agree that it's not that important how we name it.
> What is important, is that users and developers understand the difference

I agree they are unexpected and confusing. I don't agree that they are
unexpected or confusing to those knowledgeable in the domain. So, the
question becomes - whose expectation is wrong? Should the user learn
more? Or should we push for a change in terminology? Does it make sense
for Postgres-R (which looks excellent to me BTW, at least in principle)
be marketed differently, because a few users tie "synchronous
replication" to "serialized access"?

Because that's really what we're talking about - we're talking about
transactions in all sessions being serialized between machines to
provide less surprise to users who don't understand the complexity of
having multiple replicas.

Forget replication - even for the exact same server - I don't expect
that if I commit from one session, I will be able to see the change
immediately from my other session or a new session that I just opened.
Perhaps this is often stable to rely on this, and it is useful for the
database server to minimize the window during which the commit becomes
visible to others, but I think it's a false expectation from the start
that it absolutely will be immediately visible to another session. I'm
thinking of situations where some part of the table is in cache. The
only way the commit can communicate that the new transaction is
available is by during communication between the processes or threads,
or between the multiple CPUs on the machine. Do I want every commit to
force each session to become fully in alignment before my commit
completes? Does PostgreSQL make this guarantee today? I bet it doesn't
if you look far enough into the guts. It might be very fast - I don't
think it is infinitely fast.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

On Sat, Dec 13, 2008 at 1:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
>> I think we need to reserve the term "synchronous replication" for a
>> system where transactions that begin at the same time on the primary
>> and standby see the same tuples. Clearly that is "more" synchronous
>
> We won't call it anything, because we never will or can implement that.
> See the theory of relativity: the notion of exactly simultaneous events

OK, fine. I'll be more precise. I think we need to reserve the term
"synchronous replication" for a system where transactions that begin
on the standby after the transactions has committed on the master see
the effects of the committed transaction.

> at distinct locations isn't even well-defined, because observers at yet
> other locations will disagree about what is "simultaneous". And I'm
> not just making a joke here --- speed-of-light delays in a WAN are
> meaningful compared to current computer speeds. In practice, the
> slave and the master will never commit at exactly the same time.
>
> I agree with the point made upthread that we should use the term
> "synchronous replication" the way it's commonly used in the industry.
> Inventing our own terminology might be fun but it's not really going
> to result in less confusion.

I just googled "synchronous replication" and read through the first
page of hits. Most of them do not address the question of whether
synchronous replication can be said to have be completed when WAL has
been received by the standby not but yet applied. One of the ones
that does is:

http://code.google.com/p/google-mysql-tools/wiki/SemiSyncReplicationDesign

...which refers to what we're proposing to call "Synchronous
Replication" as "Semi-Synchronous Replication" (or 2-safe replication)
specifically to distinguish it. The other is:

http://www.cnds.jhu.edu/pub/papers/cnds-2002-4.pdf

...which doesn't specifically examine the issue but seems to take the
opposite position, namely that the server on which the transaction is
executed needs to wait only for one server to apply the changes to the
database (the others need only to know that they need to commit it;
they don't actually need to have done it). However, that same paper
refers to two-phase commit as a synchronous replication algorithm, and
Wikipedia's discussion of two-phase commit:

http://en.wikipedia.org/wiki/Two-phase_commit_protocol

...clearly implies that the transaction must be applied everywhere
before it can be said to have committed.

The second page of Google results is mostly a further discussion of
the MySQL solution, which is mostly described as "semi-synchronous
replication".

Simon Riggs said upthread that Oracle called this "synchronous redo
transport". That is obviously much closer to what we are doing than
"synchronous replication".

...Robert

On Sat, 2008-12-13 at 21:35 -0500, Robert Haas wrote:
> On Sat, Dec 13, 2008 at 1:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
> >> I think we need to reserve the term "synchronous replication" for a
> >> system where transactions that begin at the same time on the primary
> >> and standby see the same tuples. Clearly that is "more" synchronous
> >
> > We won't call it anything, because we never will or can implement that.
> > See the theory of relativity: the notion of exactly simultaneous events
>
> OK, fine. I'll be more precise. I think we need to reserve the term
> "synchronous replication" for a system where transactions that begin
> on the standby after the transactions has committed on the master see
> the effects of the committed transaction.
>

If it's guaranteed to be visible on the standby after it's committed on
the master, and you don't have any way to make it actually simultaneous,
then that implies that it's visible on the slave for some brief period
of time before it's committed on the master.

That situation is still asymmetric, so why is that a better use of the
term "synchronous"?

Regards,
Jeff Davis

> If it's guaranteed to be visible on the standby after it's committed on
> the master, and you don't have any way to make it actually simultaneous,
> then that implies that it's visible on the slave for some brief period
> of time before it's committed on the master.
>
> That situation is still asymmetric, so why is that a better use of the
> term "synchronous"?

Because that happens anyway. If I request a commit on a single,
unreplicated server, the server makes the commit visible to new
transactions and then sends me a message informing me that the commit
has completed. Since the message takes some finite time to reach me,
there is a window of time after the commit has completed and before I
know that the commit has been completed.

Suppose for the sake of argument that the single, unreplicated server
did these two tasks in the opposite order - namely, first, it sent a
message to the process requesting the commit stating that the commit
had completed, and only then made the transaction visible. This would
create a race condition: the process requesting the commit might
receive the commit and begin a new transaction before the previous
transaction had been made visible, and would therefore not be able to
see the results of its own previous actions. I think it's fair to say
that this behavior would be judged totally intolerable.

Therefore, there can't possibly be any applications out there which
are depending on the fact that commits don't become visible until they
are acknowledged, but there very well could be some applications which
depend on the fact that one commits are acknowledged, they are
visible. If replication is synchronous in this sense, then I can open
a connection to the master, write some data, close the connection,
open a new connection to the master or the slave (not caring which),
and read back the data that I just wrote (assuming no one else has
modified it in the mean time). If it isn't, then I can't. Some
people will not care about this, but some will.

The point here is that synchronous replication, at least to some
people, is going to imply that the user-visible states of the two
copies are consistent. To other people, it is going to imply that
committed transactions will never be lost even in the event of a
catastropic loss of the primary 1 picosecond after the commit is
acknowledged. We need to choose some word that implies that we are
guaranteeing the latter of these two things but not the former.
Otherwise, we will have confused users, and terminological confusion
when and if we ever implement the former as well.

...Robert

> Might it not be true that anybody unfamiliar would be confused and that this
> is a bit of a straw man?
[...]
> If my application assumes that it can commit to one server, and then read
> back the commit from another server, and my application breaks as a result,
> it's because I didn't understand the problem. Even if PostgreSQL didn't use
> the word "synchronous replication", I could still be confused. I need to
> understand the problem no matter what words are used.

That is certainly true. But there is value in choosing words which
elucidate the situation as much as possible.

...Robert

On Sat, 2008-12-13 at 22:23 -0500, Robert Haas wrote:
> > If it's guaranteed to be visible on the standby after it's committed on
> > the master, and you don't have any way to make it actually simultaneous,
> > then that implies that it's visible on the slave for some brief period
> > of time before it's committed on the master.
> >
> > That situation is still asymmetric, so why is that a better use of the
> > term "synchronous"?
>
> Because that happens anyway. If I request a commit on a single,
> unreplicated server, the server makes the commit visible to new
> transactions and then sends me a message informing me that the commit
> has completed. Since the message takes some finite time to reach me,
> there is a window of time after the commit has completed and before I
> know that the commit has been completed.
>

Oh, I see the distinction now.

Thanks for the detailed reply.

Regards,
Jeff Davis

> The point here is that synchronous replication, at least to some
> people, is going to imply that the user-visible states of the two
> copies are consistent. To other people, it is going to imply that
> committed transactions will never be lost even in the event of a
> catastropic loss of the primary 1 picosecond after the commit is
> acknowledged. We need to choose some word that implies that we are
> guaranteeing the latter of these two things but not the former.
> Otherwise, we will have confused users, and terminological confusion
> when and if we ever implement the former as well.

Right. Before watching this thread, I had thought that the log
shipping sync replication behaves former (and I had told so to people
in Japan who are interested in 8.4 development. Of course this is my
fault, though).

Now I understand the log shipping sync replication does not behave
same as other "sync replications" such as pgpool and PGCluster (there
maybe more, but I don't know)
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> The point here is that synchronous replication, at least to some
> people, is going to imply that the user-visible states of the two
> copies are consistent. To other people, it is going to imply that
> committed transactions will never be lost even in the event of a
> catastropic loss of the primary 1 picosecond after the commit is
> acknowledged. We need to choose some word that implies that we are
> guaranteeing the latter of these two things but not the former.
> Otherwise, we will have confused users, and terminological confusion
> when and if we ever implement the former as well.

With apologies for replying to my own post:

It's also important to understand that these two invariants are
completely separate and it is possible to guarantee either without the
other. If you want (1), the standby needs to apply the WAL before
sending an acknowledgment to the primary but does not necessarily need
to write it to disk (of course, it will have to be written to disk
before the modified buffers are written to disk, but that's a separate
issue). If you want (2), the standby needs to write the WAL to disk
before sending the acknowledgment but does not necessarily need to
apply it. If you want both, then, you need to wait for both (and it's
worth noting that your performance will probably be nothing to write
home about).

I also did some research on terminology that has been used in the
literature. As Jim Gray describes it:

1-safe replication. Transaction is committed when it has been locally
WAL-logged to durable storage.
Group-safe replication. Transaction is committed when WAL has been
received by all remote servers, but not necessarily written to durable
storage.
Group-safe & 1-safe replication. Transaction is committed when it has
been locally WAL-logged to durable storage and WAL has been received
by all remote servers.
2-safe replication. Transaction is committed when it has been written
to durable storage on both local and remote servers.
Very safe replication. As 2-safe, but fails any read-write
transaction if the secondary is down.

(Actually, it appears that "Transaction Processing" Jim Gray and
Andreas Reuter, 1993 uses 2-safe to refer to either 2-safe or
group-safe; the distinction between the two is a subsequent
development. See e.g. Advances in Database Technology-EDBT 2004
by Elisa Bertino)

The term of art for making sure that transactions committed on the
primary are visible on the secondary seems to be "one-copy
serializability" (see, for example, a Google Books search on that
term).

...Robert

Robert Haas wrote:
> On Sat, Dec 13, 2008 at 1:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> We won't call it anything, because we never will or can implement that.
>> See the theory of relativity: the notion of exactly simultaneous events
>>
>
> OK, fine. I'll be more precise. I think we need to reserve the term
> "synchronous replication" for a system where transactions that begin
> on the standby after the transactions has committed on the master see
> the effects of the committed transaction.
>

Wouldn't this be serialized transactions?

I'd like to see proof of some sort that PostgreSQL guarantees that the
instant a 'commit' returns, any transactions already open with the
appropriate transaction isolation level, or any new sessions *will* see
the results of the commit.

I know that most of the time this happens - but what process
synchronization steps occur to *guarantee* that this happens?

> I just googled "synchronous replication" and read through the first
> page of hits. Most of them do not address the question of whether
> synchronous replication can be said to have be completed when WAL has
> been received by the standby not but yet applied. One of the ones
> that does is:
>
> http://code.google.com/p/google-mysql-tools/wiki/SemiSyncReplicationDesign
>
> ...which refers to what we're proposing to call "Synchronous
> Replication" as "Semi-Synchronous Replication" (or 2-safe replication)
> specifically to distinguish it. The other is:
>
> http://www.cnds.jhu.edu/pub/papers/cnds-2002-4.pdf
>
> ...which doesn't specifically examine the issue but seems to take the
> opposite position, namely that the server on which the transaction is
> executed needs to wait only for one server to apply the changes to the
> database (the others need only to know that they need to commit it;
> they don't actually need to have done it). However, that same paper
> refers to two-phase commit as a synchronous replication algorithm, and
> Wikipedia's discussion of two-phase commit:
>
> http://en.wikipedia.org/wiki/Two-phase_commit_protocol
>
> ...clearly implies that the transaction must be applied everywhere
> before it can be said to have committed.
>
> The second page of Google results is mostly a further discussion of
> the MySQL solution, which is mostly described as "semi-synchronous
> replication".
>
> Simon Riggs said upthread that Oracle called this "synchronous redo
> transport". That is obviously much closer to what we are doing than
> "synchronous replication".
>

Two phase commit doesn't imply that the transaction is guaranteed to be
immediately visible. See my previous paragraph. Unless transactions are
locked from starting until they are able to prove that they have the
latest commit (a feat which I'm going to theorize as impossible -
because the moment you wait for a commit, and you begin again, you
really have no guarantee that another commit has not occurred in the
mean time), I think it's clear that two phase commit guarantees that the
commit has taken place, but does *not* guarantee anything about visibility.

It might be a good bet - but guarantee? There is no such guarantee.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

Hi all,

I just wanted to point out a detail that I have not seen mentioned in
this thread (but I might have skipped some messages and I apologize in
advance if this is a duplicate).

What the application is going to see is a failure when the postmaster it
is connected to is going down. If this happen at commit time, I think
that there is no guarantee for the application to know what happened:
1. failure occurred before the request reached postmaster: no instance
committed
2. failure occurred during commit: might be committed on either nodes
3. failure occurred while sending back ack of commit to client: both
instances have committed
But for the client, it will all look the same: an error on commit.

This is just to point out that despite all your efforts, the client
might think that some transactions have failed (error on commit) but
they are actually committed. If you don't put some state in the driver
that is able to check at failover time if the commit operation succeeded
or not, it does not really matter what happens for in-flight
transactions (or in-commit transactions) at failure time. In all cases,
a manual inspection of the database logs will be required.
Actually, if there was a way to query the database about the status of a
particular transaction by providing a cluster-wide unique id, that would
help a lot. I wrote a paper on the issues with database replication at
Sigmod earlier this year (http://infoscience.epfl.ch/record/129042).
Even though it was targeted at middleware replication, I think that some
of it is still relevant for the problem at hand.

Regarding the wording, if experts can't agree, you can be sure that
users won't either. Most of them don't have a clue about the different
flavors of replication. So as long as you state clearly how it behaves
and define all the terms you use that should be fine.

manu

--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu(at)frogthinker(dot)org
Skype: emmanuel_cecchet

Robert Haas wrote:
> The term of art for making sure that transactions committed on the
> primary are visible on the secondary seems to be "one-copy
> serializability" (see, for example, a Google Books search on that
> term).
Not exactly. 1-copy-serializability which is the standard for
multi-master solutions, guarantees that transactions are executed in the
same serializable order at each replica (which means that transactions
can be executed in different order and committed at different times on
different replica as long as a consistent serializable view is presented
to the client).
There are a number of optimizations in that area but in a multi-master
case, replicas rarely commit at the same time. There are interesting
papers on the subject (like Tashkent & Tashkent+ based on Postgres) for
those who want to understand these problems more thoroughly.

Hope this helps,
manu

--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu(at)frogthinker(dot)org
Skype: emmanuel_cecchet

On Sun, 2008-12-14 at 13:31 +0900, Tatsuo Ishii wrote:
> > The point here is that synchronous replication, at least to some
> > people, is going to imply that the user-visible states of the two
> > copies are consistent. To other people, it is going to imply that
> > committed transactions will never be lost even in the event of a
> > catastropic loss of the primary 1 picosecond after the commit is
> > acknowledged. We need to choose some word that implies that we are
> > guaranteeing the latter of these two things but not the former.
> > Otherwise, we will have confused users, and terminological confusion
> > when and if we ever implement the former as well.
>
> Right. Before watching this thread, I had thought that the log
> shipping sync replication behaves former (and I had told so to people
> in Japan who are interested in 8.4 development. Of course this is my
> fault, though).
>
> Now I understand the log shipping sync replication does not behave
> same as other "sync replications" such as pgpool and PGCluster (there
> maybe more, but I don't know)

GENERAL COMMENTS, not to anybody in particular:

'Tis but thy name that is my enemy.
...
What's in a name? That which we call a rose
By any other name would smell as sweet.
...

Juliet, from "Romeo and Juliet"

I am truly lost to understand why the *name* "synchronous replication"
causes so much discussion, yet nobody has discussed what they would
actually like the software to *do* (this being a software discussion
list...). AFAICS we can make the software behave like *any* of the
definitions discussed so far.

It is certainly far too early to say what the final exact behaviour will
be and there is no reason at all to pre-suppose that it need only be a
single behaviour. I'm in favour of options, generally, but I would say
that the distinction between some of these options is mostly very fine
and strongly doubt whether people would use them if they existed. *But*
I think we can add them at a later stage of development if requirements
genuinely exist once all the benefits *and* costs are understood.

I would also point out that the distinction made between various
meanings of synchronous is *only* important if Hot Standby is included
as well. And that is closely linked to the replication feature, which we
really need to complete first. We have much to do yet.

So let's please end the name debate there and think about software.

...

We can make the reply to a commit message when any of the following
events have occurred

1. We sent the message to standby
2. We received the message on standby
3. We wrote the WAL to the WAL file
4. We fsync'd the WAL file
5. We CRC checked the WAL commit record
6. We applied the WAL commit record

Now you might think from what people have said that having synchronised
contents on both primary and standby is the only way to achieve exactly
the same results to queries on both nodes. Another way is to utilise a
snapshot taken on the primary and simply wait until the standby catches
up with that snapshot's LSN. So there is more than one way of achieving
a particular result and it is not dependent upon the exact
synchronisation we employ at commit time.

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

Simon Riggs wrote:
> I am truly lost to understand why the *name* "synchronous replication"
> causes so much discussion, yet nobody has discussed what they would
> actually like the software to *do* (this being a software discussion
> list...). AFAICS we can make the software behave like *any* of the
> definitions discussed so far.
>

I think people have talked about 'like' in the context of user
expectations. That is, there seems to exist a set of people (probably
those who've never worked with a multi-replica solution before) who
expect that once commit completes on one server, they can query any
other master or slave and be guaranteed visibility of the transaction
they just committed. These people may theoretically change their
decision to not use Postgres-R, or at least change their approach to how
they work with Postgres-R, if the name was in some way more intuitive to
them in terms of what is actually being provided.

"Synchronous replication" itself says only details about replication, it
does not say anything about visibility, so to some degree, people are
focusing on the wrong term as the problem. Even if it says "asynchronous
replication" - not sure that I care either way - this doesn't improve
the understanding for the casual user of what is happening behind the
scenes. Neither synchronous nor asynchronous guarantees that the change
will be immediately visible from other nodes after I type 'commit;'.
Asynchronous might err on the side of not immediately visible, where
synchronous might (incorrectly) imply immediate visibility, but it's not
an accurate guarantee to provide.

Synchronous does not guarantee visibility immediately after. Some
indefinite but usually short time must normally pass from when my
'commit;' completes until when the shared memory visible to my process
"sees" the transaction. Multiple replicas with network latency or
reliability issues increases the theoretical minimum size of this window
to something that would be normally encountered as opposed to something
that is normally not encountered.

The only way to guarantee visibility is to ensure that the new
transaction is guaranteed to be visible from a shared memory perspective
on every machine in the pool, and every active backend process. If my
'commit;' is going to wait for this to occur, first, I think this forces
every commit to have numerous network round trips to each machine in the
pool, it forces each machine in the pool to be network accessible and
responsive, it forces all commits to be serialized in the sense of "the
slowest machine in the pool determines the time for my commit to
complete", and I think it implies some sort of inter-process signalling,
or at the very least CPU level signalling about shared memory (in the
case of multiple CPUs).

People such as myself think that a visibility guarantee is unreasonable
and certain to cause scalability or reliability problems. So, my 'like'
is an efficient multi-master solution where if I put 10 machines in the
pool, I expect my normal query/commit loads to approach 10X as fast. My
like prefers scalability over guarantees that may be difficult to
provide, and probably are not provided today even in a single server
scenario.

> It is certainly far too early to say what the final exact behaviour will
> be and there is no reason at all to pre-suppose that it need only be a
> single behaviour. I'm in favour of options, generally, but I would say
> that the distinction between some of these options is mostly very fine
> and strongly doubt whether people would use them if they existed. *But*
> I think we can add them at a later stage of development if requirements
> genuinely exist once all the benefits *and* costs are understood.
>

The above 'commit;' behaviour difference - whether it completes when the
commit is permanent (it definitely will be applied for certain to all
replicas - it just may take time to apply to all replicas), or when the
commit has actually taken effect (two-phase commit on all replicas - and
both phases have completed on all replicas - what happens if second
phase commit fails on one or more servers?), or when the commit is
guaranteed to be visible from all existing and new sessionss (two-phase
commit plus additional signalling required?) might be such an option.

I'm doubtful, though - as the difference in implementation between the
first and second is pretty significant.

I'm curious about your suggestion to direct queries that need the latest
snapshot to the 'primary'. I might have misunderstood it - but it seems
that the expectation from some is that *all* sessions see the latest
snapshot, so would this not imply that all sessions would be redirect to
the 'primary'? I don't think it is reasonable myself, but I might be
misunderstanding something...

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

> We can make the reply to a commit message when any of the following
> events have occurred
>
> 1. We sent the message to standby
> 2. We received the message on standby
> 3. We wrote the WAL to the WAL file
> 4. We fsync'd the WAL file
> 5. We CRC checked the WAL commit record
> 6. We applied the WAL commit record

Also

0. The same time we would have done so if replication had not been
configured at all.

I think the basic problem here is that we can talk about "asynchronous
replication" and "synchronous replication" but there are n>2
possible/useful behaviors (I would guess principally 0, 2, 4, and 6,
but YMMV). So we're going to need some way to clarify what we mean.

BTW, in case my previous emails on this topic might have given someone
the contrary impression, I'm not really that worked up about this
either. Interesting? Yes. Have opinions? Yes. Lie awake nights
worrying about it? Nope. :-)

...Robert

Mark Mielke wrote:
> Forget replication - even for the exact same server - I don't expect
> that if I commit from one session, I will be able to see the change
> immediately from my other session or a new session that I just opened.
> Perhaps this is often stable to rely on this, and it is useful for the
> database server to minimize the window during which the commit becomes
> visible to others, but I think it's a false expectation from the start
> that it absolutely will be immediately visible to another session. I'm
> thinking of situations where some part of the table is in cache. The
> only way the commit can communicate that the new transaction is
> available is by during communication between the processes or threads,
> or between the multiple CPUs on the machine. Do I want every commit to
> force each session to become fully in alignment before my commit
> completes? Does PostgreSQL make this guarantee today? I bet it doesn't
> if you look far enough into the guts. It might be very fast - I don't
> think it is infinitely fast.

FYI: I haven't been able to prove this. Multiple sessions running on my
dual-core CPU seem to be able to see the latest commits before they
begin executing. Am I wrong about this? Does PostgreSQL provide a
intentional guarantee that a commit from one session that completes
immediately followed by a query from another session will always find
the commit effect visible (provide the transaction isolation level
doesn't get in the way)? Or is the machine and algorithms just fast
enough that by the time it executes the query (up to 1 ms later) the
commit is always visible in practice?

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

Mark Mielke wrote:
> Mark Mielke wrote:
>> Forget replication - even for the exact same server - I don't expect
>> that if I commit from one session, I will be able to see the change
>> immediately from my other session or a new session that I just opened.
>> Perhaps this is often stable to rely on this, and it is useful for the
>> database server to minimize the window during which the commit becomes
>> visible to others, but I think it's a false expectation from the start
>> that it absolutely will be immediately visible to another session. I'm
>> thinking of situations where some part of the table is in cache. The
>> only way the commit can communicate that the new transaction is
>> available is by during communication between the processes or threads,
>> or between the multiple CPUs on the machine. Do I want every commit to
>> force each session to become fully in alignment before my commit
>> completes? Does PostgreSQL make this guarantee today? I bet it doesn't
>> if you look far enough into the guts. It might be very fast - I don't
>> think it is infinitely fast.
>
> FYI: I haven't been able to prove this. Multiple sessions running on my
> dual-core CPU seem to be able to see the latest commits before they
> begin executing. Am I wrong about this? Does PostgreSQL provide a
> intentional guarantee that a commit from one session that completes
> immediately followed by a query from another session will always find
> the commit effect visible (provide the transaction isolation level
> doesn't get in the way)?

Yes. PostgreSQL does guarantee that, and I would expect any other DBMS
to do the same.

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 14 déc. 08 à 16:48, Simon Riggs a écrit :
> I am truly lost to understand why the *name* "synchronous replication"
> causes so much discussion, yet nobody has discussed what they would
> actually like the software to *do* (this being a software discussion
> list...). AFAICS we can make the software behave like *any* of the
> definitions discussed so far.