Re: [HACKERS] The dangers of "-F" - Mailing list pgsql-hackers

> I realize that writing buffers to a file and making sure they're
> on disk are two different issues.  My point is that without the
> fsynch, the backend loses control over the order in which blocks
> are written to the disk.

Yes, that is the problem.  One solution is to fync all modified file
descriptors every ~30 seconds, then write and fsync pg_log(), so you
only do an fsync every 30 seconds.  Of course you have to make sure
pg_log doesn't get put on disk until after all the file descriptors are
fsync'ed.  Of couse, you have a 30-second window of loss, but most file
systems do this every 30-seconds, so it is no less reliable than that. 
(Well, most OS's sync on file close, so you could say the file system is
has less loss.)  Anyway, this is how most commercial db's do it.  (One
easy way to do it would be do issue a "sync" every 30 seconds to flush
the whole OS, but that seems a little extreme.)


> For instance, if there are assumptions that all data blocks are
> written before this fact is recorded in a log file, then
> "write data blocks" "fsynch" "write log" "fsynch" doesn't break
> that assumption, where "write data blocks" (no fsynch) "write log"
> might, as the operating system's free to write the "write log"
> blocks to disk before any of the data blocks are (though an
> LRU algorithm most likely wouldn't).  You could end up in a
> case where the log records a successful write of data, without
> any data actually being on disk.
> 
> I don't know how postgres works internally.  So my question is
> really "are any such assumptions broken by the use of -F, and
> does breaking such assumptions lead to a more serious form
> of failure if there's a crash?"

It is possible in an OS crash because we don't have any info about what
order stuff is written to disk with -F.

> I agree that the risks of running -F are low with reliable
> hardware and a UPS.  I'm just trying to get a handle on just
> what a user might be facing in terms of corruption compared
> to a crash with fsynch'ing enabled.  I can live with "the
> database might well become corrupted and you'll have to
> reload your latest dump".
> 
> My current plan is to implement a set of queries that do
> fairly detailed consistency checks on my database every
> night, before doing the nightly dump and copy to a second
> machine, as well as each time I restart the web server
> (typically only after crashes).  In this way I'll know
> quickly if any harm's been done after a crash, I'll have
> some assurance the database is in good shape before dumps
> (my code, not just the backend, might have bugs!), etc.
> 

Sounds like a good plan.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026