Обсуждение: restarting logical replication after upgrading standby

Hi all,

I'm writing an ansible role to upgrade instances.

I did a shutdown of the the standby and run pg_upgrade to completeion.
Stanby is running and configured as before, but receives no replication 
data.

Replication to another standby is working, but the slot of the upgraded 
standby is inactive.
What is the recommended procedure to get the upgraded standby in sync?

Axel
-- 
PGP-Key: CDE74120  ☀  computing @ chaos claudius

The upgrade was from 12.9 to 13.5 .

Am 03.02.22 um 17:06 schrieb Axel Rau:
> Hi all,
> 
> I'm writing an ansible role to upgrade instances.
> 
> I did a shutdown of the the standby and run pg_upgrade to completeion.
> Stanby is running and configured as before, but receives no replication 
> data.
> 
> Replication to another standby is working, but the slot of the upgraded 
> standby is inactive.
> What is the recommended procedure to get the upgraded standby in sync?
> 
> Axel


-- 
PGP-Key: CDE74120  ☀  computing @ chaos claudius

Am 03.02.22 um 17:06 schrieb Axel Rau:
> Replication to another standby is working, but the slot of the upgraded 
> standby is inactive.
Also the subscription has subenabled set to false.
Enabling it, turns on the active bit at the publisher side, but does not 
start replication.
Recreating the subscription does not help either.

There should be a solution as logical replication is documented to 
support upgrade of the standby.

Axel
-- 
PGP-Key: CDE74120  ☀  computing @ chaos claudius

Thanks for your answer, Vijay,

Perhaps I should mention, that logical replication was already running 
prior to upgrade.
I just did a shutdown of the server at the subscriber (being upgraded).
I did nothing at the publisher.
The slot just went to inactive.

Should I have deleted the slot prior to upgrading?

Axel

Am 05.02.22 um 23:41 schrieb Vijaykumar Jain:
> On Sun, 6 Feb 2022 at 02:20, Axel Rau <Axel.Rau@chaos1.de> wrote:
> 
>> Am 03.02.22 um 17:06 schrieb Axel Rau:
>>> Replication to another standby is working, but the slot of the upgraded
>>> standby is inactive.
>> Also the subscription has subenabled set to false.
>> Enabling it, turns on the active bit at the publisher side, but does not
>> start replication.
>> Recreating the subscription does not help either.
>>
>> There should be a solution as logical replication is documented to
>> support upgrade of the standby.
>>
> 
> i just performed an upgrade using pg_upgrade link mode from pg13 to pg14.
> 
> the only additional step as mentioned in the docs was to create the
> logical replication slot manually.
> subscription started working just fine.
> 
> 
> postgres@controller:/var/tmp/log$ pwd
> /var/tmp/log
> postgres@controller:/var/tmp/log$ /usr/lib/postgresql/14/bin/initdb -D
> /var/tmp/log/testdb
> postgres@controller:/var/tmp/log$ tail postgresql.conf >>
> testdb/postgresql.conf
> postgres@controller:/var/tmp/log$ tail testdb/postgresql.conf
> # Add settings for extensions here
> wal_level=logical
> archive_mode = on
> archive_command = '/bin/true'
> max_wal_size = 48MB
> min_wal_size = 32MB
> shared_buffers = 32MB
> port = 8001
> max_logical_replication_workers = 10
> 
> postgres@controller:/var/tmp/log$  /usr/lib/postgresql/14/bin/pg_ctl -D
> testdb -l logfile start
> waiting for server to start.... done
> server started
> postgres@controller:/var/tmp/log$ psql -p 8001
> psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
> Type "help" for help.
> 
> postgres=# create table t(id int primary key);
> CREATE TABLE
> postgres=# create subscription mysub connection 'port=5001' publication
> mypub;
> NOTICE:  created replication slot "mysub" on publisher
> CREATE SUBSCRIPTION
> postgres=# table t;
>   id
> ----
>    1
>    2
> (2 rows)
> 
> *-- after i do an upgrade it stop getting updates from publisher as no slot
> on pg14*
> *postgres=# select * from pg_stat_subscription;*
> * subid | subname | pid | relid | received_lsn | last_msg_send_time |
> last_msg_receipt_time | latest_end_lsn | latest_end_time*
>
*-------+---------+-----+-------+--------------+--------------------+-----------------------+----------------+-----------------*
> * 16389 | mysub   |     |       |              |                    |
>                   |                |*
> *(1 row)*
> 
> *-- but when i create the logical replication slot on pg14 (see below), it
> starts running just fine*
> *postgres=# select * from pg_stat_subscription;*
> *postgres=# \x*
> *Expanded display is on.*
> *postgres=# select * from pg_stat_subscription;*
> *-[ RECORD 1 ]---------+---------------------------------*
> *subid                 | 16389*
> *subname               | mysub*
> *pid                   | 6099*
> *relid                 |*
> *received_lsn          | 0/C1BC8E0*
> *last_msg_send_time    | 2022-02-06 03:59:07.272526+05:30*
> *last_msg_receipt_time | 2022-02-06 03:59:07.272627+05:30*
> *latest_end_lsn        | 0/C1BC8E0*
> *latest_end_time       | 2022-02-06 03:59:07.272526+05:30*
> 
> *postgres=# table t;*
> *-[ RECORD 1 ]*
> *id | 1*
> *-[ RECORD 2 ]*
> *id | 2*
> *-[ RECORD 3 ]*
> *id | 3*
> 
> -------------------------------------------------------------------------------------------------
> upgrade from pg13 to pg14
> postgres@controller:~/temp/13$ tail postgresql.conf
> # Add settings for extensions here
> wal_level=logical
> archive_mode = on
> archive_command = '/bin/true'
> max_wal_size = 48MB
> min_wal_size = 32MB
> shared_buffers = 32MB
> port = 5001
> max_logical_replication_workers = 10
> 
> postgres@controller:~/temp/13$ initdb -D pg13
> postgres@controller:~/temp/13$ cp postgresql.conf pg13/postgresql.conf
> postgres@controller:~/temp/13$ pg_ctl -D pg13 -l logfile start
> waiting for server to start.... done
> server started
> postgres@controller:~/temp/13$ psql -p 5001
> psql (13.5 (Ubuntu 13.5-2.pgdg20.04+1))
> Type "help" for help.
> 
> postgres=# create table t(id int primary key);
> CREATE TABLE
> postgres=# insert into t values (1);
> INSERT 0 1
> postgres=# insert into t values (2);
> INSERT 0 1
> postgres=# checkpoint;
> CHECKPOINT
> postgres=# create publication mypub for table t;
> CREATE PUBLICATION
> postgres=# \x
> Expanded display is on.
> postgres=# select * from pg_replication_slots;  -- note the details as we
> need to create on pg14 after upgrade
> -[ RECORD 1 ]-------+----------
> slot_name           | mysub
> plugin              | pgoutput
> slot_type           | logical
> datoid              | 13414
> database            | postgres
> temporary           | f
> active              | t
> active_pid          | 5332
> xmin                |
> catalog_xmin        | 489
> restart_lsn         | 0/15E7098
> confirmed_flush_lsn | 0/15E70D0
> wal_status          | reserved
> safe_wal_size       |
> 
> postgres=# \q
> postgres@controller:~/temp/13$  pg_ctl -D pg13 -l logfile stop
> waiting for server to shut down.... done
> server stopped
> 
> 
> ---------------------------------------
> pg14
> 
> postgres@controller:~/temp/14$ initdb -D /var/lib/postgresql/temp/14/pg14
> #perform upgrade
> postgres@controller:~/temp/14$
> /usr/lib/postgresql/${NEW_PSQL}/bin/pg_upgrade -b
> /usr/lib/postgresql/${OLD_PSQL}/bin/ -B
> /usr/lib/postgresql/${NEW_PSQL}/bin/  -d
> /var/lib/postgresql/temp/${OLD_PSQL}/pg${OLD_PSQL} -D
> /var/lib/postgresql/temp//${NEW_PSQL}/pg${NEW_PSQL} -o "-c
> config-file=/var/lib/postgresql/temp/${OLD_PSQL}/pg${OLD_PSQL}/postgresql.conf"
> -O "-c
> config-file=/var/lib/postgresql/temp/${NEW_PSQL}/pg${NEW_PSQL}/postgresql.conf"
> --link
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions                                   ok
> Checking database user is the install user                  ok
> Checking database connection settings                       ok
> Checking for prepared transactions                          ok
> Checking for system-defined composite types in user tables  ok
> Checking for reg* data types in user tables                 ok
> Checking for contrib/isn with bigint-passing mismatch       ok
> Checking for user-defined encoding conversions              ok
> Checking for user-defined postfix operators                 ok
> Creating dump of global objects                             ok
> Creating dump of database schemas
>                                                              ok
> Checking for presence of required libraries                 ok
> Checking database user is the install user                  ok
> Checking for prepared transactions                          ok
> Checking for new cluster tablespace directories             ok
> 
> If pg_upgrade fails after this point, you must re-initdb the
> new cluster before continuing.
> 
> Performing Upgrade
> ------------------
> Analyzing all rows in the new cluster                       ok
> Freezing all rows in the new cluster                        ok
> Deleting files from new pg_xact                             ok
> Copying old pg_xact to new server                           ok
> Setting oldest XID for new cluster                          ok
> Setting next transaction ID and epoch for new cluster       ok
> Deleting files from new pg_multixact/offsets                ok
> Copying old pg_multixact/offsets to new server              ok
> Deleting files from new pg_multixact/members                ok
> Copying old pg_multixact/members to new server              ok
> Setting next multixact ID and offset for new cluster        ok
> Resetting WAL archives                                      ok
> Setting frozenxid and minmxid counters in new cluster       ok
> Restoring global objects in the new cluster                 ok
> Restoring database schemas in the new cluster
>                                                              ok
> Adding ".old" suffix to old global/pg_control               ok
> 
> If you want to start the old cluster, you will need to remove
> the ".old" suffix from
> /var/lib/postgresql/temp/13/pg13/global/pg_control.old.
> Because "link" mode was used, the old cluster cannot be safely
> started once the new cluster has been started.
> 
> Linking user relation files
>                                                              ok
> Setting next OID for new cluster                            ok
> Sync data directory to disk                                 ok
> Creating script to delete old cluster                       ok
> Checking for extension updates                              ok
> 
> Upgrade Complete
> ----------------
> Optimizer statistics are not transferred by pg_upgrade.
> Once you start the new server, consider running:
>      /usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages
> 
> Running this script will delete the old cluster's data files:
>      ./delete_old_cluster.sh
> 
> #copy the conf file
> postgres@controller:~/temp/14$ cp postgresql.conf pg14/
> postgres@controller:~/temp/14$ pg_ctl -D pg14 -l logfile start
> waiting for server to start.... done
> server started
> 
> postgres@controller:~/temp/14$  /usr/lib/postgresql/14/bin/vacuumdb -p 5001
> --all --analyze-in-stages
> vacuumdb: processing database "postgres": Generating minimal optimizer
> statistics (1 target)
> vacuumdb: processing database "template1": Generating minimal optimizer
> statistics (1 target)
> vacuumdb: processing database "postgres": Generating medium optimizer
> statistics (10 targets)
> vacuumdb: processing database "template1": Generating medium optimizer
> statistics (10 targets)
> vacuumdb: processing database "postgres": Generating default (full)
> optimizer statistics
> vacuumdb: processing database "template1": Generating default (full)
> optimizer statistics
> postgres@controller:~/temp/14$ psql -p 5001
> 
> 
> ### this is the only post action after upgrade i had to do (since you would
> use ansible, you can automate this my creating slots manually or via if
> exists and not via create subscription
> psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
> Type "help" for help.
> 
> postgres=# SELECT * FROM pg_create_logical_replication_slot('mysub',
> 'pgoutput', false, true);
>   slot_name |    lsn
> -----------+-----------
>   mysub     | 0/C19E358
> (1 row)
> 
> postgres=# select * from pg_replication_slots;
> postgres=# \x
> Expanded display is on.
> postgres=# select * from pg_replication_slots;
> -[ RECORD 1 ]-------+----------
> slot_name           | mysub
> plugin              | pgoutput
> slot_type           | logical
> datoid              | 16401
> database            | postgres
> temporary           | f
> active              | t
> active_pid          | 6100
> xmin                |
> catalog_xmin        | 760
> restart_lsn         | 0/C19E320
> confirmed_flush_lsn | 0/C19E358
> wal_status          | reserved
> safe_wal_size       |
> two_phase           | t
> 
> 
> --- the below changes get pushed to subscriber just fine. i did not create
> publication.
> postgres=# insert into t values (3);
> INSERT 0 1
> postgres=# \q
> postgres@controller:~/temp/14$ pg_ctl -D pg14  -l logfile stop
> waiting for server to shut down.... done
> server stopped
> 
> 


-- 
PGP-Key: CDE74120  ☀  computing @ chaos claudius

Thanks Vijay for all your work, you have done to help me to resolve this 
issue.

Am 06.02.22 um 20:03 schrieb Vijaykumar Jain:
> *# so finally, i truncate the table and re-sync :( 

I'm disappointed. )-:

   and everything works,
> but i think i am missing something here. i am pretty sure we *
> *# can play with pg_replication_origin_advance
> or pg_replication_slot_advance to move the lsn to continue subscription
> without a sync, i'll have to spend some time to understand that*
> *# but for now, in summary, subscription breaks in upgrade. After an
> upgrade, we need to enable it back and possibly refresh publication
> (re-sync) from scratch.*
> *# but i am hoping i can be corrected by more experienced people here.*

Anybody out there, succeeding with upgrading a logical replication 
subscriber without resyncing it?

Any help appreciated,
Axel
-- 
PGP-Key: CDE74120  ☀  computing @ chaos claudius

Am 07.02.22 um 18:00 schrieb Axel Rau:
> Anybody out there, succeeding with upgrading a logical replication 
> subscriber without resyncing it?
> 
Here [1] I read, it's unsupported.
To upgrade a subscriber, you just do a fresh installation of the subscriber.

Axel

[1] 
https://www.postgresql.org/message-id/CAA4eK1JGwajs%2BALpTgCf1DfF%2Bhi%2BVOx%3DibU8_Dv_mgSYDFJJMQ%40mail.gmail.com
-- 
PGP-Key: CDE74120  ☀  computing @ chaos claudius

Am 08.02.22 um 17:32 schrieb Axel Rau:
> Am 07.02.22 um 18:00 schrieb Axel Rau:
>> Anybody out there, succeeding with upgrading a logical replication 
>> subscriber without resyncing it?
>>
> Here [1] I read, it's unsupported.
> To upgrade a subscriber, you just do a fresh installation of the 
> subscriber.

And here [1], I read a howto with pg_upgrade on both publisher and 
subscriber with minimum doentime. I will try this and report back.

Axel
[1] https://elephanttamer.net/?p=58

-- 
PGP-Key: CDE74120  ☀  computing @ chaos claudius

> On Feb 7, 2022, at 11:00 AM, Axel Rau <Axel.Rau@chaos1.de> wrote:
>
> Anybody out there, succeeding with upgrading a logical replication subscriber without resyncing it?


I have not tried it, but thought this the idea below would work for upgrading the publisher or subscriber with
pg_upgrade.Plan to test it in a few weeks. If anyone knows it will not work, that will save me some time :). 


Before pg_upgrade on the subscriber:

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

And then after pg_upgrade:

alter subscription my_subscription enable;

John DeSoi, Ph.D.