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

Hmm... the database itself predates me, so I can't say for sure what
encoding it was created with, but when I did a "pg_dumpall -s" it
showed every database in the cluster uses "SET client_encoding =
'UTF8';"

On Thu, May 9, 2013 at 7:25 PM, Bruce Momjian <bruce@momjian.us> wrote:
> 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 CHARACTER varying(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),
>                 x14 DATE,
>                 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. +