Re: replication choices - Mailing list pgsql-general

I believe I have a similar situation involving multiple database
instances acting quasi-independently on a common (at least conceptually)
set of data.  Since each instance can effectively operate independently,
I am uncertain if the term replication is accurate, but here is my
strategy to keep the data properly synchronized.  It is still unproven
so any advice or scrutiny that can be given is welcome.

Situation:
There are multiple sites at which the same database/front-end
application is running.  None of the sites are directly connected to one
another over a network and the only communication between sites is
effectively unidirectional to a central location (i.e., information can
independently go both ways during a communications link, but it's not
real-time duplex).  Each of the sites allows authorized users to perform
any type of change to the data.

Solution:
Each site has 3 different versions of what I call the base schema:
Confirmed, Pending, and Update.  Each of these versions has some special
columns associated with it to capture other information about changes
that are made to it (e.g. timestamp, action(insert,update,delete), and
status).  The central site (which I'm loathe to call 'master') has these
same schemas, plus it has an additional Update schema for each other
site in the system.

During normal use at each non-central site, the Pending schema is the
active schema  from which data is queried and also added, modified, and
deleted.  Each time a record is changed in the Pending schema it's
status is flagged as 'pending' and the new data is copied to the Update
schema.  Also copied to the Update schema is the old data from the
record that was changed.  This effectively makes the Update schema a log
of what each record in the database was changed to, what it was changed
from, and when that happened (in UTC).  The data from the update schema
is then dumped regularly to a flat file.

When any remote site establishes a communications link with the central
site, the flat files of the Update schema from each site are exchanged
and the official synchronization time is taken to be that of the flat
file that was updated least recently (i.e., the older file).  Then, at
each site the data from the flat file is uploaded to the local Updates
schema.  All of the records in the now more populous Update schema are
then processed sequentially by timestamp and applied to the Confirmed
schema so, in theory, the same changes should be simultaneously getting
applied to the Confirmed schemas at both locations in question.
Finally, each record in the Pending schema is set to the value contained
in the Confirmed schema and the flag set back to 'confirmed', the two
sites are considered synchronized, and then the whole process starts
anew.

There are some details that have been glossed over here to eschew
obfuscation, and the actual situation at the central site is more
complex than this in practice, but that is the gist of the approach.  I
do not know of any product, Slony included, that has built in support
for a situation such as this, so I suspect all of the details will have
to be handled in a custom fashion.

Anyhow, Ben, this is my working solution and, from the sounds of it,
yours is the only case I have heard of that has the same set of
challenges.  I am interested in hearing if these ideas will work for you
and/or if anyone knows of any flaws in this methodology or a
better/easier/more reliable means of accomplishing this task.  I should
point out that, in our environment of understandably limited
connectivity, we are definitely more tolerant of the delayed performance
this synchronization strategy will cause than most users/companies would
be.  The important thing for us is that the data integrity is maintained
and that everyone at each site can access and change the data.

Regards,
Joel

-----Original Message-----
From: Ben [mailto:bench@silentmedia.com]
Sent: Thursday, January 25, 2007 1:18 PM
To: pgsql-general@postgresql.org
Subject: replication choices

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?