Re: Could not open file "pg_clog/...."

--- On Tue, 12/5/09, Markus Wollny <Markus(dot)Wollny(at)computec(dot)de> wrote:

> From: Markus Wollny <Markus(dot)Wollny(at)computec(dot)de>
> Subject: [GENERAL] Could not open file "pg_clog/...."
> To: pgsql-general(at)postgresql(dot)org
> Date: Tuesday, 12 May, 2009, 11:04 AM
> Hello!
>
> Recently one of my PostgreSQL servers has started throwing
> error
> messages like these:
>
> ERROR: could not access status of transaction 3489956864
> DETAIL: Could not open file "pg_clog/0D00":
> Datei oder Verzeichnis
> nicht gefunden. (file not found)
>
> The machine in question doesn't show any signs of a
> hardware defect,
> we're running a RAID-10 over 10 disks for this
> partition on a 3Ware
> hardware RAID controller with battery backup unit, the
> controller
> doesn't show any defects at all. We're running
> PostgreSQL 8.3.5 on that
> box, kernel is 2.6.18-6-amd64 of Debian Etch, the
> PostgreSQL binaries
> were compiled from source on that machine.

Apart from not being on the latest release you have a very similar setup to me.

>
> I know that I'd be loosing some transactions, but in
> our use case this
> is not critical. Anyway, this made the problem go away for
> a while but
> now I'm getting those messages again - and indeed the
> clog-files in
> question appear to be missing altogether. And what's
> worse, the
> workaround no longer works properly but makes PostgreSQL
> crash:
>

The first thing I would have done if I've been forced to do that (if there was no other option?) would be a dump / restore directly afterwards, then pick through for any inconsistencies.

> Now what exactly is causing those missing clog files, what
> can I do to
> prevent this and what can I do to recover my database
> cluster, as this
> issue seems to prevent proper dumps at the moment?

Probably wait for the big-wigs to reply but perhaps a reindex may get you going.

I'd definately be starting with a fresh database once I got out of the whole though...