Thread: 20110408pg_upgrade_fix and "FATAL: could not access status of transaction..."

Hi,

Lovely start to Saturday morning.  My eyes are misting over with joy.

I'm trying to figure out whether I'm affected (probably) by this beauty
http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix (I missed the ANNOUNCE
advisory, even more joy).

One of my DBs fails at startup:
2011-04-23 09:32:57 SAST:  6377  LOG:  consistent recovery state reached at
2E5/9D87FC90
2011-04-23 09:32:57 SAST:  6377  LOG:  redo starts at 2E5/9C00EF68
2011-04-23 09:32:57 SAST:  6377  FATAL:  could not access status of
transaction 670554347

I don't have the error:
DETAIL: could not open file "pg_clog/####": No such file or directory

...as mentioned on the wiki, hence my question.

I'm using 9.0.4 on linux.  The db is replicated to another machine where I
have made a backup of data/pg_clog before doing anything else.  Sooo, two
questions:

1.  how to proceed with getting db1 back up so I can run the script?
2.  how to proceed with replicated database (db2)? (switch to standalone
(since it's in readonly replication mode) and run upgrade fix script as per
wiki, or leave as-is...)

Running pg in single user with some extra debugging on db1 provides nothing
else of interest:

  DEBUG:  invoking IpcMemoryCreate(size=671121408)
  DEBUG:  removing file "pg_notify/0000"
  DEBUG:  InitPostgres
  DEBUG:  my backend id is 1
  LOG:  database system was interrupted while in recovery at 2011-04-23
08:43:23 SAST
  DEBUG:  checkpoint record is at 2E5/9D87FC38
  DEBUG:  redo record is at 2E5/9C00EF68; shutdown FALSE
  DEBUG:  next transaction ID: 0/670559634; next OID: 758013015
  DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
  DEBUG:  oldest unfrozen transaction ID: 654, in database 1
  DEBUG:  transaction ID wrap limit is 2147484301, limited by database with OID 1
  LOG:  database system was not properly shut down; automatic recovery in
progress
  LOG:  consistent recovery state reached at 2E5/9D87FC90
  LOG:  redo starts at 2E5/9C00EF68
  FATAL:  could not access status of transaction 670554347
  DEBUG:  shmem_exit(1): 8 callbacks to make
  DEBUG:  proc_exit(1): 3 callbacks to make
  DEBUG:  exit(1)
  DEBUG:  shmem_exit(-1): 0 callbacks to make
  DEBUG:  proc_exit(-1): 0 callbacks to make

Any suggestions would be welcomed with even more misty-eyed thanks.

Cheers
Henry

On Sat, April 23, 2011 09:56, Henry C. wrote:
> 1.  how to proceed with getting db1 back up so I can run the script?
> 2.  how to proceed with replicated database (db2)? (switch to standalone
> (since it's in readonly replication mode) and run upgrade fix script as per
> wiki, or leave as-is...)

Ran the pg_upgrade_fix script on db2 successfully after triggering failover.

Still wondering what to about db1 (other than trashing it and restoring from
backup or from db2 via rsync/replication).

I managed to resolve this issue.  Using strace

Resolved the startup problem by identifying which pg_clog file it was failing
on with:

strace postgres --single -D 9.0/data

Then grabbed that file from the replication slave.

Cheers
h