Unlinked files in PGDATA/base following unclean shutdown

From: Jack Orenstein <jack(dot)orenstein(at)hds(dot)com>
To: pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Unlinked files in PGDATA/base following unclean shutdown
Date: 2008-08-08 18:52:14
Message-ID: 489C95DE.8040400@hds.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Our application is running Postgres 7.4, (working on conversion to 8.3
right now). Our testing involves various forms of violence, including
shutting off power and kill -9 postmaster.

Occasionally we observe a form of database corruption in which one of
the files storing a table or index disappears. The logs will contain
ERRORs that look like this:

could not open relation "some_table_name": No such file or directory

When this happens, and I cross-reference the pg_class.oid with the
expected file under PGDATA, the file is missing (and does not appear
to be in lost+found).

I have fsync set to true, and wal_sync_method set to fsync.

A few questions about this:

1) Why is this happening?

2) To help investigate this problem, I've written a script to
cross-reference pg_class and the files in PGDATA/base. (I know that I
should use pg_class.relfilenode instead of pg_class.oid -- I'll fix
that.) The question is how to check for consistency in the case of
large tables, which are split into multiple segments, (e.g. 123456.1,
123456.2). I.e., how can I find out how many segments there should be?
Any chance it's as simple as (pg_class.relpages + SUITABLE_CONSTANT -
1) / SUITABLE_CONSTANT?

Jack

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Murphy 2008-08-08 18:59:39 Re: How to use postgresql-jdbc rpm with Sun JDK
Previous Message Heeman Lee 2008-08-08 17:10:47 Re: pgbench