Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id DDE5B9FB462 for ; Fri, 16 Feb 2007 11:07:36 -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 64526-01 for ; Fri, 16 Feb 2007 11:07:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from perth.mailnetwork.co.uk (perth.mailnetwork.co.uk [80.175.28.46]) by postgresql.org (Postfix) with ESMTP id 0A5D79FB427 for ; Fri, 16 Feb 2007 11:07:28 -0400 (AST) Received: from user-3e88bf95.telcl18.dsl.pol.co.uk ([62.136.191.149]:1284 helo=[10.100.9.2]) by perth.mailnetwork.co.uk with esmtpa (Exim 4.66) (envelope-from ) id 1HI4gI-0000LO-JQ; Fri, 16 Feb 2007 15:07:26 +0000 Message-ID: <45D5C8A9.8090002@mailnetwork.co.uk> Date: Fri, 16 Feb 2007 15:07:21 +0000 From: "Andy Shellam (Mailing Lists)" User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: Chad Wagner CC: "Eduardo J. Ortega" , pgsql-admin@postgresql.org Subject: Re: WAL files backup References: <945295.11491.qm@web55414.mail.re4.yahoo.com> <200702152235.57473.ejortegau@cable.net.co> <81961ff50702160334x411e3aeau76ca57c628e0f45e@mail.gmail.com> In-Reply-To: <81961ff50702160334x411e3aeau76ca57c628e0f45e@mail.gmail.com> Content-Type: multipart/alternative; boundary="------------050509080809010402040909" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.607 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/233 X-Sequence-Number: 24698 This is a multi-part message in MIME format. --------------050509080809010402040909 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Chad Wagner wrote: > On 2/15/07, *Eduardo J. Ortega* > wrote: > > After erasing the "less than names" WAL files, we add to tar the > remaining > WAL records (0003B, 0003C and so on on the example). The more WAL > files you > have after 0003B, the more up to date DB you get after restore > (since it has > more WAL files indicating more transactions that took place after > the backup. > > > Why bother trying to delete WAL files older than the .backup file? > When PostgreSQL is in recovery mode it knows which WAL files are > necessary to perform the recovery. > > Also, the documentation recommends excluding the pg_xlog directory > when performing the base backup. Likely when it comes time to > recovery the online WAL files have been archived already, so it is a > risk of confusion I am sure. If the OP is doing the same as myself, the WAL files are being archived outside of pg_xlog (indeed outside of the PG data cluster) - it makes no sense keeping around WAL files older than the .backup file because they're not needed - in a day I generate ~5GB worth of WAL files which aren't needed after the full backup runs at 2am, so it's a waste of resources to keep them around or to worry about backing them up after this time. Andy. --------------050509080809010402040909 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Chad Wagner wrote:
On 2/15/07, Eduardo J. Ortega <ejortegau@cable.net.co> wrote:
After erasing the "less than  names" WAL files, we add to tar the remaining
WAL records (0003B, 0003C  and so on on the example). The more WAL files you
have after 0003B, the more up to date DB you get after restore (since it has
more WAL files indicating more transactions that took place after the backup.

Why bother trying to delete WAL files older than the .backup file?  When PostgreSQL is in recovery mode it knows which WAL files are necessary to perform the recovery.

Also, the documentation recommends excluding the pg_xlog directory when performing the base backup.  Likely when it comes time to recovery the online WAL files have been archived already, so it is a risk of confusion I am sure.

If the OP is doing the same as myself, the WAL files are being archived outside of pg_xlog (indeed outside of the PG data cluster) - it makes no sense keeping around WAL files older than the .backup file because they're not needed - in a day I generate ~5GB worth of WAL files which aren't needed after the full backup runs at 2am, so it's a waste of resources to keep them around or to worry about backing them up after this time.

Andy.
--------------050509080809010402040909--