Re: [SPAM] Re: Best way to replicate to large number of nodes

2010/4/22 Brian Peschel <brianp(at)occinc(dot)com>:
>
> On 04/22/2010 10:12 AM, Ben Chobot wrote:
>>
>> On Apr 21, 2010, at 1:41 PM, Brian Peschel wrote:
>>
>>
>>>
>>> I have a replication problem I am hoping someone has come across before
>>> and can provide a few ideas.
>>>
>>> I am looking at a configuration of on 'writable' node and anywhere from
>>> 10 to 300 'read-only' nodes.  Almost all of these nodes will be across a WAN
>>> from the writable node (some over slow VPN links too).  I am looking for a
>>> way to replicate as quickly as possible from the writable node to all the
>>> read-only nodes.  I can pretty much guarantee the read-only nodes will never
>>> become master nodes.  Also, the updates to the writable node are bunched and
>>> at known times (ie only updated when I want it updated, not constant
>>> updates), but when changes occur, there are a lot of them at once.
>>>
>>
>> Two things you didn't address are the acceptable latency of keeping the
>> read-only nodes in sync with the master - can they be different for a day? A
>> minute? Do you need things to stay synchronous? Also, how big is your
>> dataset? A simple pg_dump and some hot scp action after you batched updates
>> might be able to solve your problem.
>
> Latency is important.  I would say 10 to 15 minutes max, but the shorter the
> better.  I don't have an exact size, but I believe the entire DB is about 10
> gig.
>
> We had an idea of creating our apps write the SQL statements to a file,
> rather than using an ODBC drive to directly change the DBs.  Then we could
> scp/rsync the files to the remote machines and execute them there.  This
> just seems like a very manual process though.

You need to have a look at PgQ. (in short, skytools will do exactly
what you want if I understand correctly your requirments, londiste
being somewhat like slony)

>
> - Brian
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Cédric Villemain