Re: pg_upgrade from 9.3 to 9.4 fails

From: Yun <yguo(at)cvent(dot)com<mailto:yguo(at)cvent(dot)com>>
Date: Monday, October 5, 2015 at 12:19 PM
To: "pgsql-admin-owner(at)postgresql(dot)org<mailto:pgsql-admin-owner(at)postgresql(dot)org>" <pgsql-admin-owner(at)postgresql(dot)org<mailto:pgsql-admin-owner(at)postgresql(dot)org>>
Subject: pg_upgrade from 9.3 to 9.4 fails

Hi,

I’m trying to use pg_upgrade to upgrade 9.3 to 9.4. And I observed weird behavior, that if I have the old 9.3 server up and running the check would pass, but if I shut it down it would fail. Any idea why would it behave this way? Is there a workaround for this issue?

With old 9.3 running:
-bash-4.1$ ps -ef | grep pg.old
postgres 16243 1 0 16:12 pts/0 00:00:00 /usr/pgsql-9.3/bin/postgres -D /data/pg.old
postgres 16261 15160 0 16:13 pts/0 00:00:00 grep pg.old
-bash-4.1$ pg_upgrade -b /usr/pgsql-9.3/bin -B /usr/pgsql-9.4/bin -d /data/pg.old -D /data/pg --check

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "line" user columns ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

*Clusters are compatible*

When 9.3 is shut down:

-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl stop -D /data/pg.old
waiting for server to shut down.... done
server stopped
-bash-4.1$ pg_upgrade -b /usr/pgsql-9.3/bin -B /usr/pgsql-9.4/bin -d /data/pg.old -D /data/pg --check
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: cache lookup failed for type 1670699

Failure, exiting