pg_upgrade and subscriptions

From: Oleksii Kliukin <alexk(at)hintbits(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_upgrade and subscriptions
Date: 2019-11-02 16:55:25
Message-ID: E956C362-30F8-4E4C-A57C-D9CB8B4FC3E2@hintbits.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

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).

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.

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?

Regards,
Oleksii

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-11-02 18:15:34 Re: 64 bit transaction id
Previous Message Pavel Stehule 2019-11-02 16:34:10 Re: [Proposal] Global temporary tables