Re: Missing files under pg_data following power failure

On Fri, 2007-10-12 at 10:54 -0500, Scott Marlowe wrote:
> On 10/12/07, Jack Orenstein <jack(dot)orenstein(at)hds(dot)com> wrote:
> > Our testing involves cutting power to machines running postgres 7.4,
> > while under load (lots of reads and writes). When we do this, we often
> > lose some files under pg_data storing table content. I.e., the file
> > named for a table's pg_class.oid value is simply gone. This can affect
> > many tables following a power outage. We know this problem has
> > occurred when we are unable to access a table, e.g.
> >
> > ERROR: relation "xxx" does not exist
> >
> > The table is present in the system tables, but the file storing table
> > content under pg_data is missing.
> >
> > Can anyone provide insight on this phenomenon? Why are these files
> > lost? Are they really lost, or have they simply moved somewhere? What
> > happens to the disk blocks formerly occupied by the files?
> >
> > Getting back in service following this file loss is not a problem; I'm
> > just trying to understand how postgres gets into this state.
>
> First of all, this should not happen on a machine with proper
> fsyncing. The possible causes are generally either fsync is off in
> postgresql.conf or the drive array <--> OS layer is lying about fsync
> operations.

What filesystem are you using? I've seen similar problems on JFS2, it
was something with logredo or fsck being busted on a Big Blue OS.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.