Re: restarting logical replication after upgrading standby

Oh sorry, I missed the part of you upgrading the subscriber as well. I'll
need to check that too, will get back on this tomorrow (too late now)

On Sun, Feb 6, 2022, 4:11 AM Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

>
> On Sun, 6 Feb 2022 at 02:20, Axel Rau <Axel(dot)Rau(at)chaos1(dot)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(at)controller:/var/tmp/log$ pwd
> /var/tmp/log
> postgres(at)controller:/var/tmp/log$ /usr/lib/postgresql/14/bin/initdb -D
> /var/tmp/log/testdb
> postgres(at)controller:/var/tmp/log$ tail postgresql.conf >>
> testdb/postgresql.conf
> postgres(at)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(at)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(at)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(at)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(at)controller:~/temp/13$ initdb -D pg13
> postgres(at)controller:~/temp/13$ cp postgresql.conf pg13/postgresql.conf
> postgres(at)controller:~/temp/13$ pg_ctl -D pg13 -l logfile start
> waiting for server to start.... done
> server started
> postgres(at)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(at)controller:~/temp/13$ pg_ctl -D pg13 -l logfile stop
> waiting for server to shut down.... done
> server stopped
>
>
> ---------------------------------------
> pg14
>
> postgres(at)controller:~/temp/14$ initdb -D /var/lib/postgresql/temp/14/pg14
> #perform upgrade
> postgres(at)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(at)controller:~/temp/14$ cp postgresql.conf pg14/
> postgres(at)controller:~/temp/14$ pg_ctl -D pg14 -l logfile start
> waiting for server to start.... done
> server started
>
> postgres(at)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(at)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(at)controller:~/temp/14$ pg_ctl -D pg14 -l logfile stop
> waiting for server to shut down.... done
> server stopped
>
>
> --
> Thanks,
> Vijay
> LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>
>