This is 100% correct. See my other posting describing the issues.
>
> >>>>> "ocie" == ocie <ocie@paracel.com> writes:
>
> > Bruce Momjian wrote:
> >> > > Hi, > > perhaps a stupid question: > > What will happen,
> >> if you start PostgreSQL with -F and write a script > which is a
> >> loop that fsyncs every 2 seconds? Does this lead to a >
> >> database which is "almost" correct? Does this seem like a good
> >> > compromise?
> >>
> >> Well, you really have to sync the data pages BEFORE sync'ing
> >> pg_log.
>
> > Why should this be necessary? If the transaction is considered
> > committed once the log has be written, and the system crashes
> > before the data are written, then postgres can look at the data
> > and logs when it is next started up and apply all the
> > transactions that were logged but not committed to the data
> > pages.
>
> > Am I missing something? It seems to me if you sync the data
> > pages first, then what good is the log? (other than being able
> > to retrace your steps)
>
> I do not think that pg_log is used like a normal 'log' device in other
> databases. My quick look at the code looks like pg_log only has a
> list of transactions and not the actual data blocks. Notice that
> TransRecover is commented out in backent/access/transam/transam.c.
>
> Most database log has the before images and after images of any page
> that has been modified in a transaction followed by commit/abort
> record. This allows for only this file to have to be synced. The
> rest of the database can float (generally checkpoints are done every
> so often to reduce recover time). The method of recovering from a
> crash is to replay the log from the last checkpoint until the end of
> the log by applying the before/after images (as needed based on
> weather the transaction commited) to the actual database relations.
>
> I would appreciate anyone correcting any mistakes in my understanding
> of how postgres works.
>
> > Ocie Mitchell
>
> Kent S. Gordon
> Architect
> iNetSpace Co.
> voice: (972)851-3494 fax:(972)702-0384 e-mail:kgor@inetspace.com
>
--
Bruce Momjian
maillist@candle.pha.pa.us
