Re: Question on placing database on a network attached

If you are using postgresql-8.0.x, then you can use PITR and base backups to
achieve a fairly consistent backup on a NAS which restores rather quickly. If
the NAS supports hard links, then you can use the --link-dest option of rsync
to make the base backups quite fast and still retain more than one base backup
at a time. The only problem with this solution is that you need at least as
much disk space as your PGDATA directory contains, plus enough space for the
WAL archives between base backups.

If the NAS does not support hard links, but does do snapshotting as you
indicate, then using rsync with the --partial flag against the previous
base backup would also be quite fast and bandwidth efficient. The
snapshotting on the NAS would take care of the prior version base backup for
you and all you need do is rotate out the WAL archives according to how many
snapshots you care about.

Using PITR in this way has the added benefit of allowing you to restore the DB
to some point in the past if need be.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

On Sat, 30 Jul 2005, N/A wrote:

> The NAS is a Network Appliance 920c. I understand that an individual file
> while be "intact" during a snap, (not sure I believe it, but that's what I'm
> told). The NAS may be able to monitor the state of an individual file and
> not "snap" it if it is on an "open" state or has a pending write, or some
> such, but as the NAS has no knowledge of groups of files that need to exist
> in a known state, (it deals on the file level), it cannot guarantee a
> consistent state across a group of files. (The database will be on the NAS
> and will be NFS mounted filesystem to a Linux Redhat 3 running the
> application.)