Re: Point in Time Recovery

On Thu, 2004-07-15 at 10:47, Mark Kirkwood wrote:
> I tried what I thought was a straightforward scenario, and seem to have
> broken it :-(
>
> Here is the little tale
>
> 1) initdb
> 2) set archive_mode and archive_dest in postgresql.conf
> 3) startup
> 4) create database called 'test'
> 5) connect to 'test' and type 'checkpoint'
> 6) backup PGDATA using 'tar -zcvf'
> 7) create tables in 'test' and add data using COPY (exactly 2 logs worth)
> 8) shutdown and remove PGDATA
> 9) recover using 'tar -zxvf'
> 10) copy recovery.conf into PGDATA
> 11) startup
>
> This is what I get :
>
> LOG: database system was interrupted at 2004-07-15 21:24:04 NZST
> LOG: recovery command file found...
> LOG: restore_program = cp %s/%s %s
> LOG: recovery_target_inclusive = true
> LOG: recovery_debug_log = true
> LOG: starting archive recovery
> LOG: restored log file "0000000000000000" from archive
> LOG: checkpoint record is at 0/A48054
> LOG: redo record is at 0/A48054; undo record is at 0/0; shutdown FALSE
> LOG: next transaction ID: 496; next OID: 25419
> LOG: database system was not properly shut down; automatic recovery in
> progress
> LOG: redo starts at 0/A48094
> LOG: restored log file "0000000000000001" from archive
> LOG: record with zero length at 0/1FFFFE0
> LOG: redo done at 0/1FFFF30
> LOG: restored log file "0000000000000001" from archive
> LOG: restored log file "0000000000000001" from archive
> PANIC: concurrent transaction log activity while database system is
> shutting down
> LOG: startup process (PID 13492) was terminated by signal 6
> LOG: aborting startup due to startup process failure
>
> The concurrent access is a bit of a puzzle, as this is my home machine
> (i.e. I am *sure* noone else is connected!)

First, thanks for sticking with it to test this.

I've not received such a message myself - this is interesting.

Is it possible to copy that directory to one side and re-run the test?
Add another parameter in postgresql.conf called "archive_debug = true"
Does it happen identically the second time?

What time difference was there between steps 5 and 6? I think I can here
Andreas saying "told you".... I'm thinking the backup might be somehow
corrupted because the checkpoint occurred during the backup. Hmmm...

Could you also post me the recovery.log file? (don't post to list)

Thanks, Simon Riggs