Re: restarting logical replication after upgrading standby - Mailing list pgsql-admin

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