Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here is my scenario

Master1 hosting DB1
Master2 hosting DB2
...
MasterN hosting DBN

replicate to:

Slave1 hosting DB1,DB2... DBN

I've read similar questions and they recommend to start different instances at Slave1 and simply do MasterN-Slave1(instanceN) replication, as instructed here:

Single slave - multiple master MySQL replication

That would be piece of cake, but running different instances might be a waste of resources.

I really want to achieve this with an single DBMS instance at Slave1, and if possible with PostgreSQL; but can try with MySQL if there is a solution for this.

Any help is appreciated.

share|improve this question

migrated from stackoverflow.com Feb 27 '13 at 16:29

This question came from our site for professional and enthusiast programmers.

1 Answer 1

the cleanest solution with PostgreSQL is just to do an initdb for each server you will be replicating from, run it on a different port, and have each server replicating to a server software instance on the slave.

If you need something more complex, you should start looking at Slony, Londiste, or Bucardo. These are trigger-based replication systems which are far more flexible than the built-in replication systems that ship with the database. You can build amazingly flexible solutions with these, but there is a lot of design overhead.

The simplest approach is that every table on every master gets writes redirected to a partition for the master and then these partitions get replicated into separate partitions on the slave.

That may be more complicated than ETL, which may be a better option.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.