How can one heroku db instance be made to follow multiple parent databases?

Question

I currently have two SOA applications (soa1 and soa2), each with their own database. I would like to create a third application (report1) which somehow merges these databases and provides reports. I was thinking, if I could get soa1 and soa2 to use WAL-E, then report1 could potentially become a read slave and I could use a multi-tenant solution.

I want to join data across soa1 and soa2, so it's important that I have access to both in a single query.

There are three ways I think I can do this

Take my databases off of heroku, setup three databases on my own (ick)
Magically have one database instance on heroku follow soa1 and soa2
Leave everything as it is and inject data into queries, using CTE (also ick)

I don't even know if I can have two separate WALs read into a database, under different schemas... But if I can, I assume I'll have to go with option 1, because I assume heroku doesn't support what I want to do...

Barring all of that, does anyone have any other thoughts on how this could be accomplished?

Craig Ringer · Accepted Answer · 2013-05-08 00:21:22Z

3

You cannot do this with PostgreSQL's built-in binary replication. It cannot merge multiple archive streams and is unlikely to ever be able to do so.

We're working on logical replication that can do this (I'm testing it right now) but it requires patches to PostgreSQL and won't be available with an unpatched PostgreSQL before PostgreSQL 9.4 (9.3 is just about to come out).

In the mean time you can use trigger-based replication solutions like Bucardo, Slony-I, etc to achieve this sort of task. This may be challenging on Heroku as you can't run your own processes on the Heroku database instances directly; you'll probably have to run the replication agents remotely from your own server.

answered May 8, 2013 at 0:21

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can one heroku db instance be made to follow multiple parent databases?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related