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: (view raw or whole 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

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).

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-2015 The PostgreSQL Global Development Group