Re: General queries regarding backup

Thanks for your replies Peter.

On Wed, Jul 29, 2009 at 12:45 PM, Peter Eisentraut<peter_e@gmx.net> wrote:
> On Wednesday 22 July 2009 08:25:36 Deepak Bala wrote:
>> Hi everyone,
>>
>> I have some queries regarding the PITR backup procedure on Postgres
>> 8.3. Here are the steps I follow for backup
>>
>> 1. I set up WAL archiving and checked that this is working.
>> 2. Execute SELECT pg_start_backup('label')
>> 3. Zip the entire data directory excluding the pg_xlog directory.
>> 4. At this point the WAL archive directory contains a .backup file
>> which looks something like this
>> 00000001000000000000000B.00000020.backup. This recognizes that the WAL
>> file 00000001000000000000000B (and all subsequent WAL files) must be
>> present when we restore the database.
>> 5. The contents of the .backup file looks something like this
>> START WAL LOCATION: 0/B000020 (file 00000001000000000000000B)
>> STOP WAL LOCATION: 0/C000000 (file 00000001000000000000000C)
>> CHECKPOINT LOCATION: 0/B000020
>> START TIME: 2009-07-22 04:02:25 UTC
>> LABEL: Something
>> STOP TIME: 2009-07-22 04:02:39 UTC
>> 6. Execute the SELECT pg_stop_backup() command to stop the backup.
>>
>> I have a few questions about this.
>>
>> 1. I was not able to find the file 00000001000000000000000C in the WAL
>> archive location after taking the base backup. Is that normal ? The
>> file 00000001000000000000000B exists and is the last WAL file. The
>> server was stopped after taking the base backup
>
> This is normal, although arguably not desirable.  In PostgreSQL 8.4, this was
> changed so that pg_stop_backup() waits until the ...000C file in your case is
> in the archive.  So that is what you want.
>
>> 2. When I do a restore, postgres will have a look at the
>> restore_command from my recover.conf to look for all WAL files from
>> 00000001000000000000000B right ? Is it ok if it does not find
>> 00000001000000000000000C ?
>
> Yes.  Recovery will stop when it runs out of files to restore.
>
>> 3. Lets assume that after taking the base backup the WAL files with
>> the suffix 0C 0D 0E etc were generated. What happens if the entire
>> hard disk crashes but I still have the data directory archived along
>> with the WAL file 00000001000000000000000B ? It means that all the
>> data that was in the DB till the base backup can be recovered but any
>> subsequent data that was updated / inserted will be lost. Am I right
>> when I say that ?
>
> In this scenario you would have to restore your *previous* base backup,
> because the current base backup wouldn't be usuable, as it requires that the
> ...000C file be present.
>
> It's always a good idea to have two base backups around, if you can afford the
> space, in case something goes wrong during or around the time you take the
> next base backup.
>
>