Skip site navigation (1) Skip section navigation (2)

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

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [SPAM] Re: Best way to replicate to large number of nodes
Date: 2010-04-28 12:12:58
Message-ID: hr98oa$frp$ (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On 2010-04-22, Brian Peschel <brianp(at)occinc(dot)com> wrote:
> 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.

should not be a problem 10 to 15 second latency is easy to get over 
slow connections (eg satellite) with any of the proposed solutions.

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

yes, and furthermore SQL-replication tends not to work as intended 
if you have any updates or inserts that invoke non-constant default
values like now(), nextvalue(...), or random()

In response to

pgsql-general by date

Next:From: Craig RingerDate: 2010-04-28 12:14:10
Subject: Re: How many threads/cores Postgres can utilise?
Previous:From: Piotr KublickiDate: 2010-04-28 12:08:33
Subject: Re: Start-up script for few clusters: just add water?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group