Re: replication from multiple "master" servers to a single read-only slave - Mailing list pgsql-general

Thanks for the suggestions.

I really don't want to use separate schemas for each master to logically partition the data.  I ensure that the data on
eachmaster will not clash with each other (in terms of any DB level contraints such as PK), so I'd much prefer they all
residein a single schema.  Also, my understanding is that Slony uses DB triggers to track changes (but I want to avoid
usingDB triggers). 

Another additional constraint-- the master servers may not always have connectivity to the slave machine, so the chosen
mechanismneeds to be robust and not assume 100% uptime. 

For truly simple, I could do a periodic data dump of the database on the server, ship to slave, and restore.  However,
thisis way overkill since there won't be that many changes in the data for the period of time that it will run (e.g.
every15-20 minutes).  Plus, I might end up in the situation where the slave will start to increasingly lag behind over
time(depends on the number of master servers and amount of data). 

I will check out Bucardo.

Omar

--- On Mon, 1/11/10, Ben Chobot <bench@silentmedia.com> wrote:

> From: Ben Chobot <bench@silentmedia.com>
> Subject: Re: [GENERAL] replication from multiple "master" servers to a single read-only slave
> To: "Omar Mehmood" <omarmehmood@yahoo.com>
> Date: Monday, January 11, 2010, 6:58 PM
> I'm pretty sure you can do this with
> Bucardo, and I *think* you can do it via Slony, if you're
> willing to use seperate schemas for each master. I know you
> cannot do this with PITR.
>
> On Jan 11, 2010, at 4:02 PM, Omar Mehmood wrote:
>
> > I'm wondering if it's possible to have a setup with
> multiple "master" servers replicating to a single
> slave.  I can guarantee that each server will generate
> unique PK values for all tables and all the data is
> partitioned (logically by server) across the servers. 
> I would simply like to have a read-only slave that is a
> picture of all the servers' data (relatively up to
> date).  The individual "master" servers never need to
> know about each other's data (i.e. they do not _need_ to
> "sync" with each other, nor do I want them to be sync'd).
> >
> > Would it be possible to use PostgreSQL PITR feature to
> support this functionality ?  All of the data
> created/updated/deleted per server is unique to that server,
> so replaying the log to the slave should technically be safe
> and the replaying logs from multiple servers should be safe
> as well (as long as the relative order of replay is
> preserved).  I'm just wondering how to get around the
> numbering of the log (WAL) files and the slave's tracking of
> the log files that it has already processed.
> >
> > I can certainly write my own application log module
> that runs on each server, ship over the log to the slave
> machine and replay the logs to the slave (in the meanwhile
> ensuring that the order of replay is preserved and all that
> good stuff), but I'm trying to find a quick(er) solution for
> the short term.
> >
> > Please note that I'd like to avoid using PostgreSQL
> data partitioning as well as any DB triggers (in case anyone
> was going to go down that path as part of the solution).
> >
> > Omar
> >
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>