0

I have been trying to build a pipeline using logical decoding of postgres. However, I am a little confused. Please find below the questions I have

  1. I have established a pub-sub and I can see the data flowing between the 2 servers. However, I haven't set up any slots. When do I use create_replication_slots?
  2. I have the expectation of replicating the tables between 2 servers which I have achieved. However, the detailed logging like old & new values, I would like to store in a file in S3? Can I do both writing to table and writing detailed logging to s3 together?
  3. If I have a database db1 on server1, database db2 on server2 , can I bring them together on server3 with database server3.db1 connecting to server1.db1 and server3.db2 connecting to server2.db2?

I am using postgres10

Please share your thoughts on this

rathimittha
  • 45
  • 6

1 Answers1

0

Question 1: By default, CREATE SUBSCRIPTION will create a logical replication slot.

Question 2: Use two replication slots with appropriate plugins. The one that is used to replicate to a table is automatically created when you create a subscription. For writing to a file, you'd have to create a replication slot explicitly and specific an appropriate plugin (I don't know if such a plugin exists, you may have to write it).

Question 3: Yes.

I would use at least v11 for logical replication because of the limitations in v10.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you for the reply Laurenz. For question 2, where do I specify the different plugins? And what plugins do you recommend? – rathimittha May 14 '20 at 06:12
  • Sorry, I was too sloppy. I have fixed and expanded the second answer. – Laurenz Albe May 14 '20 at 06:20
  • Thanks. I was thinking of using the output of wal2json to be written to a file. Is it possible? – rathimittha May 14 '20 at 06:23
  • Is it possible, wherein , I have a source table say tab1 which will be logically replicated in server tab1 and another table tab1 in server3 which will have detailed changes of the row? For example, when I insert a row 'India' on server1, and update it to 'INDIA', on server2 tab1 I want a single row 'INDIA', whereas on server3 tab1 I want 2 rows, both 'India' and 'INDIA' – rathimittha May 14 '20 at 06:51
  • Not with logical replication. If you write your own thing with - say - wal2json, you can. – Laurenz Albe May 14 '20 at 07:59