Thread: Failure upgrading PG 9.2 to 9.3
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?
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
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
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
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
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
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
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
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
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: 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) ... 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
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