Bug 1007802 - pg_upgrade can fail when environment changed with error "lc_collate cluster values do not match..."
pg_upgrade can fail when environment changed with error "lc_collate cluster v...
Status: CLOSED ERRATA
Product: Fedora
Classification: Fedora
Component: postgresql (Show other bugs)
21
Unspecified Unspecified
low Severity low
: ---
: ---
Assigned To: Pavel Raiskup
Fedora Extras Quality Assurance
:
Depends On: 1052063
Blocks:
  Show dependency treegraph
 
Reported: 2013-09-13 06:56 EDT by Honza Horak
Modified: 2015-10-17 09:20 EDT (History)
10 users (show)

See Also:
Fixed In Version: postgresql-9.3.5-5.fc21
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-10-29 07:02:12 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)


External Trackers
Tracker ID Priority Status Summary Last Updated
Red Hat Bugzilla 958045 None None None Never

  None (edit)
Description Honza Horak 2013-09-13 06:56:56 EDT
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):
postgresql-server-9.3.0-1.fc20.x86_64

How reproducible:
every-time

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 08:19:58 EDT
Not sure but it can be related: bug #1007802
Comment 2 Honza Horak 2013-09-13 08:21:50 EDT
(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 04:48:12 EST
I tried to propose possible solution upstream:
http://www.postgresql.org/message-id/4628051.BObrLhnj7b@nb.usersys.redhat.com
Comment 4 Pavel Raiskup 2014-10-14 08:52:53 EDT
*** Bug 1152556 has been marked as a duplicate of this bug. ***
Comment 5 Pavel Raiskup 2014-10-14 09:05:51 EDT
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 09:56:15 EDT
thanks Pavel, your workaround fixes my problem.
Comment 7 Fedora Update System 2014-10-20 07:15:50 EDT
postgresql-9.3.5-2.fc20 has been submitted as an update for Fedora 20.
https://admin.fedoraproject.org/updates/postgresql-9.3.5-2.fc20
Comment 8 Fedora Update System 2014-10-20 07:16:31 EDT
postgresql-9.3.5-5.fc21 has been submitted as an update for Fedora 21.
https://admin.fedoraproject.org/updates/postgresql-9.3.5-5.fc21
Comment 9 Fedora Update System 2014-10-20 11:31:11 EDT
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:
https://admin.fedoraproject.org/updates/FEDORA-2014-13321/postgresql-9.3.5-5.fc21
then log in and leave karma (feedback).
Comment 10 Fedora Update System 2014-10-29 07:02:12 EDT
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 05:40:42 EST
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.