Re: archived WALL files question - Mailing list pgsql-admin

Kevin,

Thank you very much. I understand the multiple base backups are only to reduce the amount of time it could take to
actuallyrestore or reply all the LOGS. 
Because let's suppose; I create a base backup today 19/04/2010 then one year later, I need to use that base backup, I
willhave to play one year worth of logs. 

It is an interesting setup you have there, but you must have loads of space for all of those backups.
How long does it take for base backup of one of your busy DBs?

Thank you Very much

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://www.grant.co.uk/

Grant Instruments (Cambridge) Ltd

Company registered in England, registration number 658133

Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK

-----Original Message-----


From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: 16 April 2010 18:16
To: Frederiko Costa; Renato Oliveira; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] archived WALL files question

Renato Oliveira wrote:

> I thought the base backup was only necessary once. For example
> once you have done your first base backup, that is it, all you
> need is to replay the logs and backup the logs.  What would be
> the reason(s) for you to do weekly base backups?

There are a few reasons, many of which are probably not applicable
to most environments.

(1)  Most of our source databases are spread around the state --
some over 300 miles away (500 km for those with sane units of
measure).  Management has mandated that we must have backups in our
central location which have been restored to confirm usability, as
well as a copy of the backup files in the source locations.  We
don't have equipment at the source locations to keep a warm standby
running, so a recovery there would need to replay all WAL files
since the base backup.  A weekly cycle for base backups keeps the
set of WAL files we need to keep relatively small and allows
relatively rapid recovery.

(2)  Because of the large number of database clusters being backup
up (about 100), it is not feasible to actually have warm standbys
for all of them which we can just switch to in case of emergency.
We use one rather largish machine to host a all the "warm standby"
instances, just to make sure that the WAL files are applying without
error to the base backups.  We keep three machines prepped and ready
to go for emergencies, but we have to do the whole PITR recovery
from the base backup to get a production-ready copy.  Again, the
shorter the time from the base backup, the fewer WAL files to apply,
and the sooner we're ready to go.

(3)  We're mandated to keep monthly archival "snapshot" backups, so
we just grab the first weekly base backup of each month, with the
minimum set of WAL files needed to get it running (determined by the
.backup file).

(4)  A fair amount of paranoia.  If any subtle bugs affecting corner
cases were ever to creep into the software, the affects would be
minimized by using a fresher base backup and fewer WAL files.

I have heard of people running warm standbys for months without
getting a fresh base, and for many users that may be the best
option.  Our situation is pretty unique -- although when I say that,
people are usually quick to point out that so is everyone else's.
;-)

-Kevin



-----Original Message-----


P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named
recipients(s).If you are not the named recipient please notify the sender immediately and do not disclose the contents
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks before opening the attachment(s). 

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
http://www.grant.co.uk/Support/openxml.html