Re: Off-site storage for PITR logs

From: Nicos Panayides <nicos(at)magneta(dot)com(dot)cy>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Off-site storage for PITR logs
Date: 2010-03-15 09:27:03
Message-ID: 4B9DFD67.3050106@magneta.com.cy
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.
>

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Simon Riggs 2010-03-15 09:57:01 Re: Off-site storage for PITR logs
Previous Message seetharaman jayaraman 2010-03-15 09:13:42 how to get notification in front end application when ever postgre DB table is modified, any tool, jar, api available?