Re: Invalid operation order while producing DB dump

From: Sergey Belyashov <sergey(dot)belyashov(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Invalid operation order while producing DB dump
Date: 2022-02-14 13:47:58
Message-ID: CAOe0RDwM+k5KsvcySxL4C3-x3OjxwK5u2Q20jTb8x7vyYD8oxA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bryn Llewellyn 2022-02-14 17:14:40 Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
Previous Message Emanuel Calvo 2022-02-14 11:09:07 Re: Babelfish for PostgreSQL