I have two computers. Computer A is dedicated to collecting data and storing it in its local database. Computer B is for analysis. A is running flat out just with the data collection, so I want to replicate the collected data onto B. The catch is, analysis will generate its own derived data which should also be stored in the database (but only on B). My understanding is that the off-the-shelf replication mechanisms require the recipient database to be read-only.
Is it possible to do something like streaming replication, but which allows the recipient database to be read-write? Ideally, the schema containing the tables replicated from A would be read-only to analysis users, but separate schemas owned by those users would be read-write for them.
It is okay for the replica to be hours out of date.
I want to keep data collection on A writing to a local database, so that it can continue whether or not B is available.
(Currently I'm on 9.3 but about to upgrade to 9.4 betas for unrelated reasons.)
Best Answer
I'd use Londiste for this. Set up the data collector as a publisher and the analytics DB as a subscriber.
The main issue is that Londiste imposes a significant load due to write-amplification - each write must be written once to the table and once to a trigger-maintained replication queue. So using Londiste for replication may add too much load to an already struggling data collector server.
After comments: No
PRIMARY KEYmeans you're pretty stuffed.I haven't checked if any of the trigger based replication systems support append-only replication of tables without a
PRIMARY KEYbut I'd be surprised.Streaming replication doesn't support replication of only some tables, nor is a replica writeable.
The only solution I know of that'll meet your use case is a unidirectional variant of BDR, which we're calling "UDR", but it's still a few months from being ready.