Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 367246405BD for ; Mon, 15 Mar 2010 06:27:20 -0300 (ADT) 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 70032-03 for ; Mon, 15 Mar 2010 09:27:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.netmail.com.cy (mail.netmail.com.cy [82.102.64.39]) by mail.postgresql.org (Postfix) with ESMTP id 8593D638A04 for ; Mon, 15 Mar 2010 06:27:09 -0300 (ADT) Received: from mail.magneta.com.cy (mail.magneta.com.cy [82.102.77.57]) by mail.netmail.com.cy (Postfix) with ESMTP id F04B81A264E5; Mon, 15 Mar 2010 11:27:27 +0200 (EET) Received: from [10.1.1.244] (unknown [10.1.0.2]) by mail.magneta.com.cy (Postfix) with ESMTPSA id 921B8A5603; Mon, 15 Mar 2010 11:27:05 +0200 (EET) Message-ID: <4B9DFD67.3050106@magneta.com.cy> Date: Mon, 15 Mar 2010 11:27:03 +0200 From: Nicos Panayides User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3 MIME-Version: 1.0 To: Simon Riggs CC: pgsql-admin@postgresql.org Subject: Re: Off-site storage for PITR logs References: <4B9A6E8B.4050303@magneta.com.cy> <1268556612.3825.5025.camel@ebony> In-Reply-To: <1268556612.3825.5025.camel@ebony> 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=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/93 X-Sequence-Number: 33892 Hi Simon, thanks for the suggestion. What kind of space savings should I expected from turning off full_page_writes? The servers have battery-backed write-caches for the disk controllers, so it should be safe to give this a try. Does turning off full_page_writes have any other effect on the database? Regards, Nicos Panayides IT Manager Magneta Technologies Ltd Tel: +357 22721919, 22317400 Fax: +357 22721917 Web: http://www.magneta.eu On 14/03/2010 10:50, Simon Riggs wrote: > On Fri, 2010-03-12 at 18:40 +0200, Nicos Panayides wrote: > >> I am planning an off-site backup solution for a fairly busy >> (mostly-write) 8.3 database. The database is currently about 200GB in >> size. I though about using log shipping and cold standby since it's easy >> in terms of administration and also offers point in time recovery. >> >> 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! >> >> 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? > > Consider turning off full_page_writes which will permanently minimise > the amount of WAL traffic, as well as being an integrated and > fully-supported option with Postgres. You will need additional > non-volatile cache on your disks to ensure that is a safe option, though > that will yield performance advantages also. >