Re: Snapshot Copy of a Postgres DB

On 5/3/06 8:18 AM, "Samuel, Rowena" <Rowena(dot)Samuel(at)netapp(dot)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