pg_upgrade and publication/subscription problem

From: Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_upgrade and publication/subscription problem
Date: 2021-11-25 11:43:11
Message-ID: CAB-JLwYRk8K9-+sKd3DQcR=9Y04KC=Cv4BL11XyPgPtX9TtQYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

A publication for all tables was running fine, Master is a PostgreSQL
11.11. Replica was running version 13 (don´t remember minor version).

Then we tried to update only subscriber server, nothing was done on master
side.

Then we did ...
- installed postgresql-14.
- configured postgresql.conf to be similar to previous.
- on version 13 disabled subscription - alter subscription disable.
- changed both port to run pg_upgrade.
- stop services for both 13 e 14.
- */usr/lib/postgresql/14/bin/pg_upgrade -b /usr/lib/postgresql/13/bin -B
/usr/lib/postgresql/14/bin -d /etc/postgresql/13/main/ -D
/etc/postgresql/14/main/ -j 2 --link -p 9999 -P 9998 -U postgres -v*
- when finished upgrade process, we removed version 13 and ran *vacuumdb -p
9998 -U postgres --all --analyze-in-stages*
- last step was to enable that subscription.
- just wait for the subscriber to get the data changed, pg_upgrade ran for
15 minutes, this should be synced in a few seconds ...
- few seconds later we remembered that some other tables were created on
publication server, so we did a refresh publication.

Then, some minutes later we got lots of log entries "duplicate key value
violates unique constraint pk..." because it was trying to COPY that table
from master.

We disable subscription again until we solve, as remains.

Selecting from pg_subscription_rel all old tables are with srsubstate i for
initialize, not s for synchronized or r for ready, as they should. And
all srsublsn of these records were null, so it lost synchronization
coordination for all tables which existed before this upgrade process.

So, my first question is, as our publication server continues running, lots
of updates were processed, so how can I synchronize both sides without
recreating that publication ?
And my second question is, is this problem documented ? Is this problem
expected to happen ?

regards,
Marcos

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2021-11-25 12:03:41 Re: Windows build warnings
Previous Message Dilip Kumar 2021-11-25 11:16:54 Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints