Bug 1007802 - pg_upgrade can fail when environment changed with error "lc_collate cluster values do not match..."
Summary: pg_upgrade can fail when environment changed with error "lc_collate cluster v...
Alias: None
Product: Fedora
Classification: Fedora
Component: postgresql
Version: 21
Hardware: Unspecified
OS: Unspecified
Target Milestone: ---
Assignee: Pavel Raiskup
QA Contact: Fedora Extras Quality Assurance
Depends On: 1052063
TreeView+ depends on / blocked
Reported: 2013-09-13 10:56 UTC by Honza Horak
Modified: 2015-10-17 13:20 UTC (History)
10 users (show)

Fixed In Version: postgresql-9.3.5-5.fc21
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed: 2014-10-29 11:02:12 UTC
Type: Bug

Attachments (Terms of Use)

System ID Private Priority Status Summary Last Updated
Red Hat Bugzilla 958045 1 None None None 2021-01-20 06:05:38 UTC

Description Honza Horak 2013-09-13 10:56:56 UTC
Description of problem:
IIUIC, Collate and Ctype settings of database objects (tables at least) when creating are taken from current $LANG environment variable if not specified explicitly using --lc-collate when running initdb. So, when I initialize postgresql database cluster with $LANG set to en_US.UTF-8 (defined so in /etc/locale.conf), the tables will look like the following:

postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

Then, when I change /etc/locale.conf to a different value (actually I don't remember I done it consciously, but it had to be changed by someone/something probably), say en_US.utf8, the next postgresql upgrade fails because it checks the collations to be the same with the following error message:

lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_US.utf8"

That's because a new cluster is initialized in one step during upgrading process and thus the new collation is used (the one set in /etc/locale.conf).

Version-Release number of selected component (if applicable):

How reproducible:

Steps to Reproduce:
1. start with postgresql-9.2.x
2. # systemctl stop postgresql
3. # mv /var/lib/pgsql/{data,backup-data}
4. set LANG=en_US.UTF-8 in /etc/locale.conf
5. # postgresql-setup initdb
6. upgrade to postgresql-9.3.x
7. Change LANG=en_US.utf8 in /etc/locale.conf
9. # postgresql-setup upgrade

Actual results:
upgrade fails, /var/lib/pgsql/pgupgrade.log includes:
lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_US.utf8"

Expected results:
upgrade succeeds

Additional info:
IMHO, the only thing postgresql can do better about that is to explicitly define the same collation in initdb call (called in postgresql-setup script) as the former data stack had (automatically or offer a way to define it by hand), since this is required in the 5th step of pg_upgrade in [1]:

"Initialize the new PostgreSQL cluster
Initialize the new cluster using initdb. Again, use compatible initdb flags that match the old cluster. Many prebuilt installers do this step automatically. There is no need to start the new cluster."

Anyway, even if this is not done by postgresql automatically, this problem during upgrading can be quite easily fixed by changing /etc/locale.conf value to match older collate settings for the time of upgrading.

[1] http://www.postgresql.org/docs/9.3/static/pgupgrade.html

Comment 1 Honza Horak 2013-09-13 12:19:58 UTC
Not sure but it can be related: bug #1007802

Comment 2 Honza Horak 2013-09-13 12:21:50 UTC
(In reply to Honza Horak from comment #1)
> Not sure but it can be related: bug #1007802

I meant bug #958045

Comment 3 Pavel Raiskup 2014-01-08 09:48:12 UTC
I tried to propose possible solution upstream:

Comment 4 Pavel Raiskup 2014-10-14 12:52:53 UTC
*** Bug 1152556 has been marked as a duplicate of this bug. ***

Comment 5 Pavel Raiskup 2014-10-14 13:05:51 UTC
The bug 1052063 introduced the way how to specify concrete locale for
'initdb' which is done during 'postgresql-setup upgrade' -- for the case that
the system locale does not match that one which was system default during the
initial 'initdb' run for PG data directory:

   PGSETUP_INITDB_OPTIONS="--locale=en_US.UTF-8" postgresql-setup upgrade

Substitute the 'en_US.UTF-8' with your 'old' locale.  That way you may
work-around also the issue with 'en_US.utf8' ~> 'en_US.UTF-8' change.

Comment 6 udo.rader 2014-10-14 13:56:15 UTC
thanks Pavel, your workaround fixes my problem.

Comment 7 Fedora Update System 2014-10-20 11:15:50 UTC
postgresql-9.3.5-2.fc20 has been submitted as an update for Fedora 20.

Comment 8 Fedora Update System 2014-10-20 11:16:31 UTC
postgresql-9.3.5-5.fc21 has been submitted as an update for Fedora 21.

Comment 9 Fedora Update System 2014-10-20 15:31:11 UTC
Package postgresql-9.3.5-5.fc21:
* should fix your issue,
* was pushed to the Fedora 21 testing repository,
* should be available at your local mirror within two days.
Update it with:
# su -c 'yum update --enablerepo=updates-testing postgresql-9.3.5-5.fc21'
as soon as you are able to.
Please go to the following url:
then log in and leave karma (feedback).

Comment 10 Fedora Update System 2014-10-29 11:02:12 UTC
postgresql-9.3.5-2.fc20 has been pushed to the Fedora 20 stable repository.  If problems still persist, please make note of it in this bug report.

Comment 11 Fedora Update System 2014-12-06 10:40:42 UTC
postgresql-9.3.5-5.fc21 has been pushed to the Fedora 21 stable repository.  If problems still persist, please make note of it in this bug report.

Note You need to log in before you can comment on or make changes to this bug.