Don Seiler <don@xxxxxxxxx> 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
