Thread: Re: [HACKERS] postgres 9.6.2 update breakage
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
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 timesthe amount of caution of upgrades, a vat of coffee to work through the problems, and a barrel of whiskey for whenit 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...
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
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