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?
1 Answer 1
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.