Upgrade from 9.3 to 9.4 issue

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general