It's possible to use logical decoding to replicate a single table?

Question

I'm doing a research about logical decoding and I've been able to create a slot and replicate all transactions in a database to another using streaming replication protocol, and it works really well.

But I need to replicate just a single table and not all the tables on the database.

So, my question is: Does the logical decoding allows to filter the stream for a single table?

My current hint is to create a custom logical decoding output plugin, am I wrong?

Update

I've built an output plugin based on contrib/test decoding from postgresql sources and it was a good workaround. However it wasn't useful for real use cases, so I decided to take some other projects as references to fork and update.

The best for me was wal2json, so I decided to fork it and add the table filter as an option and not to hardcode the table names.

Here is the fork and this is the changeset.

How to use

First create the slot with the wal2json plugin:

pg_recvlogical -d postgres --slot test_slot --create-slot -P wal2json

Then start receiving the stream

pg_recvlogical -d postgres --slot test_slot --start -o limit-to=table_foo,table_bar -f -

Now we are ready to receive the updates on table_foo and table_bar only.

---

This was a really good challenge, I'm not a c developer and I know that the code needs some optimizations, but for now it works better than expected.

Answer 1

The current version of wal2json has these options:

* `filter-tables` - tables to exclude
* `add-tables`- tables to include

Usage:

pg_recvlogical -slot test_slot -o add-tables=myschema.mytable,myschema.mytable2

Reference: https://github.com/eulerto/wal2json#parameters

Answer 2

According to the documentation you can implement your own synchronous replication solutions by implementing streaming replication interface methods:

• CREATE_REPLICATION_SLOT slot_name LOGICAL options
• DROP_REPLICATION_SLOT slot_name
• START_REPLICATION SLOT slot_name LOGICAL options

In addition to the interface above you also need to implement Logical Decoding Output plugin. In this plugin interface you need to adjust Change Callback operation, which listens to all DML operations:

The required change_cb callback is called for every individual row modification inside a transaction, may it be an INSERT, UPDATE, or DELETE. Even if the original command modified several rows at once the callback will be called individually for each row.

This is the function where you want to check particular table for replication. Also be aware of the fact that Change Callback will NOT handle UNLOGGED and TEMP tables, but I guess it is not severe limitation.