Re: Upgrade from 9.3 to 9.4 issue

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.