Hello,
Currently running Postgres v 8.0.3 on a RHEL AS4 production system (Kernel 2.6.9-42.0.2.ELsmp). Database is fairly large and growing, as such has vacuum and pg_dump scripted to run from cron nightly. A few days ago noticed the nightly dump filesize was roughly half its 'normal' size (288 MB gzip'ed, down from ~ 500 MB gzip'ed). Ran our pg_dump script manually, received the following output:
pg_dump: ERROR: could not access status of transaction 1024987564
DETAIL: could not open file "/rtdb/data/pg_clog/03D1": No such file or directory
pg_dump: SQL command to dump the contents of table "attachments" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: could not access status of transaction 1024987564
DETAIL: could not open file "/rtdb/data/pg_clog/03D1": No such file or directory
pg_dump: The command was: COPY public.attachments (id, transactionid, parent, messageid, subject, filename, contenttype, contentencoding, content, headers, creator, created) TO stdout;
The DB error log shows the following:
ERROR: could not access status of transaction 1024987564
DETAIL: could not open file "/rtdb/data/pg_clog/03D1": No such file or directory
File '03D1' doesn't exist in the pg_clog directory (22 total entries, 0000 thru 0015). Searched for this error on the Net, found the "phantom pg_clog" to be fairly common and indicative of data corruption within the database; a bad tuple header / row association. What is throwing me is the 'PQendcopy() failed'stanza, with a whole table failing to process, not just a specific row.
The scripts that are called from cron are as follows
Vacuum: /usr/local/postgres/bin/vacuumdb -U postgres --analyze rt3 -v >> /rtdb/logs/rtvacuum_`date +%Y%m%d` 2>&1
"pg_dump": /usr/local/postgres/bin/pg_dump -U postgres -c --format=c rt3 | gzip > /rtdb/backups/rtdata-`date +%Y%m%d`.gz
The vacuum runs 15 minutes prior to the pg_dump script.
Any and all help on troubleshooting this issue and recovering the associated data is greatly appreciated.
Thanks,
Duane
Duane S. Alter
Health Care Information Systems
University of Iowa Hospitals and Clinics