pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
To: Jeff <threshar@torgo.978.org>
To: Josh Berkus <josh@agliodbs.com>
Cc: markw@osdl.org
Cc: pgsql-performance@postgresql.org
Cc: linux-lvm@sistina.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [PERFORM] backup/restore - another area.
Date: Fri, 17 Oct 2003 10:45:37 -0400
Message-ID: <2D92FEBFD3BE1346A6C397223A8DD3FC09243E@THOR.goeci.com> (raw)

There's been a lot of discussion on the ADMIN list about postgresql backups
from LVM snapshots:
http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=LVM+snapshot&q=b

Note that the existence of the snapshot slows the original filesystem down,
so you want to minimize the duration for which the snapshot exists. A two
phase rsync -- first off the "live" filesystem, second off the snapshot --
to a backup filesystem accomplishes this. If you don't have the capacity to
duplicate your $PGDATA folder, you'll want to consider doing incremental
backups with xfsdump. Since xfs and xfsdump has been around forever, so you
can bet your data on them; of course, xfsdump requires that pgdata be hosted
on an xfs volume (XFS is a fast filesystem with metadata journaling, giving
fast crash recovery, so this is a good idea anyway). For other filesystems,
incremental backup and restores are being developed in star:
http://freshmeat.net/projects/star/. star also claims to be faster than gnu
tar, so you might use it even if you aren't doing incremental backups.

The latest version of my backup script is attached. It demonstrates the
implementation of filesystem level backup of the postgresql data cluster
(the $PGDATA folder) using two-phase rsync. The $PGDATA folder is on an xfs
formatted LVM volume; various checks are done before and after backup, and
errors are e-mailed to a specified account. The script handles situations
where (i) the XFS filesystem containing $PGDATA has an external log and (ii)
the postmaster log ($PGDATA/pg_xlog) is written to a filesystem different
than the one containing the $PGDATA folder. These configurations enhance
database performance, though an external XFS log is not a big win for
postgresql, which creates relatively few new files and deletes relatively
few files (relative to an e-mail server). It should be possible, using this
script, to keep backup times below 10 minutes even for very high loads -
just increase the frequency at which you do backups (it has been tested to
run hourly, and runs every three hours on the production server).

Cheers,
	Murthy

(Note: I have experienced filesystem hangs within 2 days to a week, from
running this script frequently with XFS versions including XFS 1.3. The XFS
CVS kernel from Sep 30th 2003 seems not to have this problem - I have been
running this script hourly for two weeks without problems. So you might
either use a CVS kernel or wait for an XFS release based on linux 2.4.22 or
later; and you will be testing your own setup, right?!)






>-----Original Message-----
>From: Jeff [mailto:threshar@torgo.978.org]
>Sent: Thursday, October 16, 2003 13:37
>To: Josh Berkus
>Cc: markw@osdl.org; pgsql-performance@postgresql.org;
>linux-lvm@sistina.com; pgsql-admin@postgresql.org
>Subject: Re: [ADMIN] [PERFORM] backup/restore - another area.
>
>
>On Thu, 16 Oct 2003 10:09:27 -0700
>Josh Berkus <josh@agliodbs.com> wrote:
>
>> Jeff,
>> 
>> > I left the DB up while doing this.
>> >
>> > Even had a program sitting around committing data to try 
>and corrupt
>> > things. (Which is how I discovered I was doing the snapshot wrong)
>> 
>> Really?   I'm unclear on the method you're using to take the 
>snapshot,
>> then; I seem to have missed a couple posts on this thread.   Want to
>> refresh me?
>> 
>
>I have a 2 disk stripe LVM on /dev/postgres/pgdata/
>
>lvcreate -L4000M -s -n pg_backup /dev/postgres/pgdata
>mount /dev/postgres/pg_backup /pg_backup 
>tar cf - /pg_backup | gzip -1 > /squeegit/mb.backup 
>umount /pg_backup;
>lvremove -f /dev/postgres/pg_backup;
>
>In a nutshell an LVM snapshot is an atomic operation that 
>takes, well, a
>snapshot of hte FS as it was at that instant.  It does not make a 2nd
>copy of the data.   This way you can simply tar up the pgdata directory
>and be happy as the snapshot will not be changing due to db activity.
>
>-- 
>Jeff Trout <jeff@jefftrout.com>
>http://www.jefftrout.com/
>http://www.stuarthamm.net/
>
>---------------------------(end of 
>broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index 
>scan if your
>      joining column's datatypes do not match
>

Attachments:

  pgSnapBack3.generic (application/octet-stream, 10.0K) download

view thread (14+ messages)  latest in thread

Message-ID: <2D92FEBFD3BE1346A6C397223A8DD3FC09243E@THOR.goeci.com>
Permalink:  ../2D92FEBFD3BE1346A6C397223A8DD3FC09243E@THOR.goeci.com/
Also on:    postgresql.org/message-id/2D92FEBFD3BE1346A6C397223A8DD3FC09243E@THOR.goeci.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: murthy.kambhampaty@goeci.com, threshar@torgo.978.org, josh@agliodbs.com, markw@osdl.org, linux-lvm@sistina.com, pgsql-admin@postgresql.org
  Subject: Re: [PERFORM] backup/restore - another area.
  In-Reply-To: <2D92FEBFD3BE1346A6C397223A8DD3FC09243E@THOR.goeci.com>
  References: <2D92FEBFD3BE1346A6C397223A8DD3FC09243E@THOR.goeci.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox