Failure upgrading PG 9.2 to 9.3

Started by Sam Saffronabout 12 years ago11 messagesgeneral

sam.saffron@gmail.com

about 12 years ago

I am getting the following failure on a customer DB upgrading 9.2 to 9.3

Selecting previously unselected package postgresql-9.2.
Unpacking postgresql-9.2 (from
.../postgresql-9.2_9.2.8-1.pgdg12.4+1_amd64.deb) ...
Processing triggers for postgresql-common ...
Setting up postgresql-client-9.2 (9.2.8-1.pgdg12.4+1) ...
Setting up postgresql-9.2 (9.2.8-1.pgdg12.4+1) ...
Creating new cluster 9.2/main ...
config /etc/postgresql/9.2/main
data /var/lib/postgresql/9.2/main
locale C
port 5433
* Starting PostgreSQL 9.2 database server
...done.
* Stopping PostgreSQL 9.2 database server
...done.
* Stopping PostgreSQL 9.3 database server
...done.
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON
c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_index i ON
c.oid = i.indexrelid WHERE relkind IN ('r', 'm', 'i', 'S') AND
i.indisvalid IS DISTINCT FROM false AND i.indisready IS DISTINCT FROM
false AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~
'^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog',
'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >=
16384) OR (n.nspname = 'pg_catalog' AND relname IN
('pg_largeobject', 'pg_largeobject_loid_pn_index',
'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index') ));
ERROR: operator does not exist: name !~ unknown
LINE 1: ...disready IS DISTINCT FROM false AND ((n.nspname !~ '^pg_te...
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

----

Why would

"ERROR: operator does not exist: name !~ unknown"

Come up ?

Any way to work around this?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

about 12 years ago

In reply to: Sam Saffron (#1)

Re: Failure upgrading PG 9.2 to 9.3

On 03/24/2014 04:58 PM, Sam Saffron wrote:

I am getting the following failure on a customer DB upgrading 9.2 to 9.3

Selecting previously unselected package postgresql-9.2.
Unpacking postgresql-9.2 (from
.../postgresql-9.2_9.2.8-1.pgdg12.4+1_amd64.deb) ...
Processing triggers for postgresql-common ...
Setting up postgresql-client-9.2 (9.2.8-1.pgdg12.4+1) ...
Setting up postgresql-9.2 (9.2.8-1.pgdg12.4+1) ...
Creating new cluster 9.2/main ...
config /etc/postgresql/9.2/main
data /var/lib/postgresql/9.2/main
locale C
port 5433
* Starting PostgreSQL 9.2 database server
...done.
* Stopping PostgreSQL 9.2 database server
...done.
* Stopping PostgreSQL 9.3 database server
...done.
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON
c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_index i ON
c.oid = i.indexrelid WHERE relkind IN ('r', 'm', 'i', 'S') AND
i.indisvalid IS DISTINCT FROM false AND i.indisready IS DISTINCT FROM
false AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~
'^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog',
'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >=
16384) OR (n.nspname = 'pg_catalog' AND relname IN
('pg_largeobject', 'pg_largeobject_loid_pn_index',
'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index') ));
ERROR: operator does not exist: name !~ unknown
LINE 1: ...disready IS DISTINCT FROM false AND ((n.nspname !~ '^pg_te...
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

----

Why would

"ERROR: operator does not exist: name !~ unknown"

Come up ?

Any way to work around this?

If you going from 9.2 --> 9.3 why are you installing 9.2?

Are you sure you are working in the right direction?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane

tgl@sss.pgh.pa.us

about 12 years ago

In reply to: Sam Saffron (#1)

Re: Failure upgrading PG 9.2 to 9.3

Sam Saffron <sam.saffron@gmail.com> writes:

Why would
"ERROR: operator does not exist: name !~ unknown"
Come up ?

It's hard to explain that as anything except corrupted system catalogs
in your existing database :-(. If you were really lucky, reindexing
pg_operator would fix it; but since pg_operator is usually pretty static,
it seems unlikely that it suffered index corruption.

Any way to work around this?

Rather than relying on pg_upgrade, you could try using pg_dump(all)
to extract the data. With some luck, pg_dump wouldn't be affected by
whatever has happened to the pg_operator catalog.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sam Saffron

sam.saffron@gmail.com

about 12 years ago

In reply to: Tom Lane (#3)

Re: Failure upgrading PG 9.2 to 9.3

Thanks heaps Tom,

I can confirm corrupt db upgrades fine with pg_dump. Was wondering if
there are any plans to add a --no-validate to pg_upgrade, since the
crash seems only to happen during validation.

Cheers
Sam

On Wed, Mar 26, 2014 at 3:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Sam Saffron <sam.saffron@gmail.com> writes:

Why would
"ERROR: operator does not exist: name !~ unknown"
Come up ?

It's hard to explain that as anything except corrupted system catalogs
in your existing database :-(. If you were really lucky, reindexing
pg_operator would fix it; but since pg_operator is usually pretty static,
it seems unlikely that it suffered index corruption.

Any way to work around this?

Rather than relying on pg_upgrade, you could try using pg_dump(all)
to extract the data. With some luck, pg_dump wouldn't be affected by
whatever has happened to the pg_operator catalog.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane

tgl@sss.pgh.pa.us

about 12 years ago

In reply to: Sam Saffron (#4)

Re: Failure upgrading PG 9.2 to 9.3

Sam Saffron <sam.saffron@gmail.com> writes:

I can confirm corrupt db upgrades fine with pg_dump. Was wondering if
there are any plans to add a --no-validate to pg_upgrade, since the
crash seems only to happen during validation.

Skipping validation would probably just result in the same error happening
later, when it's too late to back out easily ...

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

about 12 years ago

In reply to: Sam Saffron (#4)

Re: Failure upgrading PG 9.2 to 9.3

On 03/25/2014 04:32 PM, Sam Saffron wrote:

Thanks heaps Tom,

I can confirm corrupt db upgrades fine with pg_dump. Was wondering if
there are any plans to add a --no-validate to pg_upgrade, since the
crash seems only to happen during validation.

Hmm, so I am still unclear on this. The 'corrupt' database is the one
you upgraded away from or to? If to I am not sure you have solved
anything. For the sake of discussion I am assuming you did a pg_dump on
the 9.2 instance and a restore on the 9.3 instance. Is this correct?

Cheers
Sam

On Wed, Mar 26, 2014 at 3:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Sam Saffron <sam.saffron@gmail.com> writes:

Why would
"ERROR: operator does not exist: name !~ unknown"
Come up ?

It's hard to explain that as anything except corrupted system catalogs
in your existing database :-(. If you were really lucky, reindexing
pg_operator would fix it; but since pg_operator is usually pretty static,
it seems unlikely that it suffered index corruption.

Any way to work around this?

Rather than relying on pg_upgrade, you could try using pg_dump(all)
to extract the data. With some luck, pg_dump wouldn't be affected by
whatever has happened to the pg_operator catalog.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sam Saffron

sam.saffron@gmail.com

about 12 years ago

In reply to: Adrian Klaver (#6)

Re: Failure upgrading PG 9.2 to 9.3

Yes Adrian,

That is correct (upgraded 9.2 to 9.3 via pg_dump), more than happy to
provide pg devs with the actual db if needed.

Pretty sure the target db is good, especially since we just dumped a
single db (did not do a dump_all)

On Wed, Mar 26, 2014 at 10:58 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 03/25/2014 04:32 PM, Sam Saffron wrote:

Thanks heaps Tom,

I can confirm corrupt db upgrades fine with pg_dump. Was wondering if
there are any plans to add a --no-validate to pg_upgrade, since the
crash seems only to happen during validation.

Hmm, so I am still unclear on this. The 'corrupt' database is the one you
upgraded away from or to? If to I am not sure you have solved anything. For
the sake of discussion I am assuming you did a pg_dump on the 9.2 instance
and a restore on the 9.3 instance. Is this correct?

Cheers
Sam

On Wed, Mar 26, 2014 at 3:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Sam Saffron <sam.saffron@gmail.com> writes:

Why would
"ERROR: operator does not exist: name !~ unknown"
Come up ?

It's hard to explain that as anything except corrupted system catalogs
in your existing database :-(. If you were really lucky, reindexing
pg_operator would fix it; but since pg_operator is usually pretty static,
it seems unlikely that it suffered index corruption.

Any way to work around this?

Rather than relying on pg_upgrade, you could try using pg_dump(all)
to extract the data. With some luck, pg_dump wouldn't be affected by
whatever has happened to the pg_operator catalog.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

about 12 years ago

In reply to: Sam Saffron (#7)

Re: Failure upgrading PG 9.2 to 9.3

On 03/25/2014 05:03 PM, Sam Saffron wrote:

Yes Adrian,

That is correct (upgraded 9.2 to 9.3 via pg_dump), more than happy to
provide pg devs with the actual db if needed.

Pretty sure the target db is good, especially since we just dumped a
single db (did not do a dump_all)

Well it has more to do with which Postgres instance you are calling
corrupted the the 9.2 or the 9.3?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sam Saffron

sam.saffron@gmail.com

about 12 years ago

In reply to: Adrian Klaver (#8)

Re: Failure upgrading PG 9.2 to 9.3

9.2 is the problem instance, 9.3 is clean, I am able to do many
upgrades without issues with the same script (which spawns a clean 9.3
instance and then pg_upgrades to it.)

On Wed, Mar 26, 2014 at 11:13 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 03/25/2014 05:03 PM, Sam Saffron wrote:

Yes Adrian,

That is correct (upgraded 9.2 to 9.3 via pg_dump), more than happy to
provide pg devs with the actual db if needed.

Pretty sure the target db is good, especially since we just dumped a
single db (did not do a dump_all)

Well it has more to do with which Postgres instance you are calling
corrupted the the 9.2 or the 9.3?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#10

Adrian Klaver

adrian.klaver@aklaver.com

about 12 years ago

In reply to: Sam Saffron (#9)

Re: Failure upgrading PG 9.2 to 9.3

On 03/25/2014 05:14 PM, Sam Saffron wrote:

9.2 is the problem instance, 9.3 is clean, I am able to do many
upgrades without issues with the same script (which spawns a clean 9.3
instance and then pg_upgrades to it.)

Alright that makes sense, though I am still unclear about the below from
your original post:

If the 9.2 instance was already there, why was 9.2 being installed? Is
it possible that it was installed over a running version?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#11

Adrian Klaver

adrian.klaver@aklaver.com

about 12 years ago

In reply to: Sam Saffron (#1)

Re: Failure upgrading PG 9.2 to 9.3

On 03/25/2014 05:23 PM, Sam Saffron wrote:

Sorry, its part of a rather elaborate docker based upgrade, that
install is just done to get the binaries, the data is all in a
completely different location which is untouched.

So there are two instances of 9.2 in play at one time?
The upgrade process is not inadvertently cross referencing the two?

I realize Toms suggestion got you past the error, just trying to figure
what corrupted the system catalogs in the first place. I am assuming the
9.2 instance that became corrupted was running properly until the upgrade?

On Wed, Mar 26, 2014 at 11:20 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 03/25/2014 05:14 PM, Sam Saffron wrote:

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Import Notes

Reply to msg id not found: CAAtdryPg1mepHnBqnoL1qsv9Eh8F0j-UEEuCU61xWtgDUWpzGA@mail.gmail.com