Re: postgres 9.6.2 update breakage

[Roel Janssen]

Christopher Allan Webber writes:

> Roel Janssen writes:
>
>> Jan Nieuwenhuizen writes:
>>
>>> Hi!
>>>
>>> I reconfigured my system and pulled in the postgres 9.6.2 update.  Now
>>> postgres does not start, /var/log/messages has
>>>
>>>     May 12 13:02:52 localhost postgres[451]: [1-1] FATAL:  database files 
>>> are incompatible with server
>>>     May 12 13:02:52 localhost postgres[451]: [1-2] DETAIL:  The data 
>>> directory was initialized by PostgreSQL version 9.5, which is not 
>>> compatible with this version 9.6.2.
>>>
>>>
>>> I have reverted the postgres update and everything is "fine" again.s
>>
>> I think database upgrades can be performed with 'pg_upgrade', which
>> is included in the postgresql package.  The command's '--help' switch
>> even includes an example.
>>
>> In my experience (9.2 > 9.3, 9.3 > 9.4, 9.4 > 9.5), the upgrades went
>> just fine.  You have to stop the postgresql daemon, perform the
>> upgrade, and start it again.
>
> Could you provide the steps you used to upgrade using pg_upgrade?

So, it would be something like:
postgres pg_upgrade \
  -b /var/guix/profiles/per-user/<user>/guix-profile-<N>-link/bin \
  -B ~/.guix-profile/bin \
  -d /dbs/testdb \
  -D /dbs/testdb-upgraded \
  --retain \
  --jobs=4

> It seems it needs to reference the old version of posgres, so we'll need
> to keep the old version around every time we bump the postgres version.
> We aren't doing that currently.  If I just do:
>
>   address@hidden:/tmp$ sudo -u postgres pg_upgrade
>   
>   You must identify the directory where the old cluster binaries reside.
>   Please use the -b command-line option or the PGBINOLD environment variable.
>   Failure, exiting

But you have the old binaries in the previous generation of your profile..

> Well, ok, so maybe we could keep the previous verison of postgres
> around.  But even then, this seems like it is going to be annoying for
> users because you'll need to install both versions of postres and figure
> out what the paths are to point pg_upgrade at.  Am I wrong?

Keep the previous version of postgres around as a package recipe?  I
don't think that's needed.  If you had the previous version installed,
and set up your database with it, you have it in an older generation of
your profile.  And if you don't have the previous version in your
profile, there isn't a way to set up the database with the old format.

> Hence, I think having a wrapper script that does this for users (maybe
> even through some dumb gexp) would be nice..

A wrapper script doesn't hurt, though. :-)

Kind regards,
Roel Janssen