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 <sergey(dot)belyashov(at)gmail(dot)com>:
>
> 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