Re: replication choices

Yes, but unless I'm missing something, it doesn't look like any of those
options perfectly fit my situation, except perhaps Slony, which is why I'm
leaning that direction now despite my concerns.

Is there a section of this page I should be re-reading?

On Thu, 25 Jan 2007, Bruce Momjian wrote:

>
> Have you read the 8.2 documentation about this:
>
>     http://www.postgresql.org/docs/8.2/static/high-availability.html
>
> ---------------------------------------------------------------------------
>
> Ben wrote:
>> Hi guys. I've inherited a system that I'm looking to add replication to.
>> It already has some custom replication code, but it's being nice to say
>> that code less than good. I'm hoping there's an existing project out there
>> that will work much better. Unfortunately, I'm not seeing anything that
>> obviously fits my needs, so maybe somebody here can suggest something.
>>
>> I've got a single cluster in the datacenter and dozens of remote sites.
>> Many of these sites are on unreliable connections to the internet, and
>> while they're online more often then not, when their network will go down
>> isn't known, and even when it's up, the network isn't that fast.
>>
>> A vast majority of activity occurs at these remote sites, with very little
>> at the datacenter cluster. That said, the datacenter cluster needs to keep
>> pretty good copies of most (but not all) of the data at each site.
>> Obviously the network unrealiability puts a limit on how up to date the
>> datacenter can be, but loosing data is considered Bad. So, for instance,
>> restoring the daily backup of each site at the datacenter is too
>> infrequent.
>>
>> Each site will replicate to its own schema in the datacenter cluster, so I
>> don't *think* I need a multi-master solution.... but at the same time,
>> because data will be coming from multiple sites, simply replaying WAL
>> files at the datacenter won't work.
>>
>> In addition, there will be some data changes made at the datacenter that
>> will need to replicate to all of the remote sites as soon as they're
>> online. It's ok if data being replicated from the datacenter ends up in a
>> different schema at the remote sites than the schema which holds the data
>> that will be replicated back to the datacenter.
>>
>> My current best guess of what to do is create a global schema at every
>> database, a local schema at each site, and a schema for each site at the
>> datacenter. Then I can use Slony to replicate the global schema from the
>> datacenter to each site, and again use Slony to replicate the local schema
>> from each site to that site's schema in the datacenter. But I'm not too
>> familiar with Slony, and from what I understand, using Slony with bad
>> networks leads to bad problems. I'm also not sure that Slony supports
>> replicating from multiple sources to the same postgres install, even if
>> each replication process is writing to a different schema.
>>
>> Are there any better options? Or is my Slony idea not so bad?
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>
> --
>  Bruce Momjian   bruce@momjian.us
>  EnterpriseDB    http://www.enterprisedb.com
>
>  + If your life is a hard drive, Christ can be your backup. +
>