LVM snapshots

Has anyone tried taking an LVM snapshot of a running DB? And then reverting
to the snapshot? I presume one would need to take a low level backup (i.e.
using dd, not tar or cp) of the snapshot to preserve the precise FS layout,
but are there any other issues?

I'm thinking of trying this slightly loony experiment on Postgres v 7.2.3
running on RedHat 7.3, with ext3 as the filesystem. The only 'gotchas' I
can think of at the moment are:

1. the need to preserve the FS layout precisely
2. the minor pauses that occur while LVM freezes the FS to bring the
snapshot online and offline.
3. the need to have the data directory and the pg_xlog directory on the
same logical volume so they get snapshotted together
4. the fact that the on-disk data is much larger than the output of
pg_dump, so backing up the snapshot will require quite a bit of IO

Any others I've missed? If it works then it'd be a fairly neat way to keep
a recent consistent copy of the DB around without having to run pg_dump
every 10 minutes, which chews CPU like nobody's business.

Thanks in advance for any comments.

Matt

Matt Clark
Ymogen Ltd
matt(at)ymogen(dot)net
corp.ymogen.net

On Fri, 14 Mar 2003, Matt Clark wrote:

> Has anyone tried taking an LVM snapshot of a running DB?

I don't think there's a guarantee that a snapshot of the file system
corresponds to a consistent database, even if the snapshot corresponds
to a single point-in-time.

--
David.

I *think* it is guaranteed with WAL, otherwise the system could fail to recover after a system crash. At least that's how I read
section 11.1.1 of the docs:

http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=wal.html

Could freezing of the filesystem result in the WAL being fsynced at an 'inappropriate' time? Is there ever an inappropriate time to
fsync the WAL?

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of David F. Skoll
> Sent: 14 March 2003 15:52
> To: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] LVM snapshots
>
>
> On Fri, 14 Mar 2003, Matt Clark wrote:
>
> > Has anyone tried taking an LVM snapshot of a running DB?
>
> I don't think there's a guarantee that a snapshot of the file system
> corresponds to a consistent database, even if the snapshot corresponds
> to a single point-in-time.
>
> --
> David.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>

"Matt Clark" <matt(at)ymogen(dot)net> writes:
> Has anyone tried taking an LVM snapshot of a running DB? And then reverting
> to the snapshot? I presume one would need to take a low level backup (i.e.
> using dd, not tar or cp) of the snapshot to preserve the precise FS layout,
> but are there any other issues?

This was discussed only a couple weeks ago (in the context of a similar
facility in NetApp servers, but I believe LVM was mentioned). See the
archives.

regards, tom lane

A search for 'netapp' or 'lvm' doesn't seem to throw anything relevant up on any of the lists, and I can't *see* any likely threads
in this group for Jan, Feb or March. I'm probably just being blind - can anyone recall the Subject: of the thread?

Cheers

Matt

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 14 March 2003 17:17
> To: Matt Clark
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] LVM snapshots
>
>
> "Matt Clark" <matt(at)ymogen(dot)net> writes:
> > Has anyone tried taking an LVM snapshot of a running DB? And then reverting
> > to the snapshot? I presume one would need to take a low level backup (i.e.
> > using dd, not tar or cp) of the snapshot to preserve the precise FS layout,
> > but are there any other issues?
>
> This was discussed only a couple weeks ago (in the context of a similar
> facility in NetApp servers, but I believe LVM was mentioned). See the
> archives.
>
> regards, tom lane
>

Here is the subject from my post on 2/24/03:

Re: [GENERAL] A few questions to real pgsql gurus

--On Friday, March 14, 2003 17:46:24 +0000 Matt Clark <matt(at)ymogen(dot)net>
wrote:

> A search for 'netapp' or 'lvm' doesn't seem to throw anything relevant up
> on any of the lists, and I can't *see* any likely threads in this group
> for Jan, Feb or March. I'm probably just being blind - can anyone recall
> the Subject: of the thread?
>
> Cheers
>

> Matt
>
>
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>> Sent: 14 March 2003 17:17
>> To: Matt Clark
>> Cc: pgsql-admin(at)postgresql(dot)org
>> Subject: Re: [ADMIN] LVM snapshots
>>
>>
>> "Matt Clark" <matt(at)ymogen(dot)net> writes:
>> > Has anyone tried taking an LVM snapshot of a running DB? And then
>> > reverting to the snapshot? I presume one would need to take a low
>> > level backup (i.e. using dd, not tar or cp) of the snapshot to
>> > preserve the precise FS layout, but are there any other issues?
>>
>> This was discussed only a couple weeks ago (in the context of a similar
>> facility in NetApp servers, but I believe LVM was mentioned). See the
>> archives.
>>
>> regards, tom lane
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

"Matt Clark" <matt(at)ymogen(dot)net> writes:
> Could freezing of the filesystem result in the WAL being fsynced at an
> 'inappropriate' time? Is there ever an inappropriate time to fsync
> the WAL?

If there were it'd be a bug ...

regards, tom lane