replication from multiple "master" servers to a single read-only slave
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
On Mon, Jan 11, 2010 at 03:02:18PM -0800, 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 ?
No, but you could use something like Slony to do this.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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