Postgresql – Replicating some tables from one postgres database to another

postgresqlreplication

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.

Best Answer

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.

Related Question