Обсуждение: Failed upgrade from 12.11 to 14.4

Поиск
Список
Период
Сортировка

Failed upgrade from 12.11 to 14.4

От
Arthur Ramsey
Дата:
"Database instance is in a state that cannot be upgraded: pg_restore: from TOC entry 1264; 1259 32392758 VIEW pg_stat_activity_allusers master pg_restore: error: could not execute query: ERROR: column reference "query" is ambiguous LINE 32: "get_sa"."query" ^ Command was: -- For binary upgrade, must preserve pg_type oid SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('32392760'::pg_catalog.oid); -- For binary upgrade, must preserve pg_type array oid SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('32392759'::pg_catalog.oid); -- For binary upgrade, must preserve pg_class oids SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('32392758'::pg_catalog.oid); CREATE VIEW "publiczugmgeaxkmwph5jgobvwmvzb9freg0s7"."pg_stat_activity_allusers" AS SELECT "get_sa"."datid", "get_sa"."datname", "get_sa"."pid", "get_sa"."usesysid", "get_sa"."usename", "get_sa"."application_name", "get_sa"."client_addr", "get_sa"."client_hostname", "get_sa"."client_port", "get_sa"."backend_start", "get_sa"."xact_start", "get_sa"."query_start", "get_sa"."state_change", "get_sa"."wait_event_type", "get_sa"."wait_event", "get_sa"."state", "get_sa"."backend_xid", "get_sa"."backend_xmin", "get_sa"."query" FROM "publiczugmgeaxkmwph5jgobvwmvzb9freg0s7"."get_sa"() "get_sa"("datid", "datname", "pid", "usesysid", "usename", "application_name", "client_addr", "client_hostname", "client_port", "backend_start", "xact_start", "query_start", "state_change", "wait_event_type", "wait_event", "state", "backend_xid", "backend_xmin", "query", "backend_type");".

Thanks,
Arthur 

Re: Failed upgrade from 12.11 to 14.4

От
Tom Lane
Дата:
Arthur Ramsey <arthur.ramsey@code42.com> writes:
> "Database instance is in a state that cannot be upgraded: pg_restore: from
> TOC entry 1264; 1259 32392758 VIEW pg_stat_activity_allusers master
> pg_restore: error: could not execute query: ERROR: column reference "query"
> is ambiguous LINE 32: "get_sa"."query"

> CREATE VIEW
> "publiczugmgeaxkmwph5jgobvwmvzb9freg0s7"."pg_stat_activity_allusers" AS
> SELECT "get_sa"."datid", "get_sa"."datname", "get_sa"."pid",
> "get_sa"."usesysid", "get_sa"."usename", "get_sa"."application_name",
> "get_sa"."client_addr", "get_sa"."client_hostname", "get_sa"."client_port",
> "get_sa"."backend_start", "get_sa"."xact_start", "get_sa"."query_start",
> "get_sa"."state_change", "get_sa"."wait_event_type", "get_sa"."wait_event",
> "get_sa"."state", "get_sa"."backend_xid", "get_sa"."backend_xmin",
> "get_sa"."query" FROM "publiczugmgeaxkmwph5jgobvwmvzb9freg0s7"."get_sa"()
> "get_sa"("datid", "datname", "pid", "usesysid", "usename",
> "application_name", "client_addr", "client_hostname", "client_port",
> "backend_start", "xact_start", "query_start", "state_change",
> "wait_event_type", "wait_event", "state", "backend_xid", "backend_xmin",
> "query", "backend_type");".

You're really expecting us to intuit a lot from this amount of
detail, aren't you?  But okay, I'll guess: I think this is a
view on pg_stat_activity, and it's not accounting for the fact
that pg_stat_activity gained some columns between v12 and v14.

Probably your best bet is to drop that view, do the upgrade,
and recreate the view with adjustments.

            regards, tom lane



Re: Failed upgrade from 12.11 to 14.4

От
Arthur Ramsey
Дата:
I was unaware this was a user created view.  Dropping the view did the trick thanks for the help.

On Wed, Mar 1, 2023 at 1:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Arthur Ramsey <arthur.ramsey@code42.com> writes:
> "Database instance is in a state that cannot be upgraded: pg_restore: from
> TOC entry 1264; 1259 32392758 VIEW pg_stat_activity_allusers master
> pg_restore: error: could not execute query: ERROR: column reference "query"
> is ambiguous LINE 32: "get_sa"."query"

> CREATE VIEW
> "publiczugmgeaxkmwph5jgobvwmvzb9freg0s7"."pg_stat_activity_allusers" AS
> SELECT "get_sa"."datid", "get_sa"."datname", "get_sa"."pid",
> "get_sa"."usesysid", "get_sa"."usename", "get_sa"."application_name",
> "get_sa"."client_addr", "get_sa"."client_hostname", "get_sa"."client_port",
> "get_sa"."backend_start", "get_sa"."xact_start", "get_sa"."query_start",
> "get_sa"."state_change", "get_sa"."wait_event_type", "get_sa"."wait_event",
> "get_sa"."state", "get_sa"."backend_xid", "get_sa"."backend_xmin",
> "get_sa"."query" FROM "publiczugmgeaxkmwph5jgobvwmvzb9freg0s7"."get_sa"()
> "get_sa"("datid", "datname", "pid", "usesysid", "usename",
> "application_name", "client_addr", "client_hostname", "client_port",
> "backend_start", "xact_start", "query_start", "state_change",
> "wait_event_type", "wait_event", "state", "backend_xid", "backend_xmin",
> "query", "backend_type");".

You're really expecting us to intuit a lot from this amount of
detail, aren't you?  But okay, I'll guess: I think this is a
view on pg_stat_activity, and it's not accounting for the fact
that pg_stat_activity gained some columns between v12 and v14.

Probably your best bet is to drop that view, do the upgrade,
and recreate the view with adjustments.

                        regards, tom lane