On Sat, 15 Jun 2002, Greg A. Woods wrote: > Unless you can do a global database lock at a point where there are no > open transactions and no uncommitted data in-core, the only way to > guarantee a consistent database on disk is to close all the database > files. Even then if you're very paranoid and worried about a hard crash > at a critical point during the snapshot operation you'll want to first > flush all OS buffers too, which means first unmounting and then > remounting the filesystem(s) (and possibly even doing whatever is > necessary to flush buffers in your hardware RAID system). > > It really_ is best to just use pg_dump and back up the result.
it's probably best, yes. but even here, pg_dump will not dump updates that happen after the dump starts. read up on pgsql's versioning system - there are no guarantees about data it (eg pg_dump) returns being the latest available - only that it is consistent. > I agree, but we're not discussing what you and I do, but rather what > random John Doe DBA does. There's ample quantity of suggestion out > in the world already that makes it possible he will turn off fsync > for performance reasons their tough luck really. :) > Now you're getting a little out of hand. A journaling filesystem is > a piling of one set of warts ontop of another. Now you've got a > situation where even though the filesystem might be 100% consistent > even after a catastrophic crash, the database won't be. There's no > need to use a journaling filesystem with PostgreSQL eh? there is great need - this is the only way to guarantee that when postgresql does operations (esp on its own application level logs) that the operation will either: - be completely carried out or - not carried out at all > either full mirroring or full level 5 protection). Indeed there are > potentially performance related reasons to avoid journaling > filesystems! if they're any good they should have better synchronous performance over normal unix fs's. (and synchronous perf. is what a db is interested in). > This is true. However before you give that fact 100% weight in your > decision process you need to do a lot more risk assessment and > disaster planning to understand whether or not the tradeoffs > inherent will not take away from your real needs. > > I still believe it really Really REALY is best to just use pg_dump > and back up the result. ok. whether it is best or not is a matter for debate and, perhaps, context. however, it is not on to state that use of snapshots is /never/ to be considered. --paulj
