Re: Question about linux filesystem and psql

Marcus Wegner <wegner3000@hotmail.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