Re: BUG #3484: Missing pg_clog file / corrupt index - Mailing list pgsql-bugs
| From | Marc Schablewski |
|---|---|
| Subject | Re: BUG #3484: Missing pg_clog file / corrupt index |
| Date | |
| Msg-id | 470511C0.1020802@clickware.de Whole thread Raw |
| In response to | Re: BUG #3484: Missing pg_clog file / corrupt index (Decibel! <decibel@decibel.org>) |
| List | pgsql-bugs |
I've a question that's somehow relateted to this bug. I hope it's ok to post it here, even if it's not a bug report. We are planning to set up a standby system, in case our productive database system crashes again. Replication by WAL archiving is one possible solution. But the question is: would an error like the one we had appear in WAL and would it be replicated too? Or is there some kind of consistency check, that prevents broken WAL from being restored? Marc Schablewski click:ware Informationstechnik GmbH Decibel! wrote: > On Jul 25, 2007, at 4:02 PM, Marc Schablewski wrote: >> The following bug has been logged online: >> >> Bug reference: 3484 >> Logged by: Marc Schablewski >> Email address: ms@clickware.de >> PostgreSQL version: 8.1.8 >> Operating system: SuSE Linux 10.0 / Kernel 2.6.13-15.8-smp (x86-64) >> Description: Missing pg_clog file / corrupt index >> Details: >> >> Our application receives and processes payment information that comes in >> plain text files and stores the processed data into different tables >> in our >> database. There are about 5 tables involved, three of them with 35 >> million >> records so far. We get approximately 150000 payments a day. Each >> payment is >> handled in a single transaction, because in case of an error, we want to >> store as many payments as possible. We have about 500000 INSERT and a >> few >> UPDATE statements each day. The whole application runs on two servers >> (see >> specs below) which are nearly identical. One is the production >> system, the >> other is for testing. >> >> A few months ago we had some trouble with the test system. Postgres >> complained about a missing pg_clog file during nightly routine >> VACUUM/ANALYZE. Some days later, the same error occurred on the >> production >> system, even on the same table! The corrupted table is one of those >> bigger >> ones involved into the file processing. After searching the web we >> found a >> hint that this problem could be related to a bug in 8.1.3, so we >> upgraded to >> 8.1.8 and restored the databases on both servers. This was around >> May, but >> now we discovered the same problem on our production system again. >> Actually, >> the error occurred four weeks ago, but it was not discovered until now >> if you do error logging, you should look at it from time to time ;) >> >> When trying to backup or vacuum the database, we receive one of the >> following error messages: >> >> **BACKUP** >> pg_dump: ERROR: could not access status of transaction 2134240 >> DETAIL: could not open file "pg_clog/0002": Datei oder Verzeichnis >> nicht >> gefunden >> pg_dump: SQL command to dump the contents of table "dateiblock" failed: >> PQendcopy() failed. >> pg_dump: Error message from server: ERROR: could not access status of >> transaction 2134240 >> DETAIL: could not open file "pg_clog/0002": Datei oder Verzeichnis >> nicht >> gefunden >> pg_dump: The command was: COPY public.dateiblock (id, eda_id, dbt_id, >> lfdnr, >> binaer_offset, laenge) TO stdout; >> >> **VACUUM** >> INFO: vacuuming "public.dateiblock" >> ERROR: could not access status of transaction 2134240 >> DETAIL: could not open file "pg_clog/0002": Datei oder Verzeichnis >> nicht >> gefunden >> >> (For those not familiar to the German language: Datei oder Verzeichnis >> nicht gefunden means file or directory not found). >> >> Current pg_clogs range from 005A to 008F. The oldest one is dated to >> April >> 30th. >> >> We narrowed it down to a few records in that table. Some records contain >> unreasonable values, others produce the same message about the missing >> pg_clog file when selected and some are simply missing. But they must >> have >> existed, because there are still records in a second table >> referencing them. >> One strange thing about this is, that the referencing records are >> about two >> and a half months old and shouldnt been touched since then. >> >> We dont think this is a hardware issue, because we had it on two >> different servers and within a short period of time. >> >> Luckily, the loss of data is minimal. There are only about 30 records >> affected. Otherwise this would have been fatal, because as said >> before, our >> backup was not working either. >> >> >> In addition, we had another problem today. One of the indexes on a >> second >> table became corrupted, causing the database backend to rollback all >> transactions and restart the processes. A REINDEX fixed it, but it >> leaves me >> with a bad feeling about what will break next. >> >> 2007-07-25 08:07:00 CEST PANIC: right sibling's left-link doesn't match >> 2007-07-25 08:07:00 CEST STATEMENT: insert into transaktion (kaz_id, >> dbl_id, sta_id, kss_id, summe, zeitpunkt, tracenr, terminalid, status, >> kartennr >> , wae_id, kassenschnitt, freigabe, flo_id, importdatum) VALUES ($1, >> $2, $3, >> $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15) >> 2007-07-25 08:07:00 CEST LOG: server process (PID 5699) was >> terminated by >> signal 6 >> 2007-07-25 08:07:00 CEST LOG: terminating any other active server >> processes >> 2007-07-25 08:07:00 CEST WARNING: terminating connection because of >> crash >> of another server process >> 2007-07-25 08:07:00 CEST DETAIL: The postmaster has commanded this >> server >> process to roll back the current transaction and exit, because another >> server process exited abnormally and possibly corrupted shared memory. >> >> >> Kind regards, >> >> Marc Schablewski >> >> >> >> System: >> >> OS: SUSE LINUX 10.0 (x86-64), 2.6.13-15.8-smp x86_64 >> >> System: 2x Intel(R) Xeon(TM) CPU 2.80GHz Dual Core, 4GB RAM (HP >> proliant >> server) >> 3Ware 9500S-4LP, 2xRAID1 (one for OS & one for database) >> >> Postgres 8.1.8 >> >> Postgres was compiled manually but with default parameters. The >> configuration has been tuned to improve performance. >> >> shared_buffers = 30000 >> maintenance_work_mem = 131072 >> max_fsm_pages = 500000 >> default_statistics_target = 200 >> redirect_stderr = on >> log_directory = '/var/log/pg_log' >> >> log_min_duration_statement = 10 >> log_duration = on >> log_line_prefix = '%t ' >> stats_start_collector = on >> stats_command_string = on >> stats_block_level = on >> stats_row_level = on >> stats_reset_on_server_start = off >> >> VACUUM ANALYZE runs once a day, auto_vacuum is off. > > Actually, this does sound like a hardware problem to me. You only have > 5 tables that get hit heavily, so you've likely got somewhere around a > 20% chance that corruption would hit the same table on two different > machines. > > So far you haven't said anything that sounds unusual about how you're > using the database, and the hardware certainly seems pretty > common-place, so I'm rather doubtful that it's software. But if you > still have copies of the bad database, someone might be able to help you.
pgsql-bugs by date: