DomainKey-Signature: a=rsa-sha1; c=nofws; d=wpkg.org; h=message-id:date
	:from:mime-version:to:cc:subject:references:in-reply-to
	:content-type:content-transfer-encoding; q=dns; s=default; b=otL
	DKS8dJdaYazgz25vfC872fVS+9fy/ampXjYnTRZiZCB35PdPpSjV4AGRMENlJhmL
	WHH5yNySlKSlV4UqUTyWqHjetYrSP+nq9zRi7PZq1IZEa0QF4vrM4zt4MryUpQZx
	b3/JKkrf6RdA2Bqyvxs6lban3wYya37qqpEzPSm4=
Message-ID: <4DC19CD1.7070600@wpkg.org>
Date: Wed, 04 May 2011 20:37:05 +0200
From: Tomasz Chmielewski <mangoo@wpkg.org>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.15) Gecko/20110419 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org
Subject: Re: 'SGT DETAIL:  Could not open file "pg_clog/05DC":	 No
	such file or directory' - what to do now?
References: <4DC0F063.70306@wpkg.org>
	<4DC15137020000250003D295@gw.wicourts.gov>
In-Reply-To: <4DC15137020000250003D295@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 04.05.2011 20:14, Kevin Grittner wrote:
> Tomasz Chmielewski<mangoo@wpkg.org>  wrote:
> 
>> On 1st May, I saw this message in my postgres log:
>>
>> May  2 06:52:02 db10 postgres[3590]: [29829-1] 2011-05-02 06:52:02
>> SGT ERROR:  could not access status of transaction 1573786613
>> May  2 06:52:02 db10 postgres[3590]: [29829-2] 2011-05-02 06:52:02
>> SGT DETAIL:  Could not open file "pg_clog/05DC": No such file or
>> directory.
>> May  2 06:52:02 db10 postgres[3590]: [29829-3] 2011-05-02 06:52:02
>> SGT STATEMENT:  SELECT 1 FROM core_bill_id_seq FOR UPDATE
> 
> You saw errors on the 1st dated for the 2nd?

My bad; it was 2nd, not 1st.


>> Now, I'm not sure what I should do about it. Database behaves
>> "funny", some inserts do not work.
> 
> Define "funny".  What happens when you attempt the inserts which
> don't work.  (Copy and paste any error messages.)  Is it all tables?
> All inserts to one table?  Any other discernible pattern?

This repeated many times:

/var/log/postgresql/postgresql_log.1:May  3 18:24:49 db10 postgres[21363]: [26999-1] 2011-05-03 18:24:49 SGT ERROR:  could not access status of transaction 1573786613
/var/log/postgresql/postgresql_log.1-May  3 18:24:49 db10 postgres[21363]: [26999-2] 2011-05-03 18:24:49 SGT DETAIL:  Could not open file "pg_clog/05DC": No such file or directory.
/var/log/postgresql/postgresql_log.1-May  3 18:24:49 db10 postgres[21363]: [26999-3] 2011-05-03 18:24:49 SGT STATEMENT:  SELECT 1 FROM core_wot_seq FOR UPDATE


Today I have this:

/var/log/postgresql/postgresql_log:May  4 22:43:44 db10 postgres[15773]: [555-1] 2011-05-04 22:43:44 SGT ERROR:  could not access status of transaction 1612337841
/var/log/postgresql/postgresql_log-May  4 22:43:44 db10 postgres[15773]: [555-2] 2011-05-04 22:43:44 SGT DETAIL:  Could not open file "pg_clog/0601": No such file or directory.
/var/log/postgresql/postgresql_log-May  4 22:43:44 db10 postgres[15773]: [555-3] 2011-05-04 22:43:44 SGT STATEMENT:  SELECT 1 FROM core_wbl_seq FOR UPDATE

Only such two (different) occurrences; repeated 10-20 times; two different tables.

The system is used heavily, so it would show lots of other errors in other places if it was some major fault.
Which does not include some "minor" fault.


>> 4) I may have hardware problems - but this server is running for
>> almost 1 year now, is super stable - servers with hardware issues
>> are likely to show some issues as well
> 
> Does the server have ECC memory?  Do you have SMART monitoring of
> the storage system, or something similar?  Any errors showing in any
> system logs?

No errors at all anywhere (dmesg, syslog etc.).

It's ProLiant DL180 G6, and I think it should have ECC. At least I see it being mentioned in dmidecode.

Assuming we can't determine what caused the corruption (bitflip, kernel bug, bad RAM, postgres bug, silent HDD error etc.) - how should I best recover from this?


-- 
Tomasz Chmielewski
http://wpkg.org