postgres 9.6.2 update breakage

postgres 9.6.2 update breakage

[Jan Nieuwenhuizen]

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

How do we want to handle this?  I imagine that postgres has some way to
update its database...and I probably can figure out how to do that.  But
do our users need to know this?  And more importantly, if I upgrade,
will I be able to revert to a previous generation of my system?

Greetings,
janneke

-- 
Jan Nieuwenhuizen <janneke@gnu.org> | GNU LilyPond http://lilypond.org
Freelance IT http://JoyofSource.com | Avatar®  http://AvatarAcademy.nl  

Re: postgres 9.6.2 update breakage

[Roel Janssen]

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.

>
> How do we want to handle this?  I imagine that postgres has some way to
> update its database...and I probably can figure out how to do that.  But
> do our users need to know this?  And more importantly, if I upgrade,
> will I be able to revert to a previous generation of my system?

I think providing the latest PostgreSQL software is OK.. If you want to
stay on the previous version of PostgreSQL, stick to that package
(e.g. don't upgrade).

I don't think PostgreSQL upgrades are downgradeable.  But you can keep
the "old" data directory so that a downgrade will still work with the
data in your database at the time before the upgrade.

Hope this helps..

Kind regards,
Roel Janssen

Re: postgres 9.6.2 update breakage

[Christopher Allan Webber]

Hi there!

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
>
> How do we want to handle this?  I imagine that postgres has some way to
> update its database...and I probably can figure out how to do that.  But
> do our users need to know this?  And more importantly, if I upgrade,
> will I be able to revert to a previous generation of my system?

Yes, so therew as some conversation on this last year:

  https://lists.gnu.org/archive/html/guix-devel/2016-06/msg00917.html

Basically, we should have a package like "postgresql-upgrade".  It
should use:

  https://www.postgresql.org/docs/9.4/static/pgupgrade.html

... and do an upgrade between postgres versions.

Any volunteers want to work on this? :)

Re: postgres 9.6.2 update breakage

[Christopher Allan Webber]

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?

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:

  cwebber@oolong:/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

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?

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

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:
>
>   cwebber@oolong:/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

Re: postgres 9.6.2 update breakage

[Jan Nieuwenhuizen]

Roel Janssen writes:

> So, it would be something like:
> postgres pg_upgrade \
> ...

It's great to have a recipe `that works', so thanks!

However, whether or not we automate this, I cannot help to wonder if
we should support downgrading -- at least to the previous version
in this case?

If I'm not mistaken, everything else in GuixSD will run if I select a
previous system generation in Grub...except for this?

Is involving postgres developers an option, I'm sure a least one of
the postgresql hackers[cc] are already looking at Guix[SD]?

Greetings,
janneke

-- 
Jan Nieuwenhuizen <janneke@gnu.org> | GNU LilyPond http://lilypond.org
Freelance IT http://JoyofSource.com | Avatar®  http://AvatarAcademy.nl  

Re: postgres 9.6.2 update breakage

[Christopher Allan Webber]

Jan Nieuwenhuizen writes:

> Roel Janssen writes:
>
>> So, it would be something like:
>> postgres pg_upgrade \
>> ...
>
> It's great to have a recipe `that works', so thanks!
>
> However, whether or not we automate this, I cannot help to wonder if
> we should support downgrading -- at least to the previous version
> in this case?
>
> If I'm not mistaken, everything else in GuixSD will run if I select a
> previous system generation in Grub...except for this?
>
> Is involving postgres developers an option, I'm sure a least one of
> the postgresql hackers[cc] are already looking at Guix[SD]?
>
> Greetings,
> janneke

There's a big difference in upgrading and downgrading between guix
revisions and doing so in highly stateful databases, unfortunately.

I can't speak for postgres specifically, but here's my experience with
migrations as the tech lead of MediaGoblin:

 - upgrades should be taken with extreme caution, and you should back up
   first.
 - downgrades should be taken with ten times the amount of caution of
   upgrades, a vat of coffee to work through the problems, and a barrel
   of whiskey for when it doesn't.  I say that as someone who's mostly
   given up coffee and doesn't drink alcohol.

State changes are bad enough when unidirectional.  Django, for instance,
provides an API that does both upgrades and downgrades.  Almost
everybody spends a bunch of time carefully crafting their upgrades, and
just leaves their downgrades as the stubs that come with it.  These are
stubs that drop columns entirely, possibly columns that data was moved
to in the migration.  Reverse course, and suddenly you don't have a lot
of data you used to.

What we really want to do is provide the option to snapshot things
*before* you do an upgrade, IMO...

Re: postgres 9.6.2 update breakage

[Roel Janssen]

Jan Nieuwenhuizen writes:

> Roel Janssen writes:
>
>> So, it would be something like:
>> postgres pg_upgrade \
>> ...
>
> It's great to have a recipe `that works', so thanks!
>
> However, whether or not we automate this, I cannot help to wonder if
> we should support downgrading -- at least to the previous version
> in this case?
>
> If I'm not mistaken, everything else in GuixSD will run if I select a
> previous system generation in Grub...except for this?
>
> Is involving postgres developers an option, I'm sure a least one of
> the postgresql hackers[cc] are already looking at Guix[SD]?
>
> Greetings,
> janneke

Ah yes, I see the point.  The problem here is that when new features are
added to PostgreSQL, and you rely upon them in your database schemas,
downgrading will most likely cause loss of information.

Maybe we need a wrapper script that also makes a dump of all of the
data?  Now that could become a security hole.

Or the wrapper script warns about this situation, and recommends making
a (extra) back-up of the database before upgrading.

Or.. the upgrade is something a user should do explicitly, basically
giving up on the "just works" concept.  Guix already provides a nice way
to get the previous version of the exact binaries used before the
upgrade.

Kind regards,
Roel Janssen

Re: postgres 9.6.2 update breakage

[Peter Eisentraut]

On 5/15/17 02:48, Roel Janssen wrote:
> Ah yes, I see the point.  The problem here is that when new features are
> added to PostgreSQL, and you rely upon them in your database schemas,
> downgrading will most likely cause loss of information.
> 
> Maybe we need a wrapper script that also makes a dump of all of the
> data?  Now that could become a security hole.
> 
> Or the wrapper script warns about this situation, and recommends making
> a (extra) back-up of the database before upgrading.
> 
> Or.. the upgrade is something a user should do explicitly, basically
> giving up on the "just works" concept.  Guix already provides a nice way
> to get the previous version of the exact binaries used before the
> upgrade.

The best way to manage this with PostgreSQL is to make separate packages
for each PostgreSQL major version.  I see for example that you have
packages gcc-4.9, gcc-5, gcc-6, etc.  You should do the same with
PostgreSQL, e.g., postgresql-9.5, postgresql-9.6, postgresql-10.  Then
you don't have to concern yourselves with how "upgrades" and
"downgrades" should look for the users of your packaging system.  Minor
version upgrades are just installing the new package and restarting.
Major version upgrades are figured out by the user.

Downgrades between minor versions of the same major versions should
mostly work.  They are not well tested, if at all, but I don't think
that's all that different from downgrading any other package.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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