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