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