Re: Off-site storage for PITR logs

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