POSTGRESQL table replication between 2 servers

All we need is an easy explanation of the problem, so here it is.

I want to to replicate A TABLE from one server A to another B so that A and B always in-sync.

This table on server A has like 4 – 5 million rows, whereas the table B is empty.

What I want to do is doing this by pushing data from A to B. I will be using TRIGGER on Server A via either FDW or DBLINK. If there is CRUD operation on A, then it will immediately pushed into B.

Previously, FWD has been used on another table, but it really really slow. I want to take a precaution of doing this before I proceed.

Questions:

  1. Why FWD is slow? This is the newest method of cross-database operation right?
  2. For the start, Initially I need to copy data Server A into server B. Then turning on the trigger. Correct?
  3. Is FWD better than DBLINK? If so, what makes it better and what does not?
  4. Is there any tool that I can use to achieve this? There is Pentaho. But what else are the options?

Many thanks

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This one should work just fine : Quick setup and the examples for create publication and create subscription.
But keep in mind :

  • that the replication slot must be increased if it is max-out. Use SELECT * FROM pg_create_logical_replication_slot('my_slot', 'test_decoding');
  • don’t forget to restart the server

Thanks to @a_horse_with_no_name

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply