Re: Core team statement on replication in PostgreSQL

On Fri, May 30, 2008 at 6:47 PM, Andreas 'ads' Scherbaum
<adsmail(at)wars-nicht(dot)de> wrote:
> On Fri, 30 May 2008 17:05:57 -0400 Andrew Dunstan wrote:
>> Andreas 'ads' Scherbaum wrote:
>> > On Thu, 29 May 2008 23:02:56 -0400 Andrew Dunstan wrote:
>> >
>> >> Well, yes, but you do know about archive_timeout, right? No need to wait
>> >> 2 hours.
>> >
>> > Then you ship 16 MB binary stuff every 30 second or every minute but
>> > you only have some kbyte real data in the logfile. This must be taken
>> > into account, especially if you ship the logfile over the internet
>> > (means: no high-speed connection, maybe even pay-per-traffic) to the
>> > slave.
>>
>> Sure there's a price to pay. But that doesn't mean the facility doesn't
>> exist. And I rather suspect that most of Josh's customers aren't too
>> concerned about traffic charges or affected by such bandwidth
>> restrictions. Certainly, none of my clients are, and they aren't in the
>> giant class. Shipping a 16Mb file, particularly if compressed, every
>> minute or so, is not such a huge problem for a great many commercial
>> users, and even many domestic users.
>
> The real problem is not the 16 MB, the problem is: you can't compress
> this file. If the logfile is rotated it still contains all the
> old binary data which is not a good starter for compression.

Using bzip2 in my archive_command script, my WAL files are normally
compressed to between 2MB and 5MB, depending on the write load
(larger, and more of them, in the middle of the day). bzip2
compression is more expensive and rotated WAL files are not
particularly compressable to be sure, but due to (and given) the
nature of the data bzip2 works pretty well, and much better than gzip.

>
> So you may have some kB changes in the wal logfile every minute but you
> still copy 16 MB data. Sure, it's not so much - but if you rotate a
> logfile every minute this still transfers 16*60*24 = ~23 GB a day.
>

I archived 1965 logs yesterday on one instance of my app totalling
8.5GB ... not to bad, really.

--
Mike Rylander
| VP, Research and Design
| Equinox Software, Inc. / The Evergreen Experts
| phone: 1-877-OPEN-ILS (673-6457)
| email: miker(at)esilibrary(dot)com
| web: http://www.esilibrary.com