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 backupThis 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. -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
