Thread: "column i.indnkeyatts does not exist" in pg_upgrade from 11dev to11b1

I've used pg_upgrade like this before, but maybe from a different (recent)
11dev HEAD; I found: "pg_upgrade supports upgrades from 8.4.X and later to the
current major release of PostgreSQL, including snapshot and beta releases."
(But maybe upgrades FROM beta releases aren't supported in the general case?)

sudo -u postgres /usr/pgsql-11/bin/postgres -D /var/lib/pgsql/11dev0/data
< 2018-05-29 12:06:50.104 CDT  >FATAL:  database files are incompatible with server
< 2018-05-29 12:06:50.104 CDT  >DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO 201803141, but
theserver was compiled with CATALOG_VERSION_NO 201804191. 

sudo -u postgres sh -ec 'cd /var/lib/pgsql; /usr/pgsql-11/bin/pg_upgrade -b /usr/pgsql-11dev0/bin -B /usr/pgsql-11/bin
-d/var/lib/pgsql/11dev0/data -D /var/lib/pgsql/11/data -j2 --link --retain' 
...

command: "/usr/pgsql-11/bin/pg_dump" --host /var/lib/pgsql --port 50432 --username postgres --schema-only
--quote-all-identifiers--binary-upgrade --format=custom  --file="pg_upgrade_dump_16401.custom" 'dbname=c' >>
"pg_upgrade_dump_16401.log"2>&1 
pg_dump: [archiver (db)] query failed: ERROR:  column i.indnkeyatts does not exist
LINE 1: ...atalog.pg_get_indexdef(i.indexrelid) AS indexdef, i.indnkeya...
                                                             ^
HINT:  Perhaps you meant to reference the column "i.indnatts".
pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname AS indexname, inh.inhparent AS parentidx,
pg_catalog.pg_get_indexdef(i.indexrelid)AS indexdef, i.indnkeyatts AS indnkeyatts, i.indnatts AS indnatts, i.indkey,
i.indisclustered,i.indisreplident, t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred, c.tableoid AS
contableoid,c.oid AS conoid, pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname FROM
pg_catalog.pg_tablespaces WHERE s.oid = t.reltablespace) AS tablespace, t.reloptions AS indreloptions FROM
pg_catalog.pg_indexi JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) JOIN pg_catalog.pg_class t2 ON (t2.oid =
i.indrelid)LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid = c.conindid AND c.contype
IN('p','u','x')) LEFT JOIN pg_catalog.pg_inherits inh ON (inh.inhrelid = indexrelid) WHERE i.indrelid =
'19970'::pg_catalog.oidAND (i.indisvalid OR t2.relkind = 'p') AND i.indisready ORDER BY indexname 

Justin Pryzby <pryzby@telsasoft.com> writes:
> I've used pg_upgrade like this before, but maybe from a different (recent)
> 11dev HEAD; I found: "pg_upgrade supports upgrades from 8.4.X and later to the
> current major release of PostgreSQL, including snapshot and beta releases."
> (But maybe upgrades FROM beta releases aren't supported in the general case?)

Yeah, that :-(.  pg_dump's approach to cross-version catalog differences
can only cope with differences between major versions.  So if it sees
a server that calls itself 11-something it's going to think that means
the current catalog layout.  There's no good way to deal with pre-beta
snapshot versions, other than to dump with a pg_dump of the same vintage.

            regards, tom lane

On Tue, May 29, 2018 at 02:00:20PM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > I've used pg_upgrade like this before, but maybe from a different (recent)
> > 11dev HEAD; I found: "pg_upgrade supports upgrades from 8.4.X and later to the
> > current major release of PostgreSQL, including snapshot and beta releases."
> > (But maybe upgrades FROM beta releases aren't supported in the general case?)
> 
> Yeah, that :-(.  pg_dump's approach to cross-version catalog differences
> can only cope with differences between major versions.  So if it sees
> a server that calls itself 11-something it's going to think that means
> the current catalog layout.  There's no good way to deal with pre-beta
> snapshot versions, other than to dump with a pg_dump of the same vintage.

Thanks for confirming.

In this case I worked around it by doing:
 sudo ln -sfv /usr/pgsql-11{dev0,b1}/bin/pg_dump
 sudo ln -sfv /usr/pgsql-11{dev0,b1}/bin/pg_dumpall

I guess, if need be, pg_dump could look at CATALOG_VERSION..

Justin

Justin Pryzby <pryzby@telsasoft.com> writes:
> On Tue, May 29, 2018 at 02:00:20PM -0400, Tom Lane wrote:
>> Yeah, that :-(.  pg_dump's approach to cross-version catalog differences
>> can only cope with differences between major versions.  So if it sees
>> a server that calls itself 11-something it's going to think that means
>> the current catalog layout.  There's no good way to deal with pre-beta
>> snapshot versions, other than to dump with a pg_dump of the same vintage.

> Thanks for confirming.

> In this case I worked around it by doing:
>  sudo ln -sfv /usr/pgsql-11{dev0,b1}/bin/pg_dump
>  sudo ln -sfv /usr/pgsql-11{dev0,b1}/bin/pg_dumpall

> I guess, if need be, pg_dump could look at CATALOG_VERSION..

Yeah, if somebody cared to take on a bunch more maintenance effort,
it'd be possible to distinguish at that level of detail.  So far
it hasn't seemed worth the trouble.

            regards, tom lane