X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 016459DCA43 for ; Thu, 22 Dec 2005 17:09:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56105-02 for ; Thu, 22 Dec 2005 17:09:31 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.196]) by postgresql.org (Postfix) with ESMTP id 7E4789DC8D9 for ; Thu, 22 Dec 2005 17:09:28 -0400 (AST) Received: by zproxy.gmail.com with SMTP id 13so533024nzn for ; Thu, 22 Dec 2005 13:09:30 -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=PC59JdoV3GhZHK0rZ/oudem36Aopb5/KFb9+p51QKg4p+5TKuRaONAYjUd6sIBG7Azmcev/+DHWpbPq7rqS+vBPPbMtz4ZHsv053Nc3d6TLCvJFdKa21xc3il8W6ITzKcRZc11PLnZHmN/4qv1gsAl7C3gtN3fULsC4MLVmUCjg= Received: by 10.65.115.20 with SMTP id s20mr485858qbm; Thu, 22 Dec 2005 13:09:28 -0800 (PST) Received: by 10.65.160.11 with HTTP; Thu, 22 Dec 2005 13:09:28 -0800 (PST) Message-ID: Date: Fri, 23 Dec 2005 10:09:28 +1300 From: Mike C To: Simon Riggs Subject: Re: WAL and pg_dump Cc: pgsql-admin@postgresql.org In-Reply-To: <1135208028.2964.329.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13963_7629425.1135285768406" References: <1135208028.2964.329.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.089 required=5 tests=[AWL=0.088, HTML_MESSAGE=0.001] X-Spam-Score: 0.089 X-Spam-Level: X-Archive-Number: 200512/279 X-Sequence-Number: 19999 ------=_Part_13963_7629425.1135285768406 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 12/22/05, Simon Riggs wrote: > > On Tue, 2005-12-13 at 11:18 +1300, Mike C wrote: > > All the documentation I've seen for PITR points to having to do a file > > system copy of sorts. > > Yes. There's no other way, but why would you want another way? Mainly because a pg_dump backup takes up a lot less disk space than a filesystem copy (in my case, approx 3.5GB backup from a 25GB db). > Is it possible to have a weekly pg_dump and restore the database up to > > the last archived WAL file if the db server fails? > > No. > > pg_dump only copies the data, not the current state of the database. I guess if pg_dump was modified to record the last complete transaction id (in archive format maybe) then the WAL files could be used? Thanks for the response, Mike C. ------=_Part_13963_7629425.1135285768406 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 12/22/05, Simon Riggs <simon@2ndquadrant.com> wrote:
On Tue, 2005-12-13 at 11:18 +1300, Mike C wrote:
> All the documentat= ion I've seen for PITR points to having to do a file
> system copy of= sorts.

Yes. There's no other way, but why would you want another wa= y?

Mainly because a pg_dump backup takes up a lot less disk space than a filesystem copy (in my case, approx 3.5GB backup from a 25GB db). 
> Is it = possible to have a weekly pg_dump and restore the database up to
> the last archived WAL file if the db server fails?

No.
<= br>pg_dump only copies the data, not the current state of the database.

I guess if pg_dump was modified to record the last complete transaction id (in archive format maybe) then the WAL files could be used?
 
Thanks for the response,

Mike C.
------=_Part_13963_7629425.1135285768406--