Re: General queries regarding backup

Anyone ?

On Wed, Jul 22, 2009 at 10:55 AM, Deepak Bala<deepak(dot)bala(dot)x(at)gmail(dot)com> 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
>
> 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 ?
>
> 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 ?
>
> Thanks for reading through the message and for your time.
>