Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed

On Mon, Sep 24, 2012 at 11:04:32AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Well, if you run that query on template0 in the old and new cluster, you
> > will see something different in the two of them. Could you have used
> > default in one and a non-dash in the other. Did we change the way we
> > canonicalize the locale between 9.1 and 9.2?
>
> IIRC, we didn't try to canonicalize locale names at all before 9.2.
> That initdb code you're quoting is of fairly recent vintage.

OK, I have developed two patches.

The first fixes the problem of toast tables having oid >
FirstNormalObjectId due to recreating the information_schema as outlined
in the 9.1 release notes. In fact, there are several cases this fixes,
but information_schema was the one reported. The basic problem is that
TOAST tables can't be restricted by schema -- you have to gather the
relations, and then get the toast tables. The good news is that
pg_upgrade caught its own bug and threw an error.

I was able to test this patch by testing the information_schema
recreation, and I checked to see the regression database had the
expected info.c relation count.

The second patch canonicalizes the old cluster's collation and ctype
values pulled from the template0 database.

I was recreate the fix my Debian Squeeze system. Can someone suggestion
a way? I updated pg_database on the old 9.1 cluster to be en_US.UTF8,
while the new cluster defaults to en_US.UTF-8, but pg_upgrade kept them
the same after the setlocale() call and pg_upgrade threw a mismatch
error.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +