Re: pg_upgrade and subscriptions

Em sáb., 2 de nov. de 2019 às 13:55, Oleksii Kliukin
<alexk(at)hintbits(dot)com> escreveu:
>
> I came across a surprising behavior when upgrading our PostgreSQL 10 DBs that
> also serve as a destination for the logical replication of some reference tables.
>
> pg_upgrade turns off all subscriptions on the cluster and doesn't turn them on.
> Specifically, it creates them with connect = false, as discussed at the thread
> starting at
> https://www.postgresql.org/message-id/e4fbfad5-c6ac-fd50-6777-18c84b34eb2f@2ndquadrant.com
>
> Unfortunately, I can't find any mention of this in the docs of pg_upgrade, so
> I am at leas willing to add those if we can't resolve this in a more automated
> way (read below).
>
It is documented in step 13 "Post-upgrade processing". In this case,
we need to provide a new script for subscriptions.

> Since we can determine those subscriptions that were active on the old cluster
> immediately before the upgrade, we could collect those and emit a script at
> the end of the pg_upgrade to turn them on, similar to the one pg_upgrade
> produces for the analyze.
>
+1. It seems to be an oversight (missing feature) that nobody bothers to fix it.

> There are two options when re-enabling the subscriptions: either continue from
> the current position (possibly discarding all changes that happened while the
> database was offline), or truncate the destination tables and copy the data again.
> The first one corresponds to setting copy_data=false when doing refresh publication.
> The second one is a combination of a prior truncate + refresh publication with
> copy_data=true and doesn't seem like an action that is performed in a
> single transaction. Would it make sense to add a copy_truncate flag, false
> by default, that would instruct the logical replication worker to truncate the
> destination table immediately before resyncing it from the origin?
>
It seems the common case is the former. However, I don't think people
want to "discard all the changes that happen while database was
offline" because the slot will remain in the publisher and we are
upgrading the subscriber. Since we are providing a script for the
common case, you are free to ignore it and create a new script that
fulfill your requirements.

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento