Re: How to avoid base backup in automated failover

Hi all,

Recently I was writing an application to implement automated failover with
env: Two 2008 R2 servers, Network area storage, asynchronous replication,
WAL archive on primary enabled.

Is there any way to avoid starting standby server always from base backup in
automated failover. I see the database is growing huge. I can't keep doing
base backup every day.

Please suggest solution

Regards,
Reddy

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Mon, Jun 4, 2012 at 9:14 AM, chinnaobi <chinnaobi(at)gmail(dot)com> wrote:
> Recently I was writing an application to implement automated failover with
> env: Two 2008 R2 servers, Network area storage, asynchronous replication,
> WAL archive on primary enabled.
>
> Is there any way to avoid starting standby server always from base backup in
> automated failover. I see the database is growing huge. I can't keep doing
> base backup every day.
>
> Please suggest solution

The usual solution is to configure the standby as a warm or hot
standby, so that logs are continuously replayed there. Then if the
master dies, you only have to wait for replication to catch up before
promoting.

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

Hey Haas,What does the standby server have to wait for replication to catch
up before promoting ?? Is there any parameter to configure this ??Few more
questions on this part 1. How could we ensure the standby has received all
transactions sent by primary till the point primary server is dead. (Meaning
the dead primary and standby server are exactly same, so that the dead
primary comes back it can be turned to standby without any issues).2. When
the dead primary is turned to standby the streaming is not happening due to
current_wal_location is ahead in the standby server is ahead of
wal_sent_location. In this case how can I start streaming without taking a
fresh base backup ??3. When the dead primary comes back the DB still accepts
data and it goes to out of sync with the current primary and streaming won't
start. Is there any solution for this case ??Reddy.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728517.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Hey Haas,

What does the standby server have to wait for replication to catch up before
promoting ?? Is there any parameter to configure this ??

Few more questions on this part

1. How could we ensure the standby has received all transactions sent by
primary till the point primary server is dead. (Meaning the dead primary and
standby server are exactly same, so that the dead primary comes back it can
be turned to standby without any issues).

2. When the dead primary is turned to standby the streaming is not happening
due to current_wal_location is ahead in the standby server is ahead of
wal_sent_location. In this case how can I start streaming without taking a
fresh base backup ??

3. When the dead primary comes back the DB still accepts data and it goes to
out of sync with the current primary and streaming won't start. Is there any
solution for this case ??

Reddy.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728518.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Wednesday, October 17, 2012 11:22 AM chinnaobi wrote:
> Hey Haas,
>
> What does the standby server have to wait for replication to catch up
> before
> promoting ?? Is there any parameter to configure this ??
>
> Few more questions on this part
>
> 1. How could we ensure the standby has received all transactions sent by
> primary till the point primary server is dead. (Meaning the dead primary
> and
> standby server are exactly same, so that the dead primary comes back it
> can
> be turned to standby without any issues).

> 2. When the dead primary is turned to standby the streaming is not
> happening
> due to current_wal_location is ahead in the standby server is ahead of
> wal_sent_location. In this case how can I start streaming without taking
> a
> fresh base backup ??
>
> 3. When the dead primary comes back the DB still accepts data and it
> goes to
> out of sync with the current primary and streaming won't start. Is there
> any
> solution for this case ??

I think points 2 & 3 can be addressed with new feature getting implemented
by Heikki provided standby has received all WAL of primary till the point it
goes dead.

https://commitfest.postgresql.org/action/patch_view?id=933

Hey Amitkapila,

Thank you for the quick reply.

How can implement this patch in windows, because I am using windows 9.1.1
postgreSQL application ??

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728562.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Wednesday, October 17, 2012 3:09 PM chinnaobi wrote:
> Hey Amitkapila,
>
> Thank you for the quick reply.
>
> How can implement this patch in windows, because I am using windows
> 9.1.1
> postgreSQL application ??
>

If the patch serves the feature you require, then once it gets committed
(there are few bugs yet to be resolved), the feature will be available for
windows as well.
About the version, I think it will be available in 9.3 only.
If you are very urgent need of this, may be you can merge in your own copy
of 9.1.1.
However that has its own implications.

With Regards,
Amit Kapila.