Обсуждение: Snapshot Copy of a Postgres DB

On 5/1/06 10:37 AM, "Samuel, Rowena" <Rowena.Samuel@netapp.com> wrote:

> Hi All,
>
> I have no Postgres knowledge. I am trying to find out if it would be
> possible to Snapshot a Postgres database for a
> PostgreSQL user who has a mixed environment and takes Snapshot copies of
> all his other databases.
>
> What we would normally do for a database Snapshot in other database
> environments is to write a script which places
> the database into a hot backup mode, takes the Snapshot, and then brings
> the database back into normal operational
> mode.
>
> I have talked to a couple of PostgreQL DBAs and I understand that, while
> there are hot backups with pg_dump, there is
> no hot backup mode that the database can be placed into in Postgres. Can
> I get confirmation of this?
>
> Assuming the answer to the above is "no", one DBA I talked to mentioned
> that Postgres writes to a buffer(?) or log(?)
> before the disk. So is it possible to just simply Snapshot the database
> on the fly? How does Postgres handle a
> situation where server running the database crashes? How does it handle
> error recovery? This would be similar to
> taking a snapshot on the fly....

 pg_dump makes a dump of the database that is self-consistent.  Other
transactions can be occurring during the dump, but those will not be
included in the dump.  There is no need to bring the server down or to put
it in backup mode to do this.  As for recovery, there is usually little you
need to do.  Just start postgres again and generally all is well.  If you
have disk corruption or something more serious, then the recovery is
probably similar to most other database products.  You simply take your
backup and recover with it.  If you need point-in-time-recovery, you will
probably want to look at the documentation more specifically, as the WAL
files will need to be backed up as well for that (see the docs for details).

Sean

On 5/3/06 8:18 AM, "Samuel, Rowena" <Rowena.Samuel@netapp.com> wrote:

>
>
> Hi Sean,
>
> Thanks for the reply, but not exactly what I am looking for.
>
> My customer is currently using pg-dump but his database is large (
> hundreds of GBs ) so pg-dump is becoming disruptive for him. Takes a
> while to backup and due to the number of writes on the database it does
> seem to impact performance is slow down those writes.
>
> So, he is keen to use our Snapshot technology to take an almost
> instantaneous snapshot of the DB. We have Snapshot management tools for
> some databases but not Postgres as yet. Most DBs will have some kind of
> backup mode like that of pg_dump that allows you to place the DB into a
> quiet state but without doing a dump.
>
> It definitely appears this is not the case for Postgres as noone seems
> to know about a "hot-backup" command state.
>
> So my question around recovery was, given that Postgres uses a WAL and
> has flush markers or consistency points in the log, if we took a
> Snapshot on the fly. How well would the database recover when we
> restored it? I expect it would recover quite well but I am hoping
> someone has some experience with this and understands the WAL enough to
> answer???

Have you looked here:

http://www.postgresql.org/docs/8.1/interactive/backup-online.html

I think that describes in some detail the typical way of establishing PITR
backups, but I haven't actually used this in practice (we are mainly
read-only here).  If performance is the main concern, then perhaps posting
to either "general" or "performance" with some numbers will be beneficial,
as there may be some tuning that needs to occur, like putting WAL files on a
separate disk from the main tablespace, for example.

Sean