Обсуждение: backing up a PostGRES DB

Hello,

I'm putting together a web app that uses PostGRES as a backend and I'm looking for comments on backup strategy.
The entire web app, (Tomcat and PostGRES), will be run in a virtual machine.  I was thinking about quiescing the DB, then copying the files that make up the virtual machine to some network attached backup store.  This will be done once a day.
I ask this august body, based on your experiences, does this sound like a reasonable plan, or would doing something like running pgdump be better, or ????
Thanks for the help one and all.

Rich S.

Well, there are a lot of unanswered questions in your post.

What VM technology are you using? Does it support clustering or some such?
Do you need to backup the entire VM, or just the PG data?

Generally speaking, if the "machine" itself (installed OS and programs, etc)
are not changing, what I would do is this:

a) Get to go-live on your VM system
b) Stop the VM
c) Backup the VM disk/settings/etc
d) Move it to another system to ensure it runs flawlessly
e) Burn that to DVD or stick it a few places on the network

Now, you've got your base machine. From there on out, I'd do regular
pg_dumps to just backup my data. Faster, easier to store, easier to come
back from etc.

Now, this also implies that this is not a mission-critical machine which
needs to be replicated, and that "x" hours of data loss (where "x" is the
number of hours/days/weeks/months between your pg_dumps) is an acceptable
loss.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Richard Sickler
Sent: Wednesday, April 01, 2009 10:14 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] backing up a PostGRES DB

Hello,

I'm putting together a web app that uses PostGRES as a backend and I'm
looking for comments on backup strategy.
The entire web app, (Tomcat and PostGRES), will be run in a virtual machine.
I was thinking about quiescing the DB, then copying the files that make up
the virtual machine to some network attached backup store.  This will be
done once a day.
I ask this august body, based on your experiences, does this sound like a
reasonable plan, or would doing something like running pgdump be better, or
????
Thanks for the help one and all.

Rich S.

Richard Sickler <richard.sickler@avagotech.com> writes:
> The entire web app, (Tomcat and PostGRES), will be run in a virtual
> machine.  I was thinking about quiescing the DB, then copying the files that
> make up the virtual machine to some network attached backup store.  This
> will be done once a day.

This is really only attractive if you are prepared to *shut down* the
DB during the copy, not just "quiesce" it.  Otherwise there's
considerable risk of getting a corrupted copy.  Read the manual's
chapter about backup strategies.

            regards, tom lane