From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Omar Mehmood <omarmehmood(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: replication from multiple "master" servers to a single read-only slave |
Date: | 2010-01-12 01:51:05 |
Message-ID: | 4B4BD589.2060408@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Omar Mehmood wrote:
> 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.
No, it can't be done with PITR and WAL-shipping. The write-ahead logs
are at too low a level and rely on the block layout of the Pg cluster.
WAL-shipping only works where master and slave start out with the exact
same data directory contents, with all the same block layout, same oids
for tables/types/etc. Just having the same tuples in tables of the same
names is not sufficient.
Given that you can't even WAL-ship from a master to a slave created by
pg_restore from a dump of the master, you can probably see why
WAL-shipping from multiple masters absolutely cannot work.
For this, you need something higher level that replicates at the
tuple-change level. A trigger-based system like Slony or Bucardo is most
likely to fit your needs.
> 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).
At present Pg doesn't offer safe and convenient C-level hooks for
replication systems to attach to in order to record tuple changes.
Recording of tuple changes for replication must be done with triggers.
As you need a tuple-change level replication system, you're pretty much
out of luck.
--
Craig Ringer
From | Date | Subject | |
---|---|---|---|
Next Message | Yan Cheng Cheok | 2010-01-12 02:12:18 | Best Practice when Encounter Invalid Stored Procedure Parameters |
Previous Message | Omar Mehmood | 2010-01-12 01:50:41 | transaction logging in the form of SQL statements |