From ArnoldS@osti.gov Tue Mar  2 15:43:29 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6C88163359D;
	Tue,  2 Mar 2010 11:43:29 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024)
	with ESMTP id 90377-07; Tue,  2 Mar 2010 15:43:18 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from ostiiron1.osti.gov (ostiiron1.osti.gov [192.107.175.91])
	by mail.postgresql.org (Postfix) with ESMTP id 9AA0B6329A7;
	Tue,  2 Mar 2010 11:43:18 -0400 (AST)
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AqEEAB3BjEusEIFu/2dsb2JhbAAkm1G/PIR7BIMX
X-IronPort-AV: E=Sophos;i="4.49,567,1262545200"; d="scan'208,217";a="3108810"
Received: from ostiex01.osti.gov ([172.16.129.110])
	by ostiiron1.osti.gov with ESMTP; 02 Mar 2010 10:43:12 +0500
Received: from OSTIEX01.osti.gov ([::1]) by OSTIEX01.osti.gov ([::1]) with
	mapi; Tue, 2 Mar 2010 10:43:07 -0500
From: "Arnold, Sandra" <ArnoldS@osti.gov>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Date: Tue, 2 Mar 2010 10:43:07 -0500
Subject: The OS Command for pg_hotbackup -- Use lvmsnapshot instead of tar
 cvzf
Thread-Topic: The OS Command for pg_hotbackup -- Use lvmsnapshot instead of
	tar cvzf
Thread-Index: Acq6Hw3jKfalE2C7TtWD0t3Dhj+/hw==
Message-ID: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_54E5385D47A89542BC678F5426799F3904961EF60FOSTIEX01ostig_"
MIME-Version: 1.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5
	tests=BAYES_50=0.001, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/13
X-Sequence-Number: 33812

--_000_54E5385D47A89542BC678F5426799F3904961EF60FOSTIEX01ostig_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I am currently using the pg_hotbackup Perl script to backup my production P=
ostgreSQL database.  However, the next production database is going to be c=
lose to a terrabyte in size.  We feel that a tar cvzf command is not the co=
rrect OS backup solution to use.  Instead we would like to use lvmsnapshot.=
  Is anyone using the pg_hotbackup script but instead of the tar cvzf OS co=
mmand are they using lvmsnapshot?

Or, is anyone using lvmsnapshot and doing point-in-time recoveries?

Thanks,

Sandra Arnold
Sr. Database Administrator
DOE/OSTI
Oak Ridge, TN

--_000_54E5385D47A89542BC678F5426799F3904961EF60FOSTIEX01ostig_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dus-ascii" http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18876"></HEAD>
<BODY>
<DIV><FONT size=3D2 face=3DArial><SPAN class=3D876543715-02032010>I am curr=
ently using=20
the pg_hotbackup Perl script to backup my production PostgreSQL database.&n=
bsp;=20
However, the next production database is going to be close to a terrabyte i=
n=20
size.&nbsp; We feel that a tar cvzf command is not the correct OS backup=20
solution to use.&nbsp; Instead we would like to use lvmsnapshot.&nbsp; Is a=
nyone=20
using the pg_hotbackup script but instead of&nbsp;the tar cvzf OS command a=
re=20
they using lvmsnapshot?</SPAN></FONT></DIV>
<DIV><FONT size=3D2 face=3DArial><SPAN=20
class=3D876543715-02032010></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial><SPAN class=3D876543715-02032010>Or, is an=
yone using=20
lvmsnapshot and doing point-in-time recoveries?</SPAN></FONT></DIV>
<DIV><FONT size=3D2 face=3DArial><SPAN=20
class=3D876543715-02032010></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial><SPAN=20
class=3D876543715-02032010>Thanks,</SPAN></FONT></DIV><SPAN=20
class=3D876543715-02032010>
<DIV><BR><FONT size=3D2 face=3DArial>Sandra Arnold</FONT></DIV>
<DIV><SPAN class=3D876543715-02032010><FONT size=3D2 face=3DArial>Sr. Datab=
ase=20
Administrator</FONT></SPAN></DIV>
<DIV><SPAN class=3D876543715-02032010><FONT size=3D2=20
face=3DArial>DOE/OSTI</FONT></SPAN></DIV>
<DIV><SPAN class=3D876543715-02032010><FONT size=3D2 face=3DArial>Oak Ridge=
,=20
TN</FONT></SPAN></SPAN></DIV></BODY></HTML>

--_000_54E5385D47A89542BC678F5426799F3904961EF60FOSTIEX01ostig_--



From chander@otg-nc.com Tue Mar  2 18:15:32 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id DFFFF63344C
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue,  2 Mar 2010 14:15:32 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 16106-09 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue,  2 Mar 2010 18:15:22 +0000 (UTC)
X-Greylist: delayed 00:05:24.195544 by SQLgrey-1.7.6
Received: from smtp172.iad.emailsrvr.com (smtp172.iad.emailsrvr.com
	[207.97.245.172])
	by mail.postgresql.org (Postfix) with ESMTP id B08C9632505
	for <pgsql-admin@postgresql.org>; Tue,  2 Mar 2010 14:14:59 -0400 (AST)
Received: from relay7.relay.iad.mlsrvr.com (localhost [127.0.0.1])
	by relay7.relay.iad.mlsrvr.com (SMTP Server) with ESMTP id 832C21DE602;
	Tue,  2 Mar 2010 13:09:34 -0500 (EST)
Received: by relay7.relay.iad.mlsrvr.com (Authenticated sender:
	chander-AT-otg-nc.com) with ESMTPSA id 498971DD892;
	Tue,  2 Mar 2010 13:09:34 -0500 (EST)
Message-ID: <4B8D545D.80405@otg-nc.com>
Date: Tue, 02 Mar 2010 13:09:33 -0500
From: Chander Ganesan <chander@otg-nc.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: "Arnold, Sandra" <ArnoldS@osti.gov>
CC: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: The OS Command for pg_hotbackup -- Use lvmsnapshot instead
	of tar cvzf
References: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
In-Reply-To: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 201003/17
X-Sequence-Number: 33816

Arnold, Sandra wrote:
> I am currently using the pg_hotbackup Perl script to backup my 
> production PostgreSQL database.  However, the next production database 
> is going to be close to a terrabyte in size.  We feel that a tar cvzf 
> command is not the correct OS backup solution to use.  Instead we 
> would like to use lvmsnapshot.  Is anyone using the pg_hotbackup 
> script but instead of the tar cvzf OS command are they using lvmsnapshot?
I believe that if you take an LVM snapshot and the change size exceeds 
the snapshot size (a configurable value) then the snapshot gets 
released, so you might want to be careful with this solution (I don't 
know how much data you accumulate, or how fast you accumulate it, or if 
that rate changes, etc.).  You'll also need to ensure that you snapshot 
any and all tablespaces (if you use pg_start_backup() first the 
snapshots need not be simultaneous, but they still must be done).

You might consider using rsync instead, it should be a lot faster than 
tar, and doesn't have the same downside as using snapshots.  You'll also 
find that it is fairly widely used in the PostgreSQL community.

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Expert PostgreSQL, Python, PHP, and other open source training.




From scott.marlowe@gmail.com Tue Mar  2 19:20:24 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 713C7632505;
	Tue,  2 Mar 2010 15:20:24 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024)
	with ESMTP id 98614-07; Tue,  2 Mar 2010 19:20:09 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com
 [74.125.82.46])
	by mail.postgresql.org (Postfix) with ESMTP id 1CD926322EF;
	Tue,  2 Mar 2010 15:20:11 -0400 (AST)
Received: by wwa36 with SMTP id 36so316912wwa.19
	for <multiple recipients>; Tue, 02 Mar 2010 11:20:11 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=cdE7OYgP++x7+30gVWkDvzk/mwB80vpwUbHzJxZQXYM=;
	b=uL/mcoDoShZHs6evBHmPwP/aOMwaokh0x9zzjrtCc2QA6flpXs6ypaccn32Zgi8UD3
	Pyocd5CvBAsOSdnB6L5AB4d/UGx6xZ7sIO3ufDpmfEg0txmdbkKVrR7VO/GbpGh2jbOr
	2kkn6omHWsSn6cvXAQNBSVRgNP6NESUFqwhRs=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=SI5yjWrE9TVb500xyvhwuSWxCycprUplq9RGdfMCjyFcpSHSd8T3J1hgvIwEgDukmv
	6RMQGK0WoNJQ5J3uNA6YD+DJ7xhPlo3nfp/lFRE2Jq/DprMN8tgeYMXj1dtmuBHjeC9r
	hCLovY+PCRJscsguobj0nuEAFiWS2oOWwriaM=
MIME-Version: 1.0
Received: by 10.216.87.16 with SMTP id x16mr4383681wee.27.1267557610875; Tue,
	02 Mar 2010 11:20:10 -0800 (PST)
In-Reply-To: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
References: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
Date: Tue, 2 Mar 2010 12:20:10 -0700
Message-ID: <dcc563d11003021120u2cc0ec95ud8802126521e442c@mail.gmail.com>
Subject: Re: The OS Command for pg_hotbackup -- Use lvmsnapshot
	instead of tar cvzf
From: Scott Marlowe <scott.marlowe@gmail.com>
To: "Arnold, Sandra" <ArnoldS@osti.gov>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.465 tagged_above=-10 required=5 tests=AWL=-0.725,
	BAYES_20=-0.74
X-Spam-Level: 
X-Archive-Number: 201003/18
X-Sequence-Number: 33817

On Tue, Mar 2, 2010 at 8:43 AM, Arnold, Sandra <ArnoldS@osti.gov> wrote:
> I am currently using the pg_hotbackup Perl script to backup my production
> PostgreSQL database.=A0 However, the next production database is going to=
 be
> close to a terrabyte in size.=A0 We feel that a tar cvzf command is not t=
he
> correct OS backup solution to use.=A0 Instead we would like to use
> lvmsnapshot.=A0 Is anyone using the pg_hotbackup script but instead of=A0=
the tar
> cvzf OS command are they using lvmsnapshot?
>
> Or, is anyone using lvmsnapshot and doing point-in-time recoveries?

I do not run my databases on LVM because, at least in the past, LVM
did not properly honor fsync / write barrier commands.  I don't know
if this has since  been fixed.   If your transaction rate on LVM is
unnaturally higher than it should / could be, then suspect it is not
fsyncing, and putting your data on it is putting it at risk.



From scott.marlowe@gmail.com Tue Mar  2 19:24:19 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id BF8C0632C4E;
	Tue,  2 Mar 2010 15:24:19 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024)
	with ESMTP id 27398-10; Tue,  2 Mar 2010 19:24:09 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f226.google.com (mail-ew0-f226.google.com
	[209.85.219.226])
	by mail.postgresql.org (Postfix) with ESMTP id 708F0632BD2;
	Tue,  2 Mar 2010 15:24:08 -0400 (AST)
Received: by ewy26 with SMTP id 26so454458ewy.3
	for <multiple recipients>; Tue, 02 Mar 2010 11:24:07 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=B8n/UYAgw25W53T2MNKd/GHzTrLuEr3mrC183Mr+SIM=;
	b=uJ0SXY/Kg6GcKFfQUvZeUssHC6qyYdmM4Zj42Y+7qqJqbmpTwBUCeqUzuGS0Oz3XrN
	7tC7MwX+11UVhc/munid1oXDQZXmDEXZ6PdyX5Lv+gwWY0zxRVzURlnJBrG3p5vmUfvG
	f6SLnAqTghw41TlO0C3wMpXHpxGXiNOPHSVbw=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=cto3TP34XJxptFBlv/+9G8C/aNJqTCKLHoaE9Grya/Yxn+GsBmho6w1DWVfeb+u0AS
	omrht5Owepp9XGsQHLHBJvtQrtQYUQgFwzWc33qYiL2LXD9y9jVbsB8SWE/YHjFuBHE+
	0yzJ6oNZwp20vZU5BlxI02JE8qDnKyuiUWo4U=
MIME-Version: 1.0
Received: by 10.216.156.193 with SMTP id m43mr1189wek.8.1267557846759;
 Tue, 02
	Mar 2010 11:24:06 -0800 (PST)
In-Reply-To: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
References: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
Date: Tue, 2 Mar 2010 12:24:06 -0700
Message-ID: <dcc563d11003021124x2277a0efr78135fe98a652324@mail.gmail.com>
Subject: Re: The OS Command for pg_hotbackup -- Use lvmsnapshot
	instead of tar cvzf
From: Scott Marlowe <scott.marlowe@gmail.com>
To: "Arnold, Sandra" <ArnoldS@osti.gov>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.185 tagged_above=-10 required=5
	tests=BAYES_40=-0.185
X-Spam-Level: 
X-Archive-Number: 201003/19
X-Sequence-Number: 33818

FYI, here's the conversations on the subject of LVM and write barriers
from last year about this time:

http://www.redhat.com/archives/linux-lvm/2009-March/msg00025.html
http://archives.postgresql.org/pgsql-general/2009-03/msg00204.php

Note that it's also considered bad form to post the same question to
multiple lists.



From scott.marlowe@gmail.com Tue Mar  2 19:54:42 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 4407D632EEE;
	Tue,  2 Mar 2010 15:54:42 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024)
	with ESMTP id 03698-08; Tue,  2 Mar 2010 19:54:28 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com
 [74.125.82.46])
	by mail.postgresql.org (Postfix) with ESMTP id 0EF3F632416;
	Tue,  2 Mar 2010 15:54:30 -0400 (AST)
Received: by wwa36 with SMTP id 36so335003wwa.19
	for <multiple recipients>; Tue, 02 Mar 2010 11:54:29 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=aCYlK5pnpk8GWMZkwDorsu0j1n5KCuLyXWtrsCZlI5k=;
	b=mVS2ouAmtG84Ehht5JFp8ayBmSmQf64mpbHm3W1fqvsqb1M8JcghDdZyFmId2bQ1LG
	fKVtan+U8o9PiMCV3RAeGez3O8fz1ljvBJTYumjudB0G8PrwnORNu7UR8vNnu6bri+k7
	oJ3f/JegJfZEvKkXU81QcnvhUWQjGTIYHjUZI=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=MfF5TDB/GsToWVUjVctQAMLceiJyEVuur0Bk5xj1pj2rDVnTDgnCkZW0N46t8vgT6q
	1Y7PkPPYLuwiS9kMNxvq+4pXOZOMRYiNorYJlo4Jinp2YWxLvKGQccPd2OXId6qFD1Xh
	YbaXeIItJmON6P/AZrrSl8t/JNmki59gWmmQI=
MIME-Version: 1.0
Received: by 10.216.91.76 with SMTP id g54mr4647963wef.2.1267559669735; Tue,
	02 Mar 2010 11:54:29 -0800 (PST)
In-Reply-To: <dcc563d11003021120u2cc0ec95ud8802126521e442c@mail.gmail.com>
References: <54E5385D47A89542BC678F5426799F3904961EF60F@OSTIEX01.osti.gov>
	<dcc563d11003021120u2cc0ec95ud8802126521e442c@mail.gmail.com>
Date: Tue, 2 Mar 2010 12:54:29 -0700
Message-ID: <dcc563d11003021154h7da61c9dg2aee52cf08da8f3@mail.gmail.com>
Subject: Re: The OS Command for pg_hotbackup -- Use lvmsnapshot
	instead of tar cvzf
From: Scott Marlowe <scott.marlowe@gmail.com>
To: "Arnold, Sandra" <ArnoldS@osti.gov>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.452 tagged_above=-10 required=5 tests=AWL=-0.712,
	BAYES_20=-0.74
X-Spam-Level: 
X-Archive-Number: 201003/20
X-Sequence-Number: 33819

On Tue, Mar 2, 2010 at 12:20 PM, Scott Marlowe <scott.marlowe@gmail.com> wr=
ote:
> On Tue, Mar 2, 2010 at 8:43 AM, Arnold, Sandra <ArnoldS@osti.gov> wrote:
>> I am currently using the pg_hotbackup Perl script to backup my productio=
n
>> PostgreSQL database.=A0 However, the next production database is going t=
o be
>> close to a terrabyte in size.=A0 We feel that a tar cvzf command is not =
the
>> correct OS backup solution to use.=A0 Instead we would like to use
>> lvmsnapshot.=A0 Is anyone using the pg_hotbackup script but instead of=
=A0the tar
>> cvzf OS command are they using lvmsnapshot?
>>
>> Or, is anyone using lvmsnapshot and doing point-in-time recoveries?
>
> I do not run my databases on LVM because, at least in the past, LVM
> did not properly honor fsync / write barrier commands. =A0I don't know
> if this has since =A0been fixed. =A0 If your transaction rate on LVM is
> unnaturally higher than it should / could be, then suspect it is not
> fsyncing, and putting your data on it is putting it at risk.