Re: Postgres crashes,help to recover - Mailing list pgsql-general

From Magnus Hagander
Subject Re: Postgres crashes,help to recover
Date
Msg-id 6BCB9D8A16AC4241919521715F4D8BCE6C7798@algol.sollentuna.se
Whole thread Raw
In response to Postgres crashes,help to recover  ("Andrus" <eetasoft@online.ee>)
List pgsql-general
> > What version exactly was this? There was some changes in
> 8.0.2 in this
> > area on win32. The default sync method was also changed at
> this point.
>
> "PostgreSQL 8.0.2 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.4.2 (mingw-special)"

There we go. That explains it - you have the new code.


> > What's the output of "show wal_sync_method"?
>
> "open_datasync"

This setting is only safe if you disable write cache.


> > Finally, go into device manager, find your disk, get
> properties, look
> > under Policies, is the box for "Enable write caching on the disk"
> > checked?
>
> It is checked.
>
> Does Postgres require this to be unchecked ? It is difficult
> to force customers to change it.

No, doesn't erquire it. There are a couple of different scenarios:

1) Box is checked. wal_sync_method=open_datasync. This may cause data
loss!
2) Box is checked. wal_sync_method=fsync_writethrough. This is safe.
3) Box is unchecked. wal_sync_method=open_datasync. This is safe.
4) Box is unchecked. wal_sync_method=fsync_writethrough. This is safe.

In general I would say that 1 is of course the fastest, but it's not
safe. 3 should normally be the fastest if the data is on a disk that's
only used by postgresql. 2 is probably faster if you have other
applications that also write data to the same disk. 4 is probably
*never* fastest :-)


This all assumes you don't have a battery backed cache. If you have a
controller with battery backed cache, 1 should still be the fastest, but
now it's suddenly safe.

(The basics of these changes are documented in the release notes at
http://www.postgresql.org/docs/8.0/static/release-8-0-2.html)

//Magnus

pgsql-general by date:

Previous
From: "silvanus"
Date:
Subject: development snapshots old
Next
From: teknokrat
Date:
Subject: Re: Quotation marks in queries