Re: "column i.indnkeyatts does not exist" in pg_upgrade from 11dev to 11b1
Justin Pryzby 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
Re: "column i.indnkeyatts does not exist" in pg_upgrade from 11dev to 11b1
On Tue, May 29, 2018 at 02:00:20PM -0400, Tom Lane wrote:
> Justin Pryzby 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
Re: "column i.indnkeyatts does not exist" in pg_upgrade from 11dev to 11b1
Justin Pryzby 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
"column i.indnkeyatts does not exist" in pg_upgrade from 11dev to 11b1
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 the server 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_tablespace s WHERE s.oid = t.reltablespace) AS tablespace,
t.reloptions AS indreloptions FROM pg_catalog.pg_index i 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.oid AND (i.indisvalid OR t2.relkind = 'p') AND
i.indisready ORDER BY indexname
