multi-master to single-slave replication at table level with PostgreSQL or MySQL

Question

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:

https://stackoverflow.com/questions/7544241/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.

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.