Thread: Warm-standby robustness question
Hi, We have two PostgreSQL 8.2 database servers: A master and a warm-standby server. We plan on making an initial backup of the master onto the standby and then use log-shipping with "real-time" WAL-file processing as described in http://www.postgresql.org/docs/8.2/static/warm-standby.html My question is this: If the master database is fairly busy, gets VACUUMed once a day, etc. can we expect the warm standby server to work correctly after days/weeks/months/years of log shipping, or should we periodically take new base backups? How long would you go between base backups? Or is one initial copy really sufficient with WAL-shipping-and-consuming working perfectly thereafter? Regards, David.
"David F. Skoll" <dfs@roaringpenguin.com> writes:
> My question is this: If the master database is fairly busy, gets
> VACUUMed once a day, etc. can we expect the warm standby server
> to work correctly after days/weeks/months/years of log shipping,
> or should we periodically take new base backups?
I don't think the time period is at issue. Log-shipping should keep the
slave a perfect replica of the master (if it doesn't, we have problems
anyway). The operational question you need to ask yourself is: if
you haven't swapped to the slave lately, how do you know it will work
when you need it to?
The current backup/restore docs suggest as best practice that you
intentionally swap master and slave periodically, ie, fail over
to the slave and then re-initialize the master as a new slave.
This provides a periodic test that your fail-over mechanisms actually
work, and as a bonus gives you a chance for a maintenance window
on the ex-master before it's brought up as new slave.
regards, tom lane
>>> On Tue, Dec 18, 2007 at 12:55 PM, in message <3149.1198004157@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "David F. Skoll" <dfs@roaringpenguin.com> writes: >> My question is this: If the master database is fairly busy, gets >> VACUUMed once a day, etc. can we expect the warm standby server >> to work correctly after days/weeks/months/years of log shipping, >> or should we periodically take new base backups? > > I don't think the time period is at issue. Log-shipping should keep the > slave a perfect replica of the master (if it doesn't, we have problems > anyway). Except for hint bits. This becomes more of a post-recovery performance issue as the base backup ages, since they are included in base backups, but not in WAL files. http://archives.postgresql.org/pgsql-performance/2007-12/msg00203.php > The operational question you need to ask yourself is: if > you haven't swapped to the slave lately, how do you know it will work > when you need it to? Absolutely. Nobody should ever assume they have a working backup system without periodic tests that the backups can actually be used to create a working system. Ever. -Kevin
Kevin Grittner wrote: > >>> On Tue, Dec 18, 2007 at 12:55 PM, in message <3149.1198004157@sss.pgh.pa.us>, > Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "David F. Skoll" <dfs@roaringpenguin.com> writes: > >> My question is this: If the master database is fairly busy, gets > >> VACUUMed once a day, etc. can we expect the warm standby server > >> to work correctly after days/weeks/months/years of log shipping, > >> or should we periodically take new base backups? > > > > I don't think the time period is at issue. Log-shipping should keep the > > slave a perfect replica of the master (if it doesn't, we have problems > > anyway). > > Except for hint bits. This becomes more of a post-recovery > performance issue as the base backup ages, since they are included > in base backups, but not in WAL files. But hint bits should be replicated on the next full-page-write anyhow, no? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support