Yi Sun <yinan81@gmail.com> writes:
> update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
> where datname in ('postgres','template1','template0') and
> (datcollate<>'en_US.UTF-8' or datctype<>'en_US.UTF-8')
This seems like a pretty bad idea. You might get away with it if you've
not added any user-defined indexes in any of those databases, but it's
definitely a case where if things go wrong you'll be told it's your
own fault.
The context of "moving to a new datacenter and also updating across four
Postgres versions" suggests strongly to me that you are also planning a
move to a new OS version, in which case you had better read
https://wiki.postgresql.org/wiki/Locale_data_changes
In short, it seems to me that you are at very great risk of ending
up with corrupt (incorrectly ordered) indexes on textual columns.
If you don't mind reindexing all of those after the update, you
could proceed with this plan. Otherwise, pg_dump-and-restore might
be a safer idea.
regards, tom lane