Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A78D7633829 for ; Fri, 12 Mar 2010 13:20:23 -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 96225-05 for ; Fri, 12 Mar 2010 17:20:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id BF782632FB5 for ; Fri, 12 Mar 2010 13:20:12 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 12 Mar 2010 11:20:11 -0600 Message-Id: <4B9A2367020000250002FCEC@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Fri, 12 Mar 2010 11:20:07 -0600 From: "Kevin Grittner" To: "Nicos Panayides" , Subject: Re: Off-site storage for PITR logs References: <4B9A6E8B.4050303@magneta.com.cy> In-Reply-To: <4B9A6E8B.4050303@magneta.com.cy> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.457 tagged_above=-10 required=5 tests=AWL=0.142, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/81 X-Sequence-Number: 33880 Nicos Panayides wrote: > The database generates about 3 PITR log files per minute. If my > calculations are correct the sites need to be connected with a > 7MBit connection and the logs will need about 68GB of storage per > day! I'd have put the minimum line speed at 8Mb/second, but I figure 10 bits per byte to allow for parity and packet overhead, and the assumption that I might want to use the line for something else (like monitoring) at the same time; but yeah, those numbers are in the right ballpark. > Does anyone have any suggestions on how to significantly reduce > the volume of log files or recommend another off-site backup > solution that would require less bandwidth and storage? We stream the WAL files through gzip in our archive script. That reduces them to 4MB to 8MB during normal usage, and 12MB to 15MB during our routine database vacuums. If you have relatively idle periods during which the log is pushed by archive_timeout, you should filter through pg_clearxlogtail or pglesslog before using gzip; the former yields a 16kB file during idle periods. (I'm not sure about the other, but it should be capable of getting even smaller.) You'll need to determine how far back it's valuable to have the "point in time recovery" capabilities. We do weekly base backups and keep WAL files to restore forward from the earlier to current, but then keep monthly "archival" backups, where each base backup is kept with just the WAL files needed to successfully restore it. -Kevin