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