Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group