8.3.8 question about backup/recovery behavior

From: Mike Broers <mbroers(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: 8.3.8 question about backup/recovery behavior
Date: 2010-09-15 14:34:46
Message-ID: AANLkTinVF0JDtTQXhch+5Dg=bdE+eVk4pDrbe=mAko3R@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We take nightly backups using the start backup, copying the data directory
and archived logs, then stop backup method.

Today I tested the recoverability of the backup by mounting this backup
directory on a different server, copying the 3 hours of transactions logs
from after last nights backup up until this morning to the different server,
and starting up postgres there from the backup, and I want to verify what I
experienced was normal behavior:

My first attempt was to see if I could start the postmaster without setting
up recovery.conf. It worked to my surprise, pg looked for the pg_xlog
folder, and in the log noted the last transaction was at the time of the
last available transaction log (915) and opened the database for
connections. I guess I was surprised that it automatically rolled forward
through all the logs up to the last one, and opened instead of just opening
the database at the time of the stop backup command which was about 3 hours
prior to the last available transaction log.

Here is what I saw in the log:
2010-09-14 10:48:43.626 CDT <@> [6848] LOG: 00000: database system was not
properly shut down; automatic recovery in progress
2010-09-14 10:48:43.626 CDT <@> [6848] LOCATION: StartupXLOG, xlog.c:5006
2010-09-14 10:48:43.679 CDT <@> [6848] LOG: 00000: redo starts at
15A/4E01A5D8
2010-09-14 10:48:43.679 CDT <@> [6848] LOCATION: StartupXLOG, xlog.c:5068
2010-09-14 10:49:01.059 CDT <@> [6848] LOG: 58P01: could not open file
"pg_xlog/000000010000015A000000C6" (log file 346, segment 198): No such file
or directory
2010-09-14 10:49:01.059 CDT <@> [6848] LOCATION: XLogFileRead, xlog.c:2365
2010-09-14 10:49:01.059 CDT <@> [6848] LOG: 00000: redo done at
15A/C5FFF1F8
2010-09-14 10:49:01.059 CDT <@> [6848] LOCATION: StartupXLOG, xlog.c:5138
2010-09-14 10:49:01.097 CDT <@> [6848] LOG: 00000: last completed
transaction was at log time 2010-09-14 09:15:07.450314-05
2010-09-14 10:49:01.097 CDT <@> [6848] LOCATION: StartupXLOG, xlog.c:5142

The database was open, I could log in, but I couldnt determine a way to see
if the database was brought up to 9:15am or some earlier time.

My second attempt was to shut down, set the recovery.conf, and start the
database in recovery. This worked as well as expected. Can anyone verify
that the first attempt is valid or expected behavior? I guess I'm so used
to oracles very specific and strict backup/recovery methods that this
shocked me a little.

Thanks
Mike

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-09-15 14:46:03 Re: Replacing characters in a string
Previous Message Gary Fu 2010-09-15 14:09:14 Re: select sql slow inside function