pg_upgrade problem as locale difference in data centers

From: Yi Sun <yinan81(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: pg_upgrade problem as locale difference in data centers
Date: 2021-09-17 03:54:05
Message-ID: CABWY_HB4LdgbfxCMFgV5E-YX3p4eJtgLk1f8eoViG9jwdYv6xw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Arnold Morein 2021-09-17 03:55:28 Re: The tragedy of SQL
Previous Message Ron 2021-09-17 03:39:46 Re: The tragedy of SQL