Re: journaled FS and and WAL

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of t(dot)dalpozzo(at)gmail(dot)com
Sent: Wednesday, October 19, 2016 11:01 AM
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Cc: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] journaled FS and and WAL

So, as for the data content of the WAL file, I see that no more page will be allocated. I wonder if during a crash, strange things can still happen at disk level however, in particular in SSD devices; on these things we have no control, and perhaps journaling helps?
As for the metadata, if during a crash it's flushed (with fdatasync, only when the FS decides to do that), can anything bad happen without journaling?

Third, let's suppose that the WAL can't get corrupted. When the system flushes data pages to the disk according to the WAL content, if there is a crash, am I sure that tables files old pages and /or their metadata, inode.... can't get corrupted?
If that, there is no possibility to reconstruct the things, even through the WAL. Even in this case, perhaps journaling helps.

I don't mind about performance but I absolutely mind about reliability, so I was thinking about the safest setting of linux FS and postgresql I can use.
Thanks!
Pupillo

Il 15/10/2016 07:52, Michael Paquier ha scritto:
> On Fri, Oct 14, 2016 at 11:27 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
>> After a successful commit, the WAL file and its metadata are on disk.
>> Moreover, the file metadata won't change (except for the write and
>> access
>> timestamps) because WAL files are created with their full size and
>> never extended, so no WAL file should ever get "lost" because of
>> partial metadata writes.
> This behavior depends as well on the value of wal_sync_method. For
> example with fdatasync the metadata is not flushed. It does not matter
> any for for WAL segments as Albe has already mentioned, but the choice
> here impacts performance.

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi!
PG can lost its segments from data file and nobody knows it. For PG - no file = no data and no need to recover after crash, there is no infos about what data files belongs to PG.
After this don’t bother about WAL and anything else =)
Just use FS with journal, check sums you DB with initdb -k, fsync=on , do regular backups and check it thoroughly with restore. Also don’t forget to praise the gods that so far PG clogs file is not corrupted while being not protected by any checksums in minds. Youl never know that PG clog is corrupted until "doomsday"

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company