Re: pg_upgrade problem as locale difference in data centers

[Yi Sun <yinan81@xxxxxxxxx>]

Hi Tom,

Thank you for your help.

As we use ansible to deploy the upgrade, so mentioned the data centers situation. The PostgreSQL is single node and the upgrade will be in the same data center and same Linux server(Centos 7), just will run the ansible to upgrade PG in all Data centers.

For example, in our aaa data center

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
 aaa_service                  |  ru_RU.UTF-8             |  ru_RU.UTF-8

we test before pg_upgrade, update the postgres, template1, template0 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 and Locale is like below:

postgres=# select datname,datcollate,datctype from pg_database;
            datname            | datcollate  |  datctype
-------------------------------+-------------+-------------
 postgres                       |  en_US.UTF-8             |  en_US.UTF-8
 template1                     |  en_US.UTF-8             |  en_US.UTF-8
 template0                     |  en_US.UTF-8             |  en_US.UTF-8
 aaa_service                  |  ru_RU.UTF-8             |  ru_RU.UTF-8

The aaa_service application database Locale is no change, and there is no user-defined table or index in postgres, template1, template0 3 databases. So the question is like our case, if update the postgres, template1, template0 3 databases locale and upgrade this way has problems and risk? If we have to get away with it and choose pg_dump-and-restore or pglogical? Thanks

Best regards
Sun Yi