Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 9BA5B9FA19A for ; Mon, 4 Dec 2006 10:05:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 55507-02 for ; Mon, 4 Dec 2006 10:04:49 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 5CA889FA0FB for ; Mon, 4 Dec 2006 10:05:00 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so2679994ugf for ; Mon, 04 Dec 2006 06:04:58 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ez33w9vOiAT9Rh7C6nxoOlutI32msv0RZ6io2NLrsEbc6RppzJiw/kvtoEXwfgu0H+OfwMO0lpEsR+U1FaEn+nz/Gey2L4hroI//2op0/G5wJklat9Ofu93IYafYOuZWyAPwlHN1DjyCK95IhpwUWOyRkryBp6Q53vtp3767qhQ= Received: by 10.78.203.13 with SMTP id a13mr7670913hug.1165241097626; Mon, 04 Dec 2006 06:04:57 -0800 (PST) Received: by 10.78.105.20 with HTTP; Mon, 4 Dec 2006 06:04:57 -0800 (PST) Message-ID: Date: Mon, 4 Dec 2006 09:04:57 -0500 From: "Travis Whitton" To: "Markus Schaber" Subject: Re: pg_xlog on separate drive Cc: pgsql-sql@postgresql.org In-Reply-To: <4573FEE0.2010406@logix-tt.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_30869_21842857.1165241097530" References: <4573FEE0.2010406@logix-tt.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/41 X-Sequence-Number: 26997 ------=_Part_30869_21842857.1165241097530 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks for the replies guys. I think I may be ok in my case though because I'll be importing data as a single daily batch from raw data. I'll be taking nightly backups, and in the event of a crash, I can simply restore from a recent backup and then reimport the raw data. I can now see why losing pg_xlog would be a big problem if I were inserting and updating data continuously throughout the day though. Thanks, Travis On 12/4/06, Markus Schaber wrote: > > Hi, Travis, > > Travis Whitton wrote: > > Hey guys, sorry if this is slightly OT for this list, but I figure it's > > a simple question. If I'm storing pg_xlog on a second non-redundant > > drive using the symlink method and the journal drive were to crash, how > > difficult is recovery? Will Postgresql simply be able to reinitialize > > the journal on a new drive and carry on, or is there more to it than > > that? I realize any pending transactions would be lost, but that's not a > > huge concern for me because everything I'm importing comes from raw > data. > > The problem is that you risk inconsistency at data and structural level. > > When the server crashes, it might happen that some pages in the data > files are written only partially (because most disks have a much smaller > blocksize than the PostgreSQL page size (which is 8k by default)). > > Now, when the server cannot reply the WAL log, those half-written pages > will not be repaired, and your data may be inconsistent at a very low > sematic level (duplicate rows, missing rows, broken rows, backend > crashes etc.) with no way to repair. > > HTH, > Markus > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in Europe! www.ffii.org > www.nosoftwarepatents.org > ------=_Part_30869_21842857.1165241097530 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks for the replies guys. I think I may be ok in my case though because I'll be importing data as a single daily batch from raw data. I'll be taking nightly backups, and in the event of a crash, I can simply restore from a recent backup and then reimport the raw data. I can now see why losing pg_xlog would be a big problem if I were inserting and updating data continuously throughout the day though.

Thanks,
Travis

On 12/4/06, Markus Schaber <schabi@logix-tt.com> wrote:
Hi, Travis,

Travis Whitton wrote:
> Hey guys, sorry if this is slightly OT for this list, but I figure it's
> a simple question. If I'm storing pg_xlog on a second non-redundant
> drive using the symlink method and the journal drive were to crash, how
> difficult is recovery? Will Postgresql simply be able to reinitialize
> the journal on a new drive and carry on, or is there more to it than
> that? I realize any pending transactions would be lost, but that's not a
> huge concern for me because everything I'm importing comes from raw data.

The problem is that you risk inconsistency at data and structural level.

When the server crashes, it might happen that some pages in the data
files are written only partially (because most disks have a much smaller
blocksize than the PostgreSQL page size (which is 8k by default)).

Now, when the server cannot reply the WAL log, those half-written pages
will not be repaired, and your data may be inconsistent at a very low
sematic level (duplicate rows, missing rows, broken rows, backend
crashes etc.) with no way to repair.

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

------=_Part_30869_21842857.1165241097530--