Re: [GENERAL] recovery_target_time ignored or recoveryalwaysrecovers to end of WAL

2007-07-02 Thread Simon Riggs
On Mon, 2007-07-02 at 09:21 -0700, Jason L. Buberel wrote:

 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/0001000F007C:
 Bogus page magic number D05E at offset 0
 invalid record length at F/7C1C
 ../../../archive_logs/0001000F007C.00550700.backup:
 Partial page of 241 bytes ignored
 ../../../archive_logs/0001000F007D:
 Bogus page magic number D05E at offset 0
 invalid record length at F/7D1C
 ../../../archive_logs/0001000F007D.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?

For now, remove these lines from xlogdump.c, l.82-86
  if (((XLogPageHeader) pageBuffer)-xlp_magic != XLOG_PAGE_MAGIC)
  {
printf(Bogus page magic number %04X at offset %X\n,
   ((XLogPageHeader) pageBuffer)-xlp_magic, logPageOff);
  }

The program is unfortunately release specific, which is not very useful
for you now. D05E is the correct magic number for 8.2.4.

I'll update that program once we have the main software done for 8.3. I
was hoping that Diogo would continue to support it.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [GENERAL] recovery_target_time ignored or recoveryalwaysrecovers to end of WAL

2007-07-02 Thread Simon Riggs
On Mon, 2007-07-02 at 11:06 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  On Sun, 2007-07-01 at 21:41 -0700, Jason L. Buberel wrote:
  I am trying to learn/practice the administrative steps that would need 
  to be taken in a 'fat finger' scenario, and I am running into problems. 
 
  Your example transactions are so large that going back 15 minutes is not
  enough. You'll need to go back further.
  recovery_target_time can only stop on a COMMIT or ABORT record. This is
  because it makes no sense to recover half a transaction, only whole
  transactions have meaning for recovery. So if the transactions are very
  large, you need to go back further.
 
 No, that doesn't explain it.  As long as he set the stop time before the
 commit of the unwanted transaction, it should do what he's expecting.
 It might uselessly replay a lot of the actions of a long-running
 transaction, but it will stop before the COMMIT xlog record when it
 reaches it, and thus the transaction will not be committed.
 
 What's actually happening according to the log output is that it's
 running all the way to the end of WAL.  I can't really think of an
 explanation for that other than a mistake in choosing the stop time,
 ie, it's later than the commit of the unwanted transaction.  Or maybe
 the WAL file is a stale copy that doesn't even contain the unwanted
 commit?
 
 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.

There's a patch awaiting review that adds the time of the last committed
transaction into the LOG output. That should help in cases like this.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] recovery_target_time ignored or recoveryalwaysrecovers to end of WAL

2007-07-02 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes:
 On Mon, 2007-07-02 at 09:21 -0700, Jason L. Buberel wrote:
 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:
 Bogus page magic number D05E at offset 0

 For now, remove these lines from xlogdump.c, l.82-86
   if (((XLogPageHeader) pageBuffer)-xlp_magic != XLOG_PAGE_MAGIC)

I don't think that's a very good solution; the reason the magic number
changed is that some of the record formats changed.  Jason needs a copy
that's actually appropriate to 8.2.

Howver there is something odd here, because the value of XLOG_PAGE_MAGIC
comes from src/include/access/xlog_internal.h and not from the text of
xlogdump.c itself.  What it looks like to me is that Jason compiled it
against the wrong set of Postgres header files.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match