Re: ERROR: could not access status of transaction 2495690984

On Tue, 21 Jul 2009, Merlin Moncure wrote:

> One way to suppress this error, but not fix the underlying corruption,
> is to generate clog files that the database thinks should be there.
> Make a 256k file filled with 0x55 and put it where the database is
> expecting.

Here's a list of past messages on this topic I found interesting (I just
ran into this recently):

http://archives.postgresql.org/pgsql-general/2006-12/msg01546.php
http://archives.postgresql.org/pgsql-hackers/2005-01/msg00678.php
http://archives.postgresql.org/pgsql-general/2009-03/msg01106.php
http://archives.postgresql.org/pgsql-general/2004-10/msg00542.php
http://groups.google.com/group/comp.databases.postgresql.hackers/browse_thread/thread/4988752a6939f45a/ab22db2773d387d9?q=%22could+not+access+status+of+transaction%22+group%3Acomp.databases.postgresql.*&pli=1
http://www.nabble.com/Could-not-open-file-%22pg_clog-....%22-td23499731.html
http://archives.postgresql.org/pgsql-admin/2008-09/msg00276.php

And here's how I generated dummy clog files using bash that worked to get
the database back up again. After replacing all of the ones the database
wanted, and running a database-wide VACUUM to make sure everybody was
happy, I was able to dump all the recent data I don't have anywhere else
out of the system.

---

Generate a 256K file of all "55" characters, which indicates all
transactions it might be looking for are treated as already commited:

for i in {1..262144}; do printf '\125'; done > committed
ls -l committed
od -xv committed | head
od -xv committed | tail

$ ls -l committed
-rw-r--r-- 1 gsmith gsmith 262144 2009-06-25 11:01 committed
$ od -xv committed | head
0000000 5555 5555 5555 5555 5555 5555 5555 5555
0000020 5555 5555 5555 5555 5555 5555 5555 5555
0000040 5555 5555 5555 5555 5555 5555 5555 5555
0000060 5555 5555 5555 5555 5555 5555 5555 5555
0000100 5555 5555 5555 5555 5555 5555 5555 5555
0000120 5555 5555 5555 5555 5555 5555 5555 5555
0000140 5555 5555 5555 5555 5555 5555 5555 5555
0000160 5555 5555 5555 5555 5555 5555 5555 5555
0000200 5555 5555 5555 5555 5555 5555 5555 5555
0000220 5555 5555 5555 5555 5555 5555 5555 5555
$ od -xv committed | tail
0777560 5555 5555 5555 5555 5555 5555 5555 5555
0777600 5555 5555 5555 5555 5555 5555 5555 5555
0777620 5555 5555 5555 5555 5555 5555 5555 5555
0777640 5555 5555 5555 5555 5555 5555 5555 5555
0777660 5555 5555 5555 5555 5555 5555 5555 5555
0777700 5555 5555 5555 5555 5555 5555 5555 5555
0777720 5555 5555 5555 5555 5555 5555 5555 5555
0777740 5555 5555 5555 5555 5555 5555 5555 5555
0777760 5555 5555 5555 5555 5555 5555 5555 5555
1000000

chown postgres.postgres committed
chmod 600 committed
mv -i committed $PGDATA/pg_clog/0646

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD