8

I've got the following situation: I have three machines running postgresql databases. One machine holds client account information (call this machine C), the other two machines hold client logging information (call these L1 and L2). The reason for the split is to separate loading over multiple machines (so some clients send logging information to L1, some to L2 ... and maybe some time L3, L4, ...).

When retrieving logging information, in principal I'd like to be able to JOIN between logging tables on Ln and the client account tables on C. In reality I can't do JOINs like this (and even if I could, I'd want to avoid loading C).

My thought is to replicate the tables on C onto each of L1, L2, ... so that I can do the joins. As far as the tables from C are concerned, C is master and L1, L2, ... are slaves. But for the other tables on L1, L2, ... these machines are masters. Its not exactly master-master replication, and is it not exactly master-slave.

Can postgres (I'm running 9.1) replication be persuaded to do this, or if not are there any other packages that would do the job. In the last resort, I can write some code the periodically sync's the tables (I can tolerate some delay), but it would be nice not to!

Thanks in advance.

migrated from stackoverflow.com Mar 19 '14 at 9:53

This question came from our site for professional and enthusiast programmers.

  • 1
    Maybe use FDW on the logging machines to access C? Although, that would incur a performance hit on C. Materialized Views might reduce the performance hit, but I'm not quite sure how PostgreSQL detects updates to the foreign table. If it does automatically (which the ending of the Materialized View documentation seems to suggest), this may solve your problem entirely. These are 9.3 features, though. The very active mailing list might also be of help. – jpmc26 Mar 19 '14 at 9:04
4

On PostgreSQL 9.3, you could use postgres_fdw to transparently query the foreign table on the other machine.

On older versions, dblink can be an option as mentioned by Andrew.

Another option is to use a tool like Londiste or Slony-I to replicate the tables you want. I recommend using Londiste for this, it's going to be much simpler. It creates triggers on the table to detect insert/update/delete, and replicates these using its own client/server and a queueing system to the other database, where it does corresponding insert/update/deletes. I use it in production on several client sites and it works very well.

A future option (hopefully in PostgreSQL 9.5) will be log streaming logical replication, logical changeset extraction and bidirectional replication, which will allow individual databases or tables to be replicated at the SQL level. Part of the work for this was committed to PostgreSQL 9.4, but not enough to make it useful for what you want to do.

3

You should use dblinks and materialized views to achieve this. Both features are built in to the latest versions of Postgres:

http://www.postgresql.org/docs/9.3/static/rules-materializedviews.html

http://www.postgresql.org/docs/9.3/static/dblink.html

Essentially you build an Mview on each database L1,L2... with data extracted from the tables on C, then use Mview refresh to periodically update the Mviews as frequently as required. The data is stored locally so accessing it is very fast. This is only suitable if the data is relatively static and you don't mind the local databases occasionally having slightly out-of-date info. You should set the refresh frequencies to manage this appropriately, and if it is not acceptable then you should just use a database link and deal with the resultant slowness.

If you need additional functionality, the snapshots project provides advanced features such as fast refreshes and snapshot logs:

http://pgfoundry.org/projects/snapshot/

With this you can make the refreshes only update rows that need updating, which can make them extremely fast for large, inelastic datasets, minimising disruption to your app. By default Mviews are completely dropped and recreated in Postgres, which can be very bad for performance for obvious reasons.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy