Thread: 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
Attachment
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
Attachment
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
Attachment
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
Thanks,
Vijay
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.github@gmail.com> wrote:
--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/logpostgres@controller:/var/tmp/log$ /usr/lib/postgresql/14/bin/initdb -D /var/tmp/log/testdbpostgres@controller:/var/tmp/log$ tail postgresql.conf >> testdb/postgresql.confpostgres@controller:/var/tmp/log$ tail testdb/postgresql.conf# Add settings for extensions herewal_level=logicalarchive_mode = onarchive_command = '/bin/true'max_wal_size = 48MBmin_wal_size = 32MBshared_buffers = 32MBport = 8001max_logical_replication_workers = 10postgres@controller:/var/tmp/log$ /usr/lib/postgresql/14/bin/pg_ctl -D testdb -l logfile startwaiting for server to start.... doneserver startedpostgres@controller:/var/tmp/log$ psql -p 8001psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))Type "help" for help.postgres=# create table t(id int primary key);CREATE TABLEpostgres=# create subscription mysub connection 'port=5001' publication mypub;NOTICE: created replication slot "mysub" on publisherCREATE SUBSCRIPTIONpostgres=# table t;id----12(2 rows)-- after i do an upgrade it stop getting updates from publisher as no slot on pg14postgres=# 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 finepostgres=# select * from pg_stat_subscription;postgres=# \xExpanded display is on.postgres=# select * from pg_stat_subscription;-[ RECORD 1 ]---------+---------------------------------subid | 16389subname | mysubpid | 6099relid |received_lsn | 0/C1BC8E0last_msg_send_time | 2022-02-06 03:59:07.272526+05:30last_msg_receipt_time | 2022-02-06 03:59:07.272627+05:30latest_end_lsn | 0/C1BC8E0latest_end_time | 2022-02-06 03:59:07.272526+05:30postgres=# table t;-[ RECORD 1 ]id | 1-[ RECORD 2 ]id | 2-[ RECORD 3 ]id | 3------------------------------------------------------------------------------------------------- upgrade from pg13 to pg14postgres@controller:~/temp/13$ tail postgresql.conf# Add settings for extensions herewal_level=logicalarchive_mode = onarchive_command = '/bin/true'max_wal_size = 48MBmin_wal_size = 32MBshared_buffers = 32MBport = 5001max_logical_replication_workers = 10postgres@controller:~/temp/13$ initdb -D pg13postgres@controller:~/temp/13$ cp postgresql.conf pg13/postgresql.confpostgres@controller:~/temp/13$ pg_ctl -D pg13 -l logfile startwaiting for server to start.... doneserver startedpostgres@controller:~/temp/13$ psql -p 5001psql (13.5 (Ubuntu 13.5-2.pgdg20.04+1))Type "help" for help.postgres=# create table t(id int primary key);CREATE TABLEpostgres=# insert into t values (1);INSERT 0 1postgres=# insert into t values (2);INSERT 0 1postgres=# checkpoint;CHECKPOINTpostgres=# create publication mypub for table t;CREATE PUBLICATIONpostgres=# \xExpanded 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 | mysubplugin | pgoutputslot_type | logicaldatoid | 13414database | postgrestemporary | factive | tactive_pid | 5332xmin |catalog_xmin | 489restart_lsn | 0/15E7098confirmed_flush_lsn | 0/15E70D0wal_status | reservedsafe_wal_size |postgres=# \qpostgres@controller:~/temp/13$ pg_ctl -D pg13 -l logfile stopwaiting for server to shut down.... doneserver stopped---------------------------------------pg14postgres@controller:~/temp/14$ initdb -D /var/lib/postgresql/temp/14/pg14#perform upgradepostgres@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" --linkPerforming Consistency Checks-----------------------------Checking cluster versions okChecking database user is the install user okChecking database connection settings okChecking for prepared transactions okChecking for system-defined composite types in user tables okChecking for reg* data types in user tables okChecking for contrib/isn with bigint-passing mismatch okChecking for user-defined encoding conversions okChecking for user-defined postfix operators okCreating dump of global objects okCreating dump of database schemasokChecking for presence of required libraries okChecking database user is the install user okChecking for prepared transactions okChecking for new cluster tablespace directories okIf pg_upgrade fails after this point, you must re-initdb thenew cluster before continuing.Performing Upgrade------------------Analyzing all rows in the new cluster okFreezing all rows in the new cluster okDeleting files from new pg_xact okCopying old pg_xact to new server okSetting oldest XID for new cluster okSetting next transaction ID and epoch for new cluster okDeleting files from new pg_multixact/offsets okCopying old pg_multixact/offsets to new server okDeleting files from new pg_multixact/members okCopying old pg_multixact/members to new server okSetting next multixact ID and offset for new cluster okResetting WAL archives okSetting frozenxid and minmxid counters in new cluster okRestoring global objects in the new cluster okRestoring database schemas in the new clusterokAdding ".old" suffix to old global/pg_control okIf you want to start the old cluster, you will need to removethe ".old" suffix from /var/lib/postgresql/temp/13/pg13/global/pg_control.old.Because "link" mode was used, the old cluster cannot be safelystarted once the new cluster has been started.Linking user relation filesokSetting next OID for new cluster okSync data directory to disk okCreating script to delete old cluster okChecking for extension updates okUpgrade 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-stagesRunning this script will delete the old cluster's data files:./delete_old_cluster.sh#copy the conf filepostgres@controller:~/temp/14$ cp postgresql.conf pg14/postgres@controller:~/temp/14$ pg_ctl -D pg14 -l logfile startwaiting for server to start.... doneserver startedpostgres@controller:~/temp/14$ /usr/lib/postgresql/14/bin/vacuumdb -p 5001 --all --analyze-in-stagesvacuumdb: 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 statisticsvacuumdb: processing database "template1": Generating default (full) optimizer statisticspostgres@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 subscriptionpsql (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=# \xExpanded display is on.postgres=# select * from pg_replication_slots;-[ RECORD 1 ]-------+----------slot_name | mysubplugin | pgoutputslot_type | logicaldatoid | 16401database | postgrestemporary | factive | tactive_pid | 6100xmin |catalog_xmin | 760restart_lsn | 0/C19E320confirmed_flush_lsn | 0/C19E358wal_status | reservedsafe_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 1postgres=# \qpostgres@controller:~/temp/14$ pg_ctl -D pg14 -l logfile stopwaiting for server to shut down.... doneserver stoppedThanks,Vijay
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
Attachment
On Sun, 6 Feb 2022 at 17:18, Axel Rau <Axel.Rau@chaos1.de> wrote:
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.
I just simulated an upgrade of subscriber (mine was 13 to 14 though).
after the upgrade, subscription just stops and is disabled.
looks like it gets disabled in the upgrade.
so i enable it first.
even though subscription is active, the relation which was in subscription was removed from pg_subscription_rel,
so ofcourse it was not rcving updates from the publisher.
below is the initial walkthrough.
--------- post upgrade of subscriber
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
-------+---------+-----+-------+--------------+--------------------+-----------------------+----------------+-----------------
16403 | mysub | | | | | | |
(1 row)
postgres=# select * from pg_subscription;
oid | subdbid | subname | subowner | subenabled | subbinary | substream | subconninfo | subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+-----------+-----------+-------------+-------------+---------------+-----------------
16403 | 16401 | mysub | 10 | f | f | f | port=8001 | mysub | off | {mypub}
(1 row)
postgres=# select * from pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+----------
(0 rows)
# so i enable subscription ( but still relation not subscribing to changes )
postgres=# alter subscription mysub enable;
ALTER SUBSCRIPTION
postgres=# select * from pg_subscription;
oid | subdbid | subname | subowner | subenabled | subbinary | substream | subconninfo | subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+-----------+-----------+-------------+-------------+---------------+-----------------
16403 | 16401 | mysub | 10 | t | f | f | port=8001 | mysub | off | {mypub}
(1 row)
postgres=# select * from pg_stat_subscription;
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_subscription;
-[ RECORD 1 ]---------+---------------------------------
subid | 16403
subname | mysub
pid | 1726
relid |
received_lsn | 0/15E37F0
last_msg_send_time | 2022-02-07 00:11:31.623328+05:30
last_msg_receipt_time | 2022-02-07 00:11:31.623353+05:30
latest_end_lsn | 0/15E37F0
latest_end_time | 2022-02-07 00:11:31.623328+05:30
#but still getting no changes as relation not in subscription
postgres=# select * from pg_subscription_rel;
(0 rows)
postgres=# \dt
List of relations
-[ RECORD 1 ]----
Schema | public
Name | t
Type | table
Owner | postgres
# so i try refresh publication, but that is like a re -sync, given the table already has data, it errors due to pkey conflict and aborts.
postgres=# alter subscription mysub refresh publication ;
ALTER SUBSCRIPTION
postgres=# select * from pg_subscription_rel;
-[ RECORD 1 ]-----
srsubid | 16403
srrelid | 16384
srsubstate | d ---- this is COPY
srsublsn |
# so finally, i truncate the table and re-sync :( 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.
postgres=# truncate table t;
TRUNCATE TABLE
postgres=# alter subscription mysub refresh publication ;
ALTER SUBSCRIPTION
postgres=# table t;
id
----
1
2
3
(3 rows)
#table re-enabled and is ready
postgres=# select * from pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+-----------
16403 | 16384 | r | 0/15E3E28
(1 row)
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
On Mon, 7 Feb 2022 at 00:33, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:
# so finally, i truncate the table and re-sync :( 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.
I am sorry, I have messed up the mail flow. i'll probably use gist for code dump or use an attachment in the future, and then stick to inlining.
now,
ok I just tried it again with copy_data = false, i was able to get the changes *after* refresh was done, but I lost the changes before i re-enabled subscription and refresh publication.
postgres=# table pg_stat_subscription;
-[ RECORD 1 ]---------+------
subid | 16403
subname | mysub
pid |
relid |
received_lsn |
last_msg_send_time |
last_msg_receipt_time |
latest_end_lsn |
latest_end_time |
postgres=# table pg_subscription;
-[ RECORD 1 ]---+----------
oid | 16403
subdbid | 16401
subname | mysub
subowner | 10
subenabled | f
subbinary | f
substream | f
subconninfo | port=8001
subslotname | mysub
subsynccommit | off
subpublications | {mypub}
postgres=# table pg_subscription_rel;
(0 rows)
postgres=# alter subscription mysub refresh publication with ( copy_data = false );
ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptions
postgres=# alter subscription mysub enable;
ALTER SUBSCRIPTION
postgres=# table pg_stat_subscription;
-[ RECORD 1 ]---------+---------------------------------
subid | 16403
subname | mysub
pid | 3007
relid |
received_lsn | 0/15E35F0
last_msg_send_time | 2022-02-07 00:53:09.760154+05:30
last_msg_receipt_time | 2022-02-07 00:53:09.760176+05:30
latest_end_lsn | 0/15E35F0
latest_end_time | 2022-02-07 00:53:09.760154+05:30
postgres=# table pg_subscription;
-[ RECORD 1 ]---+----------
oid | 16403
subdbid | 16401
subname | mysub
subowner | 10
subenabled | t
subbinary | f
substream | f
subconninfo | port=8001
subslotname | mysub
subsynccommit | off
subpublications | {mypub}
postgres=# table pg_subscription_rel;
(0 rows)
postgres=# alter subscription mysub refresh publication with ( copy_data = false );
ALTER SUBSCRIPTION
postgres=# table pg_subscription_rel;
-[ RECORD 1 ]-----
srsubid | 16403
srrelid | 16384
srsubstate | r
srsublsn |
# i inserted '3' at the publisher instance, but it did not get reflected here.
postgres=# table t;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2
# but when i insert a new row '4' at publisher i could get the changes just fine, but still not '3'..
# so i am doing something wrong or not using the origin advance function correctly, somewhere, but i hope this gives you some idea on how to check
postgres=# table t;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2
-[ RECORD 3 ]
id | 4
at publisher
postgres=# table t;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2
-[ RECORD 3 ]
id | 3 -- this is missing
-[ RECORD 4 ]
id | 4
btw,
you can look at the inplace major version upgrade. although this is on the top of patroni, but just in case.
and
https://github.com/chobostar/ansible-playbook-upgrade-pg (mostly for the references)
Thanks,
Vijay
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
Attachment
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
Attachment
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
Attachment
> 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.
Am 08.02.2022 um 18:25 schrieb Axel Rau <Axel.Rau@chaos1.de>:
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
I implemented this recipe in ansible and it upgraded 2 subscribers and one publisher in 4 minutes. (-;
Axel
---
PGP-Key: CDE74120 ☀ computing @ chaos claudius
PGP-Key: CDE74120 ☀ computing @ chaos claudius