Re: General queries regarding backup

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.