Search code examples
postgresqldatabase-designschemalogical-replication

Logical replication for separate schema within the SAME database in Postgresql

For reference, I'm using PostgreSQL version 14.3

Let's say I have two schema, X and Y, and in X I have a table called customer, which will be touched by a customer microservice. I want Y to be able to see the data that is added/changed/removed in real time, without touching that table, thus I wanted to set up logical replication for table X to table Y. However I cannot for the life of me figure out how to do that for 2 tables in the same database. Doing replication for a SEPARATE database is easy enough, but doing so within the same database doesn't seem to be supported at all. Is this something that I simply cannot do?

For example

CREATE SCHEMA X;

CREATE TABLE customer (
    customerstuff VARCHAR(50) NOT NULL
);

ALTER TABLE customer SET SCHEMA X;

CREATE SCHEMA Y;

CREATE TABLE customer (
    customerstuff VARCHAR(50) NOT NULL
);

ALTER TABLE customer SET SCHEMA Y;

Now, I have X.customer and Y.customer, and I want the changes of X.customer to be present in Y.customer, so I tried to do something like this:

CREATE PUBLICATION publicationname FOR TABLE X.customer

SELECT pg_create_logical_replication_slot('slot', 'pgoutput');

CREATE SUBSCRIPTION mysub
CONNECTION 'host=localhost port=5432 user=user password=pass dbname=db'
PUBLICATION publicationname
WITH (slot_name=slot, create_slot=false);

I found that in order to even create a subscription in the same cluster as a publication, I needed to separately define a logical replication slot and use that when I created the subscription, but still the tables are not being replicated. Is there something I'm missing, is there a step I skipped, is it even possible? Please let me know.

Solution

  • Currently as of today (05/2023), this is not possible even with the latest PG15 and I am not even sure if it makes sense. There is also a "similar" discussion here for different schemas but also different databases:

    https://dba.stackexchange.com/questions/227740/logical-replication-to-different-schema-name

    I understand that maybe you need this in order not to touch the main table, but what is the real reason behind it? Is the performance which may be depredated by being accessed by two microservice? Because in this case, Logical Replication is going also to add a small overhead, especially from the moment you set the server instance replication to logical.