replication from multiple "master" servers to a single read-only slave

From: Omar Mehmood <omarmehmood(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: replication from multiple "master" servers to a single read-only slave
Date: 2010-01-11 23:02:18
Message-ID: 878327.9129.qm@web53607.mail.re2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2010-01-11 23:18:58 Re: How to get DATE in server locale format
Previous Message Scott Marlowe 2010-01-11 23:01:00 Re: Huge iowait during checkpoint finish