Thread: lost replication slots after pg_upgrade

lost replication slots after pg_upgrade

From
Pavel Stehule
Date:
Hi

One customer reports issue related to pg_upgrade.


But I didn't find documentation of this limitation?

Regards

Pavel

Re: lost replication slots after pg_upgrade

From
Bruce Momjian
Date:
On Tue, Oct 13, 2020 at 06:20:41PM +0200, Pavel Stehule wrote:
> Hi
> 
> One customer reports issue related to pg_upgrade.
> 
> I found a thread https://www.postgresql-archive.org/
> Upgrade-and-re-synchronization-with-logical-replication-pglogical-and-PG-10-td6001990.html
> 
> But I didn't find documentation of this limitation?

So, what is the question?  Peter Eisentraut is right that WAL is not
preserved, so replication slots are not preserved.  We do have
pg_upgrade instructions for upgrading binary replication, but I assume
people recreate the slots.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee




Re: lost replication slots after pg_upgrade

From
Pavel Stehule
Date:


út 13. 10. 2020 v 18:33 odesílatel Bruce Momjian <bruce@momjian.us> napsal:
On Tue, Oct 13, 2020 at 06:20:41PM +0200, Pavel Stehule wrote:
> Hi
>
> One customer reports issue related to pg_upgrade.
>
> I found a thread https://www.postgresql-archive.org/
> Upgrade-and-re-synchronization-with-logical-replication-pglogical-and-PG-10-td6001990.html
>
> But I didn't find documentation of this limitation?

So, what is the question?  Peter Eisentraut is right that WAL is not
preserved, so replication slots are not preserved.  We do have
pg_upgrade instructions for upgrading binary replication, but I assume
people recreate the slots.

I cannot find related documentation.



--
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: lost replication slots after pg_upgrade

From
Bruce Momjian
Date:
On Tue, Oct 13, 2020 at 06:37:14PM +0200, Pavel Stehule wrote:
> 
> 
> út 13. 10. 2020 v 18:33 odesílatel Bruce Momjian <bruce@momjian.us> napsal:
> 
>     On Tue, Oct 13, 2020 at 06:20:41PM +0200, Pavel Stehule wrote:
>     > Hi
>     >
>     > One customer reports issue related to pg_upgrade.
>     >
>     > I found a thread https://www.postgresql-archive.org/
>     >
>     Upgrade-and-re-synchronization-with-logical-replication-pglogical-and-PG-10-td6001990.html
>     >
>     > But I didn't find documentation of this limitation?
> 
>     So, what is the question?  Peter Eisentraut is right that WAL is not
>     preserved, so replication slots are not preserved.  We do have
>     pg_upgrade instructions for upgrading binary replication, but I assume
>     people recreate the slots.
> 
> 
> I cannot find related documentation.

You mean related documentation of how to manage changing replication
slots?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee




Re: lost replication slots after pg_upgrade

From
Pavel Stehule
Date:


út 13. 10. 2020 v 18:57 odesílatel Bruce Momjian <bruce@momjian.us> napsal:
On Tue, Oct 13, 2020 at 06:37:14PM +0200, Pavel Stehule wrote:
>
>
> út 13. 10. 2020 v 18:33 odesílatel Bruce Momjian <bruce@momjian.us> napsal:
>
>     On Tue, Oct 13, 2020 at 06:20:41PM +0200, Pavel Stehule wrote:
>     > Hi
>     >
>     > One customer reports issue related to pg_upgrade.
>     >
>     > I found a thread https://www.postgresql-archive.org/
>     >
>     Upgrade-and-re-synchronization-with-logical-replication-pglogical-and-PG-10-td6001990.html
>     >
>     > But I didn't find documentation of this limitation?
>
>     So, what is the question?  Peter Eisentraut is right that WAL is not
>     preserved, so replication slots are not preserved.  We do have
>     pg_upgrade instructions for upgrading binary replication, but I assume
>     people recreate the slots.
>
>
> I cannot find related documentation.

You mean related documentation of how to manage changing replication
slots?

no, I just missi note, so after upgrade by pg_upgrade I have to recreate replication slots. Some like

after pg_upgrade you should to do:

a) run analyze .... (it is a known case)
b) recreate replication slots - these slots are not removed in the upgrade process.

Regards

Pavel



--
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: lost replication slots after pg_upgrade

From
Peter Eisentraut
Date:
On 2020-10-13 19:23, Pavel Stehule wrote:
> no, I just missi note, so after upgrade by pg_upgrade I have to recreate 
> replication slots. Some like
> 
> after pg_upgrade you should to do:
> 
> a) run analyze .... (it is a known case)
> b) recreate replication slots - these slots are not removed in the 
> upgrade process.

An argument could be made that pg_upgrade should copy over logical 
replication slots.  The normal scenario would be that you pause your 
logical subscriptions, run pg_upgrade on the publisher, then un-pause 
the subscriptions.  The subscribers then ought to be able to reconnect 
and continue consuming logical changes.  Since the content of the 
publisher database is logically the same before and after the upgrade, 
this should appear transparent to the subscribers.  They'll just see 
that the publisher was offline for a while.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: lost replication slots after pg_upgrade

From
Bruce Momjian
Date:
On Tue, Oct 13, 2020 at 11:34:27PM +0200, Peter Eisentraut wrote:
> On 2020-10-13 19:23, Pavel Stehule wrote:
> > no, I just missi note, so after upgrade by pg_upgrade I have to recreate
> > replication slots. Some like
> > 
> > after pg_upgrade you should to do:
> > 
> > a) run analyze .... (it is a known case)
> > b) recreate replication slots - these slots are not removed in the
> > upgrade process.
> 
> An argument could be made that pg_upgrade should copy over logical
> replication slots.  The normal scenario would be that you pause your logical
> subscriptions, run pg_upgrade on the publisher, then un-pause the
> subscriptions.  The subscribers then ought to be able to reconnect and
> continue consuming logical changes.  Since the content of the publisher
> database is logically the same before and after the upgrade, this should
> appear transparent to the subscribers.  They'll just see that the publisher
> was offline for a while.

I guess that is possible since pg_upgrade resets the WAL location,
though not the WAL contents.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee