Em sáb., 2 de nov. de 2019 às 13:55, Oleksii Kliukin <al...@hintbits.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-18c84b34e...@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
