Обсуждение: Lost replication slots after pg_upgrade.

Nikhil Shetty wrote:
> Hi,
> I have a Primary and Standby running on Postgres v11.7.
> 
> I was following the document to upgrade a PG v11.7 cluster to PG v13.4.
> After upgrade using pg_upgrade on primary, I ran rsync to setup standby
> PG13 but when I started the primary, the replication slots were not
> present. How will standby start streaming in this case?
> 
> We are using physical replication slots.

You can create the replication slots manually with
pg_create_physical_replication_slot() any time.

However, unless you are very brave and know what you are doing,  I'd
recommend using pg_basebackup to setup a standby, not rsync. You can
even use the -C and -S options of pg_basebackup to create the slots
for you, and many other pg_basebackup's nice options.


-- 
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet

Vijaykumar Jain wrote:
> On Thu, Feb 3, 2022, 10:54 AM Victor Sudakov <vas@sibptus.ru> wrote:
> 
> > Nikhil Shetty wrote:
> > > Hi,
> > > I have a Primary and Standby running on Postgres v11.7.
> > >
> > > I was following the document to upgrade a PG v11.7 cluster to PG v13.4.
> > > After upgrade using pg_upgrade on primary, I ran rsync to setup standby
> > > PG13 but when I started the primary, the replication slots were not
> > > present. How will standby start streaming in this case?
> > >
> > > We are using physical replication slots.
> >
> > You can create the replication slots manually with
> > pg_create_physical_replication_slot() any time.
> >
> > However, unless you are very brave and know what you are doing,  I'd
> > recommend using pg_basebackup to setup a standby, not rsync. You can
> > even use the -C and -S options of pg_basebackup to create the slots
> > for you, and many other pg_basebackup's nice
> >
> 
> A small demo of 11 to 13 upgrade using pg_upgrade and rsync.
> It might not be all the best practices, but just following the doc on my
> laptop.
> 
> >
> https://gist.github.com/cabecada/4517af13245383b888833cfc69d741be
> 
> Just ensure the backed up configs are in back in and also create the
> replication slot.

The black magick of using rsync instead of pg_basebackup is even
documented in https://www.postgresql.org/docs/13/pgupgrade.html but
it's much more difficult and error-prone than using pg_basebackup. 

-- 
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet

Nikhil Shetty wrote:
> 
> Thank you for the update. Is there a way we can retain the physical slot
> during and after upgrade?

What exactly are you trying to achieve?

A minor upgrade does not destroy your slots, actually it does not
modify $PGDATA.

A major upgrade requires creating your replicas from scratch with
pg_basebackup (unless you are adventurous enough to go the rsync way).
pg_basebackup can create the slots for you automagically (-C -S) if
they are gone.

-- 
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet

Hi,

On Tue, Feb 08, 2022 at 12:24:56PM +0530, Nikhil Shetty wrote:
> 
> I am doing a major version upgrade from pg 11 to 13. The point is if I lose
> the replication slot after upgrade then how will I sync standby from
> primary after rsync.
> 
> I can create the slot after upgrading but won't there be dataloss?

The replication slots are only there to make sure that the primary server won't
remove needed WALs before the standby can retrieve them.  Unless you start
production activity just after the pg_upgrade and before finishing rebuilding
the secondary there shouldn't be enough activity to lead to removing old WALs.
But if you do, you can create the needed replication slot(s) manually just
after the pg_upgrade.  But as already mentioned, I also recommend using
pg_basebackup for rebuilding the standby server(s).

> pg_basebackup takes time for large databases (> 5TB). I feel rsync should
> be faster.

Not necessarily.  pg_basebackup will do simple sequential read of all the data,
which is probably the fastest thing to do.  rsync will do some extra processing
that isn't required in that scenario, so it's likely to be slower (although
probably only marginally slower).

On Tue, Feb 08, 2022 at 01:18:39PM +0530, Nikhil Shetty wrote:
> 
> Thank you for the information. I wanted to understand why the replication
> slot is removed after or during upgrade?

Because pg_upgrade modifies your primary instance in such a way that a standby
can't simply keep replicating from it.

Julien Rouhaud wrote:
> 
> > pg_basebackup takes time for large databases (> 5TB). I feel rsync should
> > be faster.
> 
> Not necessarily.  pg_basebackup will do simple sequential read of all the data,
> which is probably the fastest thing to do.  rsync will do some extra processing
> that isn't required in that scenario, so it's likely to be slower (although
> probably only marginally slower).

I think Nikhil was going to invoke the rsync black magic as described
in https://www.postgresql.org/docs/current/pgupgrade.html, not for
copying the entire PGDATA from the master to standbys. While the rsync
magic should be hundreds of times faster because only modified files
will be copied and the majority of files will be just hardlinked, I've
never felt I have enough skill and luck to undertake this method.

-- 
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet

Nikhil Shetty wrote:
> Hi Julien,
> 
> Thank you for the information. I wanted to understand why the replication
> slot is removed after or during upgrade?

Because pg_upgrade basically runs initdb for you. You are dealing with
a new cluster after pg_upgrade, in fact.


-- 
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet

On Tue, Feb 08, 2022 at 08:32:22AM +0000, Victor Sudakov wrote:
> Julien Rouhaud wrote:
> > 
> > > pg_basebackup takes time for large databases (> 5TB). I feel rsync should
> > > be faster.
> > 
> > Not necessarily.  pg_basebackup will do simple sequential read of all the data,
> > which is probably the fastest thing to do.  rsync will do some extra processing
> > that isn't required in that scenario, so it's likely to be slower (although
> > probably only marginally slower).
> 
> I think Nikhil was going to invoke the rsync black magic as described
> in https://www.postgresql.org/docs/current/pgupgrade.html, not for
> copying the entire PGDATA from the master to standbys. While the rsync
> magic should be hundreds of times faster because only modified files
> will be copied and the majority of files will be just hardlinked, I've
> never felt I have enough skill and luck to undertake this method.

Ah, if that's the case I don't think there's any doubt to have for rsync being
faster.  And this method works just fine if you take care of what you're doing.

> On Feb 8, 2022, at 2:08 AM, Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> Because pg_upgrade modifies your primary instance in such a way that a standby
> can't simply keep replicating from it.

Would it work as expected if before pg_upgrade on the standby:

alter subscription my_subscription disable;
alter subscription my_subscription set (slot_name = NONE);

And then after pg_upgrade:

alter subscription my_subscription enable;

I have not tried it yet, but I thought this would be the simplest option.

John DeSoi, Ph.D.