Thread: ERROR: could not access status of transaction 2495690984
I have had som problem with my filesystem and now I get these error messsages when trying to access my database..: ERROR: could not access status of transaction 2495690984 Could not open file "pg_clog/094C": No such file or directory. Is there a way to fix this or do I have to find a backup? Regards, BTJ -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen btj@havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
2009/7/21 Bjørn T Johansen <btj@havleik.no>: > I have had som problem with my filesystem and now I get these error messsages when trying to access my database..: > > ERROR: could not access status of transaction 2495690984 > Could not open file "pg_clog/094C": No such file or directory. got backups? 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. merlin
On Tue, 21 Jul 2009 15:32:07 -0400 Merlin Moncure <mmoncure@gmail.com> wrote: > 2009/7/21 Bjørn T Johansen <btj@havleik.no>: > > I have had som problem with my filesystem and now I get these error messsages when trying to access my database..: > > > > ERROR: could not access status of transaction 2495690984 > > Could not open file "pg_clog/094C": No such file or directory. > > got backups? > > 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. > > merlin > Yes, I have backups... Was just kind of hoping for another solution.... :) BTJ
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@gregsmith.com http://www.gregsmith.com Baltimore, MD