4

I'm worried that unfortunately there's not a great way to handle this.

Here's what happened:

  1. Schema change made in main database
  2. Logically replicated db has issue with subscription given the identity change
  3. Subscription failed
  4. Inactive replication slot in pg_replication_slots

Is there anyway to get back and not lose any of the data that the WAL has written? For example, I've tried the following on the replication server:

ALTER SUBSCRIPTION "my_subscription" REFRESH PUBLICATION;
ALTER SUBSCRIPTION "my_subscription" ENABLE;

but it still doesn't work. Any thoughts?


To be clear, while I wanted to abstract the underlying sync issue from the question, I got a question about what it was.

2022-11-13 06:00:01 UTC::@:[24360]:ERROR: logical replication target relation "public.existing_table" is missing replicated columns: "my_new_col", "type"
2022-11-13 06:00:01 UTC::@:[353]:LOG: background worker "logical replication worker" (PID 24360) exited with exit code 1
2022-11-13 06:00:01 UTC::@:[24361]:ERROR: duplicate key value violates unique constraint "_prisma_migrations_pkey"

My only work around was dropping the replication slot using pg_drop_replication_slot and then setting up the subscription again.


My question is:

How can you have schema changes / identity / FK updates in the source automatically transfer to the destination (which is being replicated) without logical replication breaking?

3
  • 1
    Same thoughts as the other place you posted this.
    – jjanes
    Commented Nov 15, 2022 at 17:22
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Commented Nov 17, 2022 at 16:42
  • 1
    @MarcelloMiorelli for sure, sorry. Updated. Let me know if it's not clear!
    – jlarks32
    Commented Nov 18, 2022 at 17:16

1 Answer 1

1

The answer to your bolded question is, “You don’t. Logical replication doesn’t support those operations.”

As per the manual:

The schema definitions are not replicated, and the published tables must exist on the subscriber.

In logical replication, the subscriber table may look nothing like the publisher table. Replicating schema changes may damage the subscriber table.

If logical replication stops due to an error from a schema change, modify the schema on the subscriber (or add row filters as needed) and resume replication. No resync should be required in this case.

If you need everything to replicate then physical replication is more appropriate.

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.