| From: | Michał Kłeczek <michal(at)kleczek(dot)org> |
|---|---|
| To: | Koen De Groote <kdg(dot)dev(at)gmail(dot)com> |
| Cc: | Muhammad Usman Khan <usman(dot)k(at)bitnine(dot)net>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes? |
| Date: | 2024-10-17 09:16:41 |
| Message-ID: | 657111A5-7475-4198-B8FD-83E87D628F5B@kleczek.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
> On 17 Oct 2024, at 11:07, Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:
>
> Hello Muhammad,
>
> The problem with my scenario is the changes are written as a single transaction, with a BEGIN and COMMIT. In that transaction, there are first inserts, then a schema change, and then inserts on the new schema.
I guess until logical replication of DDL is available you’re out of luck.
The best you can do is to have a separate table for recording and replaying schema changes.
Create triggers that perform actual DDL operations based on DML in this table.
Publish this table on the publisher in the same publication as the tables affected by the DDL.
On the subscriber side it is the same - just make the trigger is marked as ENABLE REPLICA TRIGGER or ENABLE ALWAYS TRIGGER.
Kind regards,
Michał
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Koen De Groote | 2024-10-17 10:28:31 | Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes? |
| Previous Message | Koen De Groote | 2024-10-17 09:07:19 | Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes? |