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.

You can simply use CREATE PUBLICATION mypub FOR ALL TABLES. — Hayyaun
– Hayyaun, Commented Feb 3, 2024 at 10:11

pir · Accepted Answer · 2020-08-14 05:10:21Z

11

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, 2020 at 5:10

pir

6,05916 gold badges70 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pir Over a year ago

(posting on this old question as it's a top Google result)

pir Over a year ago

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.

odinho - Velmont · Accepted Answer · 2016-05-31 17:38:59Z

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, 2016 at 17:38

odinho - Velmont

21.7k6 gold badges44 silver badges34 bronze badges

answered Aug 16, 2012 at 10:47

belidzs

1541 silver badge9 bronze badges

Comments

kgrittn · Accepted Answer · 2012-08-15 22:33:56Z

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, 2012 at 22:33

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

Collectives™ on Stack Overflow

Replicate selected postgresql tables between two servers?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related