Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4

On Thu, May 9, 2013 at 05:41:39PM -0400, Evan D. Hoffman wrote:
> I believe the history of this cluster is that it started on 9.0 and
> was upgraded to 9.1 via pg_upgrade. The instance I'm working on was
> created as a streaming replica, then I broke the replication to make
> it a standalone master specifically for testing pg_upgrade to 9.2.

OK, I did some research on this, using a stripped-down copy of his table
schema:
CREATE TABLE test(    x1 bigint,    x2 bigint,    x3 CHARACTER varying(40),    x4 CHARACTER varying(40),    x5
CHARACTERvarying(40),    x6 CHARACTER varying(40),    x7 CHARACTER varying(40),    x8 CHARACTER varying(40),    x9
boolean,   x10 CHARACTER varying(40),    x11 boolean,    x12 CHARACTER varying(100),    x13 CHARACTER varying(100),
x14DATE,    x15 DATE,    x16 CHARACTER varying(10),    x17 CHARACTER varying(10));
 

Using my default UTF8 encoding, I see a TOAST table created for all
versions of Postgres, 9.0 through 9.3.  However, if I create a database
with C locale/Latin1 encoding:
CREATE DATABASE test3 ENCODING 'LATIN1' LC_COLLATE 'C'     LC_CTYPE 'C' TEMPLATE template0;

I then get a table with no TOAST table.  Could the encoding of this
database have changed?  I know pg_upgrade has always tests to see
old/new clusters had matching "LC_COLLATE" and "LC_CTYPE" match.  It is
possible that the encodings were changed, and pg_upgrade didn't test
that?  I don't think so because pg_dumpall is the one who creates the
new databases with matching encodings.

Could someone have manually changed the encoding of the 9.1 database in
the system tables?  If so, that would cause this problem.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +