Re: Question on database backup

On Wed, 4 Feb 2004, Michael Brusser wrote:

> We have customers who prefer to use their backup facilities
> instead of what we provide in the app (we use pg_dump)
> I hear speed is at least one consideration.
>
> The questions I need to answer are these:
>
> 1) Is this absolutely safe to do file copy (cpio, or
> smth. else, whatever the robust backup app. would use)
> on the Postgres db, when it's completely shut down.

Yes, it is.

> 2) Same question, but the database is up and running in
> read-only mode. We're making sure that no updates are
> taking place.

Most likely, it is. No guarantees if the database is up and running, even
if you're certain there are no updates happening.

Also, you can use a snapshotting file system to make a backup image and
then back up the image, while the database is up and being accessed, both
by readers and writers, assuming you get REAL snapshots.

> If it matters - this is on Solaris, HP, Linux.
> We're using v.7.3.2 running on UDS and v.7.3.4 on TCP/IP
> We provide no explicit settings for wal, fsync and the like.
> And (yes, I know) they often install it on NFS.

OK, here's a couple more issues to chew on as well. Suppose you have a
backup, and the database server was compiled with UNKNOWN switches. The
machine burns to the ground. now you get to try and figure out how to
compile the database on the new server so it can read the old dataset.
This may or may not be a complete friggin' nightmare for you. Dumps can
move between versions / hardware configs / differently compiled versions
of postgresql with some amount of reliability. binary copies, may or may
not move so easily.

Scenario II, the HP burns to the ground, and your boss just buys a big old
intel box. how do you get your data up and running with a binary backup?
you don't.

Scenario III. Subtle corruption gets into your dataset due to a bad block
or what not. No one notices for a while. Suddenly, someone notices.
With only file system backups, with no error messages in them, how do you
determine when the corruption occurred and get the uncorrupt data out
leaving the corrupted behind?

Plain and simple. Postgresql is designed to be backed up by pg_dump.
Using anything else "isn't supported" so to speak, and may cause you
untold grief in the future. That said, sometimes file backups are the
perfect solution, just go into with your eyes open to the possible
problems, and I'd make a pg_dump every so often just in case.