Table replication with foreign key in Postgresql

Question

I have two tables A and B and A has foreign key to B in Postgresql database . I want to replicate table A to another database without replicating table B because that takes too much time and is not necessary; Only a small portion of table B has been referenced by table A. Is there any replication system in which we can replicate table A and only rows of table B(referenced table) which has been referenced?

None I'm aware of, but I've never investigated this use case in detail. Systems like Londiste can do table-by-table replication. Research Londiste, Slony-I, Bucardo, etc. — Craig Ringer, Jul 06 '15 at 12:17
Is it just about the two tables or are there many more to replicate? — Erwin Brandstetter, Jul 06 '15 at 12:31
@ErwinBrandstetter, There are other tables to replicate , does it make a difference? — Maryam Rabiee, Jul 07 '15 at 12:47
For a few (small) tables and non-critical time management hand-knit solutions may be ok. I added more to my answer. — Erwin Brandstetter, Jul 07 '15 at 13:35

score 0 · Answer 1 · edited May 23 '17 at 12:17

If it's just about the two (or a few) tables I would create a VIEW on B in your master DB:

CREATE VIEW b_for_a
SELECT *  -- or list columns explicitly, depends on your plans
FROM   b
WHERE  EXISTS (SELECT 1 FROM a WHERE a.b_id  = b.b_id);

And sync with dblink (executed on the slave side). Code example:

Is there any shortcut for using dblink in Postgres?

Also consider:

Or use foreign tables using postgres_fdw in Postgres 9.3+.

The remaining big question is how and when to trigger the sync. If your app is not time critical, you could schedule cron jobs at opportune times. And if your tables are small you could simply truncate and copy the whole table. If you need to replicate changes more often or if the tables grow bigger or if you can't lock target tables freely, it gets more complex. Triggers, LISTEN / NOTIFY or even dblink_get_notify(), ...

Hand-knit solutions need more work and attention to details than one of the established replication systems. If things get complex, rather consider one of the established replication systems. Like Slony or Buccardo which offer per-table granularity.

Table replication with foreign key in Postgresql

1 Answers1