Re: Question about linux filesystem and psql

From: Doug McNaught <doug(at)mcnaught(dot)org>
To: Marcus Wegner <wegner3000(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Question about linux filesystem and psql
Date: 2004-07-29 16:07:42
Message-ID: 8765863igh.fsf@asmodeus.mcnaught.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Marcus Wegner <wegner3000(at)hotmail(dot)com> writes:

> Is it possible to keep data safe with linux and psql after crash?
>
> The short description of the scenario is:
> -> writing data with psql (using transactions), store process completed
> -> user hits the reset button or kernel crashes (whatever left the filesystem
> unsynced)
> -> filesystem is xfs or reiserfs
> -> reboot causes some blocks filled with zero (journalling feature discussed
> on lkml) of last accessed files from some applications

The quesiton here is: do these filesystems lie about fsync()? Or do
they just corrupt files that were written but not synced before the
crash?

I think it's the latter--lying about fsync() is a pretty major bug,
and I don't recall that being claimed on LKML.

Given this, PG should be in good shape--it fsyncs() the WAL file
before reporting transaction success, so it should be able to recover
committed transactions.

> My questions are:
> Is there any solution that psql keeps the database intact (already written
> data)?
> Is there an option for psql or filesystem (like reiserfs data=ordered) which
> should be used (maybe backup database)

You should always back up your data. :)

You might also consider ext3 with data=writeback. As long as the WAL
files are in sync and the filesystem metadata is journaled, PG can
recover from crashes just fine (barring actual hardware failure).

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jason Tesser 2004-07-29 16:09:07 Backup questions
Previous Message Greg Stark 2004-07-29 15:56:46 Re: Table access method not behaving like Oracle (index vs sequential scan). Examples and stats provided.