Re: Snapshot as Backup

Hello,

I'm using Red Hat Enterprise v2.1 and PostgreSQL v7.3.4. Our hardware
setup includes a large Storage Area Network (SAN). The systems folks
are going to utilize a snapshot type backup to backup the file system.
This snapshot will include PGDATA and all the PostgreSQL files. My
question is if I restore this snapshot will PostgreSQL work? I know if
doing a typical file system backup of PostgreSQL I must first stop the
postmaster or the backup will not produce a useable PostgreSQL system.
Is it necessary to stop the postmaster for this snapshot backup also?

Thanks,

Ed Murphy
The University of Arizona

MessageThe consensus from previous discussions (search for 'LVM' in the
archives) is essentially that it definitely *should* work, some people *do*
use it successfully, but that you *must* test it thoroughly in your own
setup under heavy write load before relying on it.

PG will believe it has 'crashed' when you start it from a restored snapshot,
and PG is designed to recover perfectly well from crashes. If you stop the
postmaster before the snapshot is taken then it will definitely work fine.

Matt
-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Ed Murphy
Sent: 13 January 2004 17:16
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] Snapshot as Backup

Hello,

I'm using Red Hat Enterprise v2.1 and PostgreSQL v7.3.4. Our hardware
setup includes a large Storage Area Network (SAN). The systems folks are
going to utilize a snapshot type backup to backup the file system. This
snapshot will include PGDATA and all the PostgreSQL files. My question is
if I restore this snapshot will PostgreSQL work? I know if doing a typical
file system backup of PostgreSQL I must first stop the postmaster or the
backup will not produce a useable PostgreSQL system. Is it necessary to
stop the postmaster for this snapshot backup also?

Thanks,

Ed Murphy
The University of Arizona

Dear Ed Murphy ,

> and all the PostgreSQL files. My question is if I restore this
> snapshot will PostgreSQL work? I know if doing a typical file system
> backup of PostgreSQL I must first stop the postmaster or the backup
> will not produce a useable PostgreSQL system. Is it necessary to stop
> the postmaster for this snapshot backup also?
>
Yes if the snapshot (filesystem) backup and restore is performed when
postmaster is NOT running you can reproduce it.
I have done this with command
dump <-- for file system backup
Filesystem ext3

Regards
Vishal Kashyap

On Wed, 14 Jan 2004 02:00:45 +0530
Sai Hertz And Control Systems <sank89(at)sancharnet(dot)in> wrote:

>
> > and all the PostgreSQL files. My question is if I restore this
> > snapshot will PostgreSQL work? I know if doing a typical file
> > system backup of PostgreSQL I must first stop the postmaster or the
> > backup will not produce a useable PostgreSQL system. Is it
> > necessary to stop the postmaster for this snapshot backup also?
> >

I've done testing on this. It works fine.
Just make sure you're doing a filesystem snapshot and not a "cp".
To PG it just looks like the power went out or some other failure.
It'll replay parts of its log and be on its merry way. This is *much*
faster than using pg_dump / pg_restore, especially on huge db's.

If you are using xfs you may want to wrap the snapshot call with
xfs_freeze/unfreeze.

It may also be nice every once in a while to do a normal pg_dump..

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/