0

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.

Skullruss
  • 63
  • 8
  • 1
    I can't answer your actual question, but since you're in the same database, you could just use a view instead. Or grant the customer's role read-only permission to the source table. – Robert Nubel Feb 28 '23 at 14:07
  • 1
    Why not using a trigger (and function) to maintain all changes in the other table? – Frank Heikens Feb 28 '23 at 14:07
  • 1
    Why not just use a view in schema Y that selects from the table in schema X? –  Feb 28 '23 at 14:11
  • @a_horse_with_no_name I'm looking into logical replication as an alternative to doing the other ridiculous thing my company wants to do, which is to MANUALLY alter all of the tables across all schema within microservices. My boss suggested logical replication as a possible alternative, and I've been trying to figure out if it's even possible. – Skullruss Feb 28 '23 at 14:18
  • Using a view seems to be a much easier way to achieve this. –  Feb 28 '23 at 14:32

2 Answers2

1

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.

Stavros Koureas
  • 1,126
  • 12
  • 34
0

One requirement of logical replication is that schema, table and columns have the same name on the publisher and the subscriber. So logical replication within the same database is not possible. Consider an alternative approach, as pointed out in the comments (view, materialized view, triggers).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263