Logical replication issue after Postgresql upgrade from 13 to 14
I have an issue with logical replication after Postgresql upgrade from 13 to 14 (upgraded subscriber only using pg_upgrade_cluster -m link 13 main). After upgrade all subscriptions were disabled so I have enabled them and replication workers successfully started. pg_stat_subscription contains list of all subscriptions. All times in this table is near current time (replication workers receives data from servers). But no changes in destination table since cluster upgrade (on publishers tables are changed). What I'm doing wrong and how to fix issue? Sergey Belyashov
Re: Logical replication issue after Postgresql upgrade from 13 to 14
I think there are some bugs in Posgresql logical replication upgrade. Because dropping and recreating subscriptions with manual synchronization has solved the problem for me. But it is not the correct way, IMHO. Sergey Belyashov ср, 1 дек. 2021 г. в 15:26, Marcos Pegoraro : > > >> I have an issue with logical replication after Postgresql upgrade from >> 13 to 14 (upgraded subscriber only using pg_upgrade_cluster -m link 13 >> main). After upgrade all subscriptions were disabled so I have enabled >> them and replication workers successfully started. >> pg_stat_subscription contains list of all subscriptions. All times in >> this table is near current time (replication workers receives data >> from servers). But no changes in destination table since cluster >> upgrade (on publishers tables are changed). What I'm doing wrong and >> how to fix issue? > > > Amit, wouldn't it be better to document all steps needed to use pg_upgrade > with logical replication ? > Sergey is showing a different problem than mine.
Upgrade 13 to 14 with replication of partitioned table
Hi,
I have tried to upgrade my cluster from version 13 to 14 using the
command: "pg_upgradecluster -m links --no-start 13 main". But upgrade
failed with messages:
-
pg_restore: creating INDEX "public.closed_sessions_closed_id_idx"
pg_restore: while processing header:
pg_restore: from header entry 3195; 1259 29484835 INDEX
closed_sessions_closed_id_idx postgres
pg_restore: error: could not execute query: ERROR: cannot use invalid
index "closed_sessions_closed_id_idx" as replica identity
While executing command:
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_index_pg_class_oid('29484835'::pg_catalog.oid);
CREATE UNIQUE INDEX "closed_sessions_closed_id_idx" ON ONLY
"public"."closed_sessions" USING "btree" ("closed", "id");
ALTER TABLE ONLY "public"."closed_sessions" REPLICA IDENTITY USING
INDEX "closed_sessions_closed_id_idx";
---
closed_sessions is partitioned table and column closed is used for
replica identity index.
Cluster version 13 works fine...
How can I upgrade the cluster?
Sergey Belyashov.
Invalid operation order while producing DB dump
Hi,
I have tried to upgrade my cluster from version 13 to 14 using the
command: "pg_upgradecluster -m links --no-start 13 main". But upgrade
failed with messages:
-
pg_restore: creating INDEX "public.closed_sessions_closed_id_idx"
pg_restore: while processing header:
pg_restore: from header entry 3195; 1259 29484835 INDEX
closed_sessions_closed_id_idx postgres
pg_restore: error: could not execute query: ERROR: cannot use invalid
index "closed_sessions_closed_id_idx" as replica identity
While executing command:
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_index_pg_class_oid('29484835'::pg_catalog.oid);
CREATE UNIQUE INDEX "closed_sessions_closed_id_idx" ON ONLY
"public"."closed_sessions" USING "btree" ("closed", "id");
ALTER TABLE ONLY "public"."closed_sessions" REPLICA IDENTITY USING
INDEX "closed_sessions_closed_id_idx";
---
closed_sessions is partitioned table and column closed is used for
replica identity index.
Cluster version 13 works fine...
I think pg_dump generates invalid order of SQL commands causing it to
create a replica identity index for partition tables without connected
partitions. Also it is possible that there is an error in the very
strict checking for invalid index.
Sergey Belyashov
Re: Invalid operation order while producing DB dump
As a workaround I have removed REPLICA IDENTITY from table
closed_sessions, do cluster upgrade, and then add REPLICA IDENTITY
back.
Sergey Belyashov
чт, 10 февр. 2022 г. в 15:09, Sergey Belyashov :
>
> Hi,
> I have tried to upgrade my cluster from version 13 to 14 using the
> command: "pg_upgradecluster -m links --no-start 13 main". But upgrade
> failed with messages:
> -
> pg_restore: creating INDEX "public.closed_sessions_closed_id_idx"
> pg_restore: while processing header:
> pg_restore: from header entry 3195; 1259 29484835 INDEX
> closed_sessions_closed_id_idx postgres
> pg_restore: error: could not execute query: ERROR: cannot use invalid
> index "closed_sessions_closed_id_idx" as replica identity
> While executing command:
> -- For binary upgrade, must preserve pg_class oids
> SELECT
> pg_catalog.binary_upgrade_set_next_index_pg_class_oid('29484835'::pg_catalog.oid);
>
> CREATE UNIQUE INDEX "closed_sessions_closed_id_idx" ON ONLY
> "public"."closed_sessions" USING "btree" ("closed", "id");
>
> ALTER TABLE ONLY "public"."closed_sessions" REPLICA IDENTITY USING
> INDEX "closed_sessions_closed_id_idx";
> ---
> closed_sessions is partitioned table and column closed is used for
> replica identity index.
> Cluster version 13 works fine...
>
> I think pg_dump generates invalid order of SQL commands causing it to
> create a replica identity index for partition tables without connected
> partitions. Also it is possible that there is an error in the very
> strict checking for invalid index.
>
> Sergey Belyashov
