4

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.)

2
  • You could create another database in the same or another cluster on Computer B, where you write your data. Operating in one of both databases, you can access the other via dblink or more modern postgres-fdw. Sep 20, 2014 at 3:01
  • The BDR project adds this capability to PostgreSQL, though it's still rather new. It requires that you replicate all tables/schemas at the moment though. See wiki.postgresql.org/wiki/BDR_User_Guide Sep 20, 2014 at 12:16

1 Answer 1

4

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 KEY means 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 KEY but 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.

2
  • I discovered Londiste shortly after I posted this question. Unfortunately, it will not work for me, because my schema contains several tables that have no PRIMARY KEYs nor can an artificial one easily be added. (I wish this were clearer in its documentation. Actually I wish the documentation were clearer in general. I blew an entire afternoon just getting to the point where it would refuse to add tables.) Still, this may be a valid solution in the general case.
    – zwol
    Sep 20, 2014 at 16:17
  • 1
    No primary key? Ugh. That leaves you in a pickle. Sep 20, 2014 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.