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.

We've got a system with about a dozen Postgres 9.3 databases representing a dozen different applications. In order to do reporting across systems, we have a script that dumps each database and loads it back into a single database (on a second server) under a schema with the same name as the original database.

So for example, we've got the following databases on the main server:

  • A
  • B
  • C

and on the reporting server, we'll load those into a single database across multiple schemas, which ends up looking like this:

  • reporting.A
  • reporting.B
  • reporting.C

Anyway, the script that moves the data takes way too long to run and I was wondering if anyone can suggest a way to use Postgres's replication capability in order to make this process a live, continuous operation?

share|improve this question
add comment

1 Answer

Streaming replication can't do this - it can only replicate the whole database cluster together, as a unit.

I think you want Londiste, Bucardo, or maybe even Slony-I for this. I've used Londiste for similar jobs and it works well.

There's also ongoing work on an in-core feature that meets this need, logical replication / bidirectional replication. It's pre-alpha quality, though, so I suggest waiting until it appears, hopefully in PostgreSQL 9.5. Parts of it have been included in 9.4, so some 3rd party tools may be able to use it down the track.

share|improve this answer
add comment

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.