Re: recovery_target_time ignored or recovery always recovers to end of WAL

Some more bits on this:

And playing with the date format does not seem to change the outcome
(the db is always recovered to the most current state). In this case, I
removed the timezone designation 'PDT' from my timestamp, and the db
properly figured out that it is running in GMT-7 (pacific) time (see
syslog ouptput below).

What worries me is the 'record with zero length', combined with my
issues (in previous email) with the xlogdump not finding the right magic
bits. Perhaps that (or problems related to it) are causing the recovery
process to ignore my PITR information leading it to simply recover the
database to the most recent state?

LOG:  database system was shut down at 2007-07-02 10:12:06 PDT
LOG:  starting archive recovery
LOG:  recovery_target_time = 2007-06-29 00:00:00-07
LOG:  restore_command = "cp /pgdata/archive_logs/%f %p"
LOG:  recovery_target_inclusive = false
LOG:  checkpoint record is at F/7E0DDA60
LOG:  redo record is at F/7E0DDA60; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/695227; next OID: 35828734
LOG:  next MultiXactId: 28; next MultiXactOffset: 55
LOG:  automatic recovery in progress
LOG:  record with zero length at F/7E0DDAA8
LOG:  redo is not required
LOG:  archive recovery complete
LOG:  database system is ready

-jason

Jason L. Buberel wrote:
> Harrumph -
>
> I downloaded the latest xlogdump source, and built/installed it
> against my 8.2.4 source tree. When I execute it however, I am informed
> that all of my WAL files (either the 'active' copies in pg_xlog or the
> 'archived' copies in my /pgdata/archive_logs dir) appear to be malformed:
>
> $ /opt/postgres-8.2.4/bin/xlogdump --port 54824 --host 127.0.0.1
> --user postgres  ../../../archive_logs/*
> ../../../archive_logs/000000010000000F0000007C:
> Bogus page magic number D05E at offset 0
> invalid record length at F/7C00001C
> ../../../archive_logs/000000010000000F0000007C.00550700.backup:
> Partial page of 241 bytes ignored
> ../../../archive_logs/000000010000000F0000007D:
> Bogus page magic number D05E at offset 0
> invalid record length at F/7D00001C
> ../../../archive_logs/000000010000000F0000007D.0006C01C.backup:
> Partial page of 241 bytes ignored
>
> Which does not help particularly much :)
>
> I'll keep plugging away at this - perhaps my problem in setting the
> database state to a PITR is related to timezones or timestamp formatting?
>
> -jason
>
> Tom Lane wrote:
>> Jason, if you can't figure it out you might grab xlogviewer
>> http://pgfoundry.org/projects/xlogviewer/
>> and see what it says the timestamps of the commit records in your WAL
>> files are.
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/