Re: Mixed Locales and Upgrading

Good morning,

Back with a follow-up question to all this. I'm wondering if we shouldn't
also change the locale settings for postgres/template0/template1 to match
our new desires setting en_US.UTF-8 with UTF8 encoding. We haven't written
anything to postgres. Some of our DB clusters have changed these already
(but still leaving things mixed) but I want to make things uniform for
future upgrades to go a lot more smoothly.

Some examples of current DB clusters that need to be fixed. The first one
has the more problematic incompatible mixing within the DBs (the original
point of this thread), the others just have different settings across DBs
that I'd like change going forward as well.

datname | encoding | datcollate | datctype | size_mb
--------------------+----------+------------+----------+---------
xxxxxxxxxxxxxxxxxx | UTF8 | en_US | en_US | 1390789
postgres | UTF8 | en_US | en_US | 6
template0 | UTF8 | en_US | en_US | 6
template1 | UTF8 | en_US | en_US | 6

datname | encoding | datcollate | datctype | size_mb
----------------------+----------+------------+------------+---------
xxxxxxxxxxxxxxxxxxx | UTF8 | en_US.utf8 | en_US.utf8 | 2178
postgres | LATIN1 | en_US | en_US | 7
template0 | LATIN1 | en_US | en_US | 7
template1 | UTF8 | en_US.utf8 | en_US.utf8 | 7

datname | encoding | datcollate | datctype | size_mb
-----------+----------+------------+------------+---------
xxxxxx | UTF8 | en_US.utf8 | en_US.utf8 | 345
postgres | LATIN1 | en_US | en_US | 7
template0 | UTF8 | en_US.utf8 | en_US.utf8 | 7
template1 | UTF8 | en_US.utf8 | en_US.utf8 | 7

For the smaller DBs I was planning to just dump/restore into a new cluster
as I upgrade to PG12 as well. However two that have the problem are the two
biggest ones where the downtime for dump/restore would be too great. So I'm
wondering if there is risk or harm in running an UPDATE pg_database command
on postgres/template0/template1 as needed and re-indexing afterward.

--
Don Seiler
www.seiler.us