Replicate selected postgresql tables between two servers?

Question

What would be the best way to replicate individual DB tables from a Master postgresql server to a slave machine? It can be done with cron+rsync, or with whatever postgresql might have build in, or some sort of OSS tool, but so far the postgres docs don't seem to cover how to do table replication. I'm not able to do a full DB replication because some tables have license->IP stuff connected to it, and I can't replicate those on the slave machine. I don't need instant replication, hourly would be acceptable as well.

If I need to just rsync, can someone help identify what files within the /var/lib/pgsql directory would need to be synced, or how I would know what tables they are.

score 9 · Answer 1 · answered Aug 14 '20 at 05:10

9

Starting with Postgres 10, logical replication is built into Postgres! This is often a better solution than external solutions. The Postgres docs are great and easy to follow. It's very easy. See the quick setup docs, which in essense boils down to running this:

-- On publisher DB
CREATE PUBLICATION mypub FOR TABLE users, departments;

-- On subscriber DB
CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;

answered Aug 14 '20 at 05:10

pir

5,513
12
63
101

(posting on this old question as it's a top Google result) – pir Aug 14 '20 at 05:10
For anyone curious, we've used this in production (tables with many millions of rows, lots of inserts/updates) and it works beautifully with low latency. – pir Aug 14 '22 at 12:02

score 4 · Answer 2 · edited May 31 '16 at 17:38

4

You might want to try Bucardo, which is an open source software to synchronize rows between tables even if they are in a remote location. It's a very simple software, and it is capable of creating one-way synchronization relationships as well.

Check out http://bucardo.org/wiki/Bucardo

edited May 31 '16 at 17:38

odinho - Velmont

20,922
6
41
33

answered Aug 16 '12 at 10:47

belidzs

145
1
9

score 3 · Accepted Answer · answered Aug 15 '12 at 22:33

3

You cannot get anything useful by copying individual tables files in the data directory. If you want to replicate selected tables, there are a number of good options.

http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

answered Aug 15 '12 at 22:33

kgrittn

18,113
3
39
47

Replicate selected postgresql tables between two servers?

3 Answers3