Kevin, Thank you very much. I understand the multiple base backups are only to reduce the amount of time it could take to actually restore 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 will have 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@xxxxxxxxxxx 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@xxxxxxxxxxxx] Sent: 16 April 2010 18:16 To: Frederiko Costa; Renato Oliveira; pgsql-admin@xxxxxxxxxxxxxx Subject: Re: 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 to another person or take copies. VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
