We did some analyzing and found out some mysterious things.
First we used the pg_check tool to analyze the table.
The result is:
# select pgcheck_page( 211593798 );
WARNING: relation 'adresse_080103', tuple (46,11): start of tuple is not aligned properly
WARNING: relation 'adresse_080103', tuple (46,11): has t_xmax < t_xmin
pgcheck_page
--------------
9808
(1 row)
# select pgcheck_index_full( 211593798 );
NOTICE: name of tested relation: 'adresse_080103':
NOTICE: relation 'adresse_080103' doesn't have index
pgcheck_index_full
--------------------
211593798
(1 row)
So we looked up the row: SELECT * FROM public.adresse_080103 where ctid = '(46,10)'::tid;
and did an pg_filedump on the database file and searched for the data of that recordset.
After that recordset we found corrupted data!
It seems to be corrupted in the same way as the error before.
The dump of the corrupted data is appended. ( We replaced some sensitive data with '*' at the beinning and the end )
We went on with analyzing:
- the table was created at 2008/01/03 17:56h
- the nightly dump started at 2008/01/03 22:00h
- it tried to copy the table 'adresse_080103' at 22:00:08
- the dump crashed at 22:32:10 ( because of the error we reported 2007/12/14; we repaired the database not till
2008/01/11)
The stat of the database file returns this:
File: "/postgres/database/data/base/23144/211593798"
Size: 1835008 Blocks: 3592 IO Block: 4096 reguläre Datei
Device: 811h/2065d Inode: 18121638 Links: 1
Access: (0600/-rw-------) Uid: ( 1001/postgres) Gid: ( 2/ daemon)
Access: 2008-02-15 18:19:44.000000000 +0100
Modify: 2008-01-03 22:00:34.000000000 +0100
Change: 2008-01-03 22:00:34.000000000 +0100
We are wondering, that the pg_dump seems to have modified the file.
But we think, that the file couldn't be corrupted at this time.
Each night from 2008/01/11 (the repair day) up to 2008/02/01 the nightly dump ran successful.
Suddenly at 2008/02/02 it crashed.
It seems that something corrupted the data without changing the modification time of the file.
At the moment we have no idea, how this situation comes.
Could you please answer, if the pg_dump modifies the access timestamp in some cases?
Thanks in advance,
Alex