Thread: Warm standby questions

Warm standby questions

From
"David F. Skoll"
Date:
Hi,

I'm running PostgreSQL 8.3.  Suppose I have master server A shipping logs
to backup server B.  At some time in the past, I did a full backup from A
to B, and now B is running in recovery mode, happily consuming WALs.

Q1.  If I stop and restart master server A gracefully, do I need to do
anything to B (assuming that it doesn't fail over... we'll assume I did
something special to the standby script to tell B not to fail over
during planned downtime of A.)  Can B continue happily consuming WALs,
or is another full backup required?

Q2.  If I stop and restart backup server B while master server A continues
to run, will B continue eating WALs from where it left off?  Or do we need
another full backup?  (We'll assume WAL shipping continues successfully during
the time B is down: I'm only stopping/restarting PostgreSQL, not the entire
machine.)

Regards,

David.

Re: Warm standby questions

From
Michael Graziano
Date:
On Oct 13, 2009, at 1:17 PM, David F. Skoll wrote:
> Hi,
>
> I'm running PostgreSQL 8.3.  Suppose I have master server A shipping
> logs
> to backup server B.  At some time in the past, I did a full backup
> from A
> to B, and now B is running in recovery mode, happily consuming WALs.

I'm running 8.4, but the following should be applicable.


> Q1.  If I stop and restart master server A gracefully, do I need to do
> anything to B (assuming that it doesn't fail over... we'll assume I
> did
> something special to the standby script to tell B not to fail over
> during planned downtime of A.)  Can B continue happily consuming WALs,
> or is another full backup required?

B can continue consuming WALs when A comes back up, provided it didn't
fail over and make itself into a master as you noted above.  If you're
using the pg_standby script you can set it up so the slave will just
wait around checking for the next segment until the master comes back
up & use the trigger file to make failover happen (via an external
process).


> Q2.  If I stop and restart backup server B while master server A
> continues
> to run, will B continue eating WALs from where it left off?  Or do
> we need
> another full backup?  (We'll assume WAL shipping continues
> successfully during
> the time B is down: I'm only stopping/restarting PostgreSQL, not the
> entire
> machine.)

No need for another backup -- the slave will pick up and run through
the WALs that have accumulated as quickly as it can when it comes back
up.  (This is how I do backups at the moment: One of my slaves shuts
down, our backup software grabs the data directory, then the slave
starts up again and chews through the logs that accumulated while it
was stopped).

The caveat is you need enough log segments available for recovery to
catch itself up, which shouldn't be a problem if your slave is only
stopped for a brief period.

-MG


Re: Warm standby questions

From
Simon Riggs
Date:
On Tue, 2009-10-13 at 13:17 -0400, David F. Skoll wrote:

> Q2.  If I stop and restart backup server B while master server A continues
> to run, will B continue eating WALs from where it left off?  Or do we need
> another full backup?  (We'll assume WAL shipping continues successfully during
> the time B is down: I'm only stopping/restarting PostgreSQL, not the entire
> machine.)

Recovery is restartable. Not quite from where it left off, but near
enough that it won't take too long to get back to where it left off.

--
 Simon Riggs           www.2ndQuadrant.com


Re: Warm standby questions

From
Michael Graziano
Date:
> A quick question for you on this note, since you seem to actually be
> doing
> it.
>
> In the case he suggested below, does B delete the WAL files once
> consumed,
> do I do that with an external monitor, or do they stay there forever
> until I
> run out of space?

The slave/standby server deletes the logs from pg_xlog when it's done
replaying them (so you need to keep the ones needed for recovery
around locally so you can do a failover - I discovered this the hard
way in our dev environment :).

If you're using the pg_standby tool (under contrib/ in the Pg source)
the directory it picks the logs up from isn't touched by default so it
will accumulate logs until the disk fills, but there are parameters to
have it remove the segments it doesn't need anymore to keep your disk
from filling up.


My environment is a little strange (at least I never read about one
set up like this before I built it) and has some associated oddities
in the old segment cleanup process - All of my slaves mount an archive
partition on the master (read-only) and every log file is "picked up"
twice: Once to copy it locally, and again to put it where Postgres
wants it for replay/recovery.
every slave to bring up a new master.  If there's interest I'll post a
writeup on my blog rather than rambling in an email :)


-MG


> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Michael
> Graziano
> Sent: Tuesday, October 13, 2009 12:45 PM
> To: David F. Skoll
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Warm standby questions
>
> On Oct 13, 2009, at 1:17 PM, David F. Skoll wrote:
>> Hi,
>>
>> I'm running PostgreSQL 8.3.  Suppose I have master server A shipping
>> logs
>> to backup server B.  At some time in the past, I did a full backup
>> from A
>> to B, and now B is running in recovery mode, happily consuming WALs.
>
> I'm running 8.4, but the following should be applicable.
>
>
>> Q1.  If I stop and restart master server A gracefully, do I need to
>> do
>> anything to B (assuming that it doesn't fail over... we'll assume I
>> did
>> something special to the standby script to tell B not to fail over
>> during planned downtime of A.)  Can B continue happily consuming
>> WALs,
>> or is another full backup required?
>
> B can continue consuming WALs when A comes back up, provided it didn't
> fail over and make itself into a master as you noted above.  If you're
> using the pg_standby script you can set it up so the slave will just
> wait around checking for the next segment until the master comes back
> up & use the trigger file to make failover happen (via an external
> process).
>
>
>> Q2.  If I stop and restart backup server B while master server A
>> continues
>> to run, will B continue eating WALs from where it left off?  Or do
>> we need
>> another full backup?  (We'll assume WAL shipping continues
>> successfully during
>> the time B is down: I'm only stopping/restarting PostgreSQL, not the
>> entire
>> machine.)
>
> No need for another backup -- the slave will pick up and run through
> the WALs that have accumulated as quickly as it can when it comes back
> up.  (This is how I do backups at the moment: One of my slaves shuts
> down, our backup software grabs the data directory, then the slave
> starts up again and chews through the logs that accumulated while it
> was stopped).
>
> The caveat is you need enough log segments available for recovery to
> catch itself up, which shouldn't be a problem if your slave is only
> stopped for a brief period.
>
> -MG
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>


Re: Warm standby questions

From
Michael Graziano
Date:
Insufficient coffee:

> My environment is a little strange (at least I never read about one
> set up like this before I built it) and has some associated oddities
> in the old segment cleanup process - All of my slaves mount an
> archive partition on the master (read-only) and every log file is
> "picked up" twice: Once to copy it locally, and again to put it
> where Postgres wants it for replay/recovery.

> every slave to bring up a new master.  If there's interest I'll post
> a writeup on my blog rather than rambling in an email :)
^^ Should say "That ensures that every slave has the segments I need
to bring it up a new master."

(I swear it made sense when I was typing it! Now I migrate to the
coffee machine...)