Hello,
We want to upgrade some PG9.6 DB to PG13, the databases locale are different in data centers, some are C, some are ru_RU.UTF-8 and so on... as below
postgres=# select datname,datcollate,datctype from pg_database;
datname | datcollate | datctype
-------------------------------+-------------+-------------
postgres | C | C
template1 | C | C
template0 | C | C
aaa_service | C | C
postgres=# select datname,datcollate,datctype from pg_database;
datname | datcollate | datctype
--------------------------------+-------------+-------------
postgres | ru_RU.UTF-8 | ru_RU.UTF-8
template1 | ru_RU.UTF-8 | ru_RU.UTF-8
template0 | ru_RU.UTF-8 | ru_RU.UTF-8
bbb_service | ru_RU.UTF-8 | ru_RU.UTF-8
We use 1 ansible script to proceed the upgrade in data centers, as pg_upgrade will only work once postgres, template1, template0 3 databases locale are same between PG9.6 and PG13, so we test before pg_upgrade, update the 3 databases locale to 'en_US.UTF-8' both in PG9.6 and PG13 as below script
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')
Then test pg_upgrade completed with no error, but as pg documentation says like"collation can't be changed after database creation. Only drop and recreate", the question is if we can update and upgrade this way, will it have problems?
Thanks and best regards
Sun Yi