General queries regarding backup

From: Deepak Bala <deepak(dot)bala(dot)x(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: General queries regarding backup
Date: 2009-07-22 06:11:09
Message-ID: e7de7c470907212311g79ea6433tbd4a14a1704f9ed1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Browse pgsql-admin by date

  From Date Subject
Next Message mitra nazemian 2009-07-22 08:23:13 Re: implement ldap authentication in PostgreSQL
Previous Message Deepak Bala 2009-07-22 05:25:36 General queries regarding backup