Thread: Upgrade from 9.3 to 9.4 issue

I have just upgraded from Fedora 21 to 22.
This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster
to fail because the existing databases are still at 9.3.
As suggested, I then ran postgresql-setup --upgrade but this failed with:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "line" user columns                    ok
Creating dump of global objects                             ok
Creating dump of database schemas
   books
   postgres
   pots
   stocks
   template1
                                                             ok

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

How can I recover from here?

Cheers and thanks,
Stephen Davies

Stephen Davies <sdavies@sdc.com.au> writes:
> I have just upgraded from Fedora 21 to 22.
> This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster
> to fail because the existing databases are still at 9.3.
> As suggested, I then ran postgresql-setup --upgrade but this failed with:
> lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_AU.UTF-8"

Hm, apparently you changed the system-wide language setting between F21
and F22?

One thing you should do is file a bug in Red Hat's bugzilla, pointing out
that postgresql-setup needs to endeavor to create the new cluster with
lc_collate and lc_ctype matching the old one.

Then, depending on what you want to do:

1. You really want to switch to en_AU, not just system-wide but for the
database: gonna have to dump and reload, I'm afraid.  pg_upgrade will
not handle this scenario.

2. You want to switch to en_AU system-wide but it's okay to leave the
database running in en_US: what I'd do personally is edit the
postgresql-setup shell script and add "export LANG=en_US.UTF-8" just
before the initdb call.  This won't stick across your next Fedora
upgrade, but hopefully by then Red Hat will have addressed your bug.

3. This was a mistake and you'd rather stay in en_US all round:
you can probably change the system-wide language setting somewhere,
but I do not remember where right at the moment.

            regards, tom lane

On 31/10/15 13:32, Tom Lane wrote:
> Stephen Davies <sdavies@sdc.com.au> writes:
>> I have just upgraded from Fedora 21 to 22.
>> This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster
>> to fail because the existing databases are still at 9.3.
>> As suggested, I then ran postgresql-setup --upgrade but this failed with:
>> lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_AU.UTF-8"
>
> Hm, apparently you changed the system-wide language setting between F21
> and F22?
>
> One thing you should do is file a bug in Red Hat's bugzilla, pointing out
> that postgresql-setup needs to endeavor to create the new cluster with
> lc_collate and lc_ctype matching the old one.
>
> Then, depending on what you want to do:
>
> 1. You really want to switch to en_AU, not just system-wide but for the
> database: gonna have to dump and reload, I'm afraid.  pg_upgrade will
> not handle this scenario.
>
> 2. You want to switch to en_AU system-wide but it's okay to leave the
> database running in en_US: what I'd do personally is edit the
> postgresql-setup shell script and add "export LANG=en_US.UTF-8" just
> before the initdb call.  This won't stick across your next Fedora
> upgrade, but hopefully by then Red Hat will have addressed your bug.
>
> 3. This was a mistake and you'd rather stay in en_US all round:
> you can probably change the system-wide language setting somewhere,
> but I do not remember where right at the moment.
>
>             regards, tom lane
>
I have resolved this issue.
I chose your option 2 (almost).
I couldn't get postgresql-setup to do what I wanted so I reverted to doing the
upgrade by hand.
Luckily, PostgreSQL 9.3 had not been removed from the box so I was able to
manually initdb with the US collation etc to create a new cluster where I
wanted it to be and then run a regular pg_upgrade.
This is actually easier than mucking about with postgesql-setup.

--
=============================================================================
Stephen Davies Consulting P/L                             Phone: 08-8177 1595
Adelaide, South Australia.                                Mobile:040 304 0583
Records & Collections Management.