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
