Thread: Database corruption
Hello all, Whilst running a regular pg_dumpall, I received the following error from our spamassassin DB. pg_dump: ERROR: could not access status of transaction 4521992 DETAIL: could not open file "pg_clog/0004": No such file or directory pg_dump: SQL command to dump the contents of table "bayes_token" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: could not access status of transaction 4521992 DETAIL: could not open file "pg_clog/0004": No such file or directory pg_dump: The command was: COPY public.bayes_token (id, token, spam_count, ham_count, atime) TO stdout; pg_dumpall: pg_dump failed on database "spamassassin", exiting It's clear something's amiss with the database but I'm not sure where to go from here. Is any data from bayes_token recoverable, should I just delete the data in that table or does the whole database need to be dropped and recreated? Tia, Shane -- http://www.cm.nu/~shane/
Shane wrote: > Hello all, > > Whilst running a regular pg_dumpall, I received the > following error from our spamassassin DB. > > pg_dump: ERROR: could not access status of transaction > 4521992 > DETAIL: could not open file "pg_clog/0004": No such file > or directory > pg_dump: SQL command to dump the contents of table > "bayes_token" failed: PQendcopy() failed. > pg_dump: Error message from server: ERROR: could not > access status of transaction 4521992 > DETAIL: could not open file "pg_clog/0004": No such file > or directory > pg_dump: The command was: COPY public.bayes_token (id, > token, spam_count, ham_count, atime) TO stdout; > pg_dumpall: pg_dump failed on database "spamassassin", > exiting > > It's clear something's amiss with the database but I'm not > sure where to go from here. Is any data from bayes_token > recoverable, should I just delete the data in that table or > does the whole database need to be dropped and recreated? Hang on. You may still recover the data. What range of files do you see in the pg_clog directory? Also, what version is this? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Fri, May 18, 2007 at 09:11:58PM -0400, Alvaro Herrera wrote: > > > > It's clear something's amiss with the database but I'm not > > sure where to go from here. Is any data from bayes_token > > recoverable, should I just delete the data in that table or > > does the whole database need to be dropped and recreated? > > Hang on. You may still recover the data. What range of files do you > see in the pg_clog directory? Also, what version is this? continuum:~# ls /var/lib/postgresql/8.1/main/pg_clog/ 0020 0025 002A 002F 0034 0039 003E 0043 0048 004D 0052 0057 0021 0026 002B 0030 0035 003A 003F 0044 0049 004E 0053 0058 0022 0027 002C 0031 0036 003B 0040 0045 004A 004F 0054 0059 0023 0028 002D 0032 0037 003C 0041 0046 004B 0050 0055 005A 0024 0029 002E 0033 0038 003D 0042 0047 004C 0051 0056 005B This is postgresql 8.1.8 from Debian. I've seen no sign of disk corruption thus far. It's a raid5 so would see a failure though not a real corruption if that actually happened. Shane -- http://www.cm.nu/~shane/