0

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

Improve this question
5
  • The most efficient solution is to use logical replication Commented Jun 30, 2022 at 7:38
  • Hi @a_horse_with_no_name. Any real example on this ? Please point me to an article or something. Thanks Commented Jun 30, 2022 at 8:12
  • Quick setup and the examples for create publication and create subscription Commented Jun 30, 2022 at 8:20
  • Hi @a_horse_with_no_name, I did follow the article you mentioned. I set wal_level to "logical", restart the publisher, and create publication. When creating subscription, the process fail with error : "ERROR: logical decoding requires wal_level >= logical". I have made sure that the wal_level is logical by checking the pg_settings and "SHOW wal_level". Anything else I should check ? Do i have to create logical replication slot ? Many thanks Commented Jul 5, 2022 at 1:29
  • Somehow the replication now works after restart here and there. Don't know what the real cause are. Thanks @a_horse_with_no_name Commented Jul 12, 2022 at 4:51

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.