If you pull the power cord, the DB should be in a consistent state barring hardware and other issues. However there can be data loss, because the clients might have been inserting data which may not be able to be reproduced again, but which was not committed nor reported as committed, and the clients may no longer have a copy of the data. So how does the app or user deal with that? That can determine if data is lost holistically.
For example, if you were in the process of inserting 1 million rows from a raw source, haven't committed and someone pulls the plug, the transaction will not be committed. Or a prospective customer clicks "Submit Order", and you pull the plug just then, customer sees the browser spin for a minute or so, times out, gives up goes somewhere else, and you don't get the order.
Given I've seen lots of things not work as they should, 100% guarantees seem like wishful thinking. I'd just believe that with Fsync on, the probability of commits being saved to disk is very high. Whereas with fsync off, the probability is a lot lower especially if the O/S is prone to taking 30 seconds or longer to sync outstanding data to disk.
Also, if power was lost in the middle of an fsync I'm not sure what actually happens. I think there are some checks, but they often assume atomicity of an operation at a particular level. Even if that holds true, the hardware could still fail on you - writing something whilst running out of power is not a good situation.
Regards, Link.
At 05:11 PM 7/7/2003 +0200, Mattias Kregert wrote:
> > If I turn fsync on and then pull the power cord while a > > number of clients are doing lots of inserts/updates and stuff, > > will the fsync then guarantee that no data will be lost or > > corrupted? > You are surely kidding, aren't you ? > > Karsten
No. No kidding.
Just to clarify, what I mean is: With FSYNC enabled, after a power failure, after "pg_ctl start" and replay of xact log etc; Are COMMITTED transactions guaranteed to be intact, and are UNCOMMITTED transactions guaranteed not to appear in the tables?
If the answer is "yes", then I understand the use of FSYNC.
If the answer is "no", then i don't see the point in using FSYNC at all.
/* m */
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
