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
--------------------+----------+------------+----------+---------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
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
----------------------+----------+------------+------------+---------
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
-----------+----------+------------+------------+---------
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
www.seiler.us
