Re: Mixed Locales and Upgrading

Don Seiler <don(at)seiler(dot)us> writes:
> However this database has encoding UTF8 while still having ctype and
> collation of en_US. I've since found that when this was last upgraded, they
> ran "update pg_database set encoding = pg_char_to_encoding('UTF8') where
> datname = 'test';" to change the encoding.

Egad.

> In my testing, pg_upgrade breaks
> when trying to restore this since UTF8 isn't supported in en_US for the CREATE
> DATABASE command used during pg_restore:

Well, in principle you could likewise manually update pg_database's
datcollate and datctype columns to say "en_US.utf8". However, there's
a much bigger problem here --- what steps if any did this cowboy take
to ensure that the data inside the database was valid UTF8?

I don't think you should use pg_upgrade here at all. A dump/restore
is really the only way to make sure that you have validly encoded data.

However, if it's only one database out of a bunch, you could do something
like

* pg_dump that one database;
* drop said database;
* pg_upgrade everything else;
* restore that one database from dump.

regards, tom lane