0

I'm using logical replication to move data from postgres to a search engine via some background process, but I am only concerned with a small set of tables. Is there a way to specify which tables a replication slot is concerned with

I have a replication user set up and am able to receive the changes just fine. I've implemented little script to handle the binary protocol and forward to my search engine with:

START_REPLICATION SLOT <SLOT NAME> LOGICAL 0/00000000

But this gives me every change for every table. I see that this is possible between postgres servers via publications and subscriptions, but this doesn't seem to work from an application client. Maybe I am missing something?

Is there a way to whitelist tables for replication slots this way?

2
  • I am facing a similar issue. Is there a solution to restrict a table from writing to a replication? Even if we set the REPLICA IDENTITY to 'NOTHING', it still writes INSERT data in replication slots. Nov 25, 2020 at 22:40
  • @Eric . We are also trying to do something similar. We used PgSync (github.com/toluaina/pgsync). However we face some issues with it as it also depends on replication slots and triggers. the replication slots seems to clone entries for every table even though we need it only for a few. More about the issues here - github.com/toluaina/pgsync/issues/62 I would appreciate if you could share more about the solution if you were able to solve it. Jan 4, 2021 at 13:58

2 Answers 2

3

This is what your output_plugin should do - required second argument to pg_create_logical_replication_slot function. By itself, the replication slot does nothing at all. It is only needed to prevent postgresql from deleting the old WALs required for logical replication.

All the work is done by the logical decoding infrastructure, passing the decoded transactions to the output plugin. This plugin is then responsible for preparing data to the client application (including some filters if needed).

Postgresql has two built-in output plugins:

  • test_decoding - example logical decoding output plugin. Just an example how to write your own output plugin. Also used in postgresql test suite.
  • pgoutput - which is used under the hood of the publishing feature.

Nothing more. All the rest are for extensions.

So, there are possibilities:

  • use native publication feature on postgresql side and modify your client to use the pgoutput plugin format (what's do Debezium, for example)
  • use some third party postgresql extension. For example, wal2json is well known and used in production
  • write your own output plugin, based on test_decoding example
0

Have you tried specifing the list of tables to be logically replicated while creating the publisher?

Create publisher pub_1 for table table_a;
Create subscription sub_1 publication pub_1;

This will send the changes only for table_a to the subscriber. This is the way to whitelist a selected set of tables to replicate.

ALTER PUBLICATION name ADD TABLE table_b;

This Alter publication command can be used to add/remove any tables after creating a publication.

I dont think there is a way to do this with replication slots.

1
  • 2
    Well I'm not replicating between multiple PostgreSQL instances - I'm replicating to elasticsearch. So Just using publications and subscriptions isn't really an option. I'm either going to have to use the SQL API or use the START_REPLICATION command, but the documentation doesn't specify options are available for that command. Oct 7, 2018 at 21:04

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.