Search code examples
postgresqldata-pipelinelogical-decoding

Logical decoding - postgres - multiple output formats

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

Solution

  • 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.