Synchronous replication: Promotion of Standby to Primary

Hi,

On Mon, May 4, 2009 at 11:07 PM, K, Niranjan (NSN - IN/Bangalore)
<niranjan.k@nsn.com> wrote:

Hi,

Re-opening the discussion related to triggers to promote standby server.
In the earlier dicussion, there were 2 proposals, Trigger based on file
and trigger based on signals. I think there was no conclusion on this.
http://archives.postgresql.org/pgsql-hackers/2008-12/msg01231.php

According to the proposal, it seems to be better if signals are used as
we can avoid checking the file existance in the loop. Or the other
approach is to use inotify (http://en.wikipedia.org/wiki/Inotify). But
portability to other unix like OS is an issue to look for.

Could you please get back which is being implemented/ considered for
promotion of standby?

I had to choose the trigger file approach last time since the patch
was also using warm-standby in part. But, in 8.5, synch-rep is
going to work without warm-standby. So, we can choose also the
trigger based on signals. In this case,

Since using kill(1) command directly for the trigger signal is troublesome,
we should also provide the wrapper command such as "pg_ctl trigger/promote".

Regards,

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

Hi,

On Thu, May 14, 2009 at 8:16 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

Hi,

On Mon, May 4, 2009 at 11:07 PM, K, Niranjan (NSN - IN/Bangalore)
<niranjan.k@nsn.com> wrote:

Hi,

Re-opening the discussion related to triggers to promote standby server.
In the earlier dicussion, there were 2 proposals, Trigger based on file
and trigger based on signals. I think there was no conclusion on this.
http://archives.postgresql.org/pgsql-hackers/2008-12/msg01231.php

According to the proposal, it seems to be better if signals are used as
we can avoid checking the file existance in the loop. Or the other
approach is to use inotify (http://en.wikipedia.org/wiki/Inotify). But
portability to other unix like OS is an issue to look for.

Could you please get back which is being implemented/ considered for
promotion of standby?

I had to choose the trigger file approach last time since the patch
was also using warm-standby in part. But, in 8.5, synch-rep is
going to work without warm-standby. So, we can choose also the
trigger based on signals. In this case,

In this case, I'm not sure which signal can be used for the trigger.
SIGINT/QUIT/TERM/USR1 are already used in postmaster.
SIGUSR2 is already reserved for children according to the source
comment.

Regards,

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

Hello Fujii,

my name is Harald Kolb, I'm a colleague of Niranjan and I will continue
his community work since he's currently busy with other topics.
We are looking for a fast mechanism to activate the switchover. Therfore
we prefer to use a signal to trigger the standby to become primary,
since in our case the postmaster will be started via fork by a watchdog.
The originally intended solution via SIGTERM should be ok.
In the case the mechanism will be still based on the trigger file
concept, we would require to have in addition a signal to speed up the
trigger file detection, since the periodic check might be not fast
enough. It should be possible to reuse the SIGHUP signal. If it occurs,
the first action should be to check for the existence of the trigger
file. In the case it now exists, the switch to master mode is performed.
Otherwise the usual re-reading of the configuration files is performed.

Are there currently some concrete plannings how the promotion to primary
will be solved in the final solution ?

BR, Harald.

-----Original Message-----
From: ext Fujii Masao [mailto:masao.fujii@gmail.com]
Sent: Thursday, May 14, 2009 1:39 PM
To: K, Niranjan (NSN - IN/Bangalore)
Cc: Kolb, Harald (NSN - DE/Munich); Czichy, Thoralf (NSN - FI/Helsinki);
PostgreSQL-development
Subject: Re: Synchronous replication: Promotion of Standby to Primary

Hi,

On Thu, May 14, 2009 at 8:16 PM, Fujii Masao <masao.fujii@gmail.com>
wrote:

Hi,

On Mon, May 4, 2009 at 11:07 PM, K, Niranjan (NSN - IN/Bangalore)
<niranjan.k@nsn.com> wrote:

Hi,

Re-opening the discussion related to triggers to promote standby

server.

In the earlier dicussion, there were 2 proposals, Trigger based on

file

and trigger based on signals. I think there was no conclusion on

this.

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

According to the proposal, it seems to be better if signals are used

as

we can avoid checking the file existance in the loop. Or the other
approach is to use inotify (http://en.wikipedia.org/wiki/Inotify).

But

portability to other unix like OS is an issue to look for.

Could you please get back which is being implemented/ considered for
promotion of standby?

I had to choose the trigger file approach last time since the patch
was also using warm-standby in part. But, in 8.5, synch-rep is
going to work without warm-standby. So, we can choose also the
trigger based on signals. In this case,

In this case, I'm not sure which signal can be used for the trigger.
SIGINT/QUIT/TERM/USR1 are already used in postmaster.
SIGUSR2 is already reserved for children according to the source
comment.

Regards,

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

Hi,

On Mon, May 25, 2009 at 7:10 PM, Kolb, Harald (NSN - DE/Munich)
<harald.kolb@nsn.com> wrote:

Hello Fujii,

my name is Harald Kolb, I'm a colleague of Niranjan and I will continue
his community work since he's currently busy with other topics.
We are looking for a fast mechanism to activate the switchover. Therfore
we prefer to use a signal to trigger the standby to become primary,
since in our case the postmaster will be started via fork by a watchdog.
The originally intended solution via SIGTERM should be ok.
In the case the mechanism will be still based on the trigger file
concept, we would require to have in addition a signal to speed up the
trigger file detection, since the periodic check might be not fast
enough.

To be honest, I don't think that such quick detection shortens
the failover time so much (probably it would be shortened only
a few ms). Instead, we should reduce the time of checkpoint
which occurs at the end of recovery/failover. It might take a few
minutes to end. So, I'd like to emphasize user-friendliness
rather than the failover time in the choice of the way of activation.
What is your opinion?

Regards,

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

Hi,

On Wed, May 27, 2009 at 3:27 PM, Kolb, Harald (NSN - DE/Munich)
<harald.kolb@nsn.com> wrote:

In our use case it's important to have a short failover time.
So everything what keeps the time low, would be good to have.

Yes. I think that it's a matter of priority.

We already made some failover investigations and got some good results,
in
some cases it took less than 1 second after creating the (finish)
trigger file.
Surely, with some best case conditions like small DB and less
modifications.

What use casees do you have in mind when mentioning a few_minutes
timeframe ?

It's the case where long-term recovery generates a lot of dirty blocks
in buffer pool. It would take a long time to write out all dirty blocks.
Of course, since bgwriter works also during recovery in 8.4, things
might have got better.

Are there plans to improve the final checkpointing ?

IIRC, in last CommitFest, this improvement was proposed by Simon
as a part of his recovery_infrastructure patch, and was postponed
until 8.5. But, I'm not sure if it will be actually proposed again for 8.5.

Regards,

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

On Wed, 2009-05-27 at 21:28 +0900, Fujii Masao wrote:

Hi,

On Wed, May 27, 2009 at 3:27 PM, Kolb, Harald (NSN - DE/Munich)
<harald.kolb@nsn.com> wrote:

In our use case it's important to have a short failover time.
So everything what keeps the time low, would be good to have.

Yes. I think that it's a matter of priority.

Reducing failover time should be the subject of another patch, not
something to be included in synch rep. That will just make patch bigger
and harder to commit.

Fast failover is important and we should eventually do it, even plan it
for next release, but it should not be a priority (ahead of or same as
sync rep).

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

Hi,

On Wed, May 27, 2009 at 10:49 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Wed, 2009-05-27 at 21:28 +0900, Fujii Masao wrote:

Hi,

On Wed, May 27, 2009 at 3:27 PM, Kolb, Harald (NSN - DE/Munich)
<harald.kolb@nsn.com> wrote:

In our use case it's important to have a short failover time.
So everything what keeps the time low, would be good to have.

Yes. I think that it's a matter of priority.

Reducing failover time should be the subject of another patch, not
something to be included in synch rep. That will just make patch bigger
and harder to commit.

Agreed. I'll concentrate on synch rep itself until it's committed.

Regards,

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