Created attachment 180457 [details] Patch to update default version of PostgreSQL to 9.5 Hello, i want to update the default version of PostgreSQL to 9.5 as discussed in 201796. Therefore attached a patch. Aim of the exp-run is to find any fallout. Currently no errors are known be me (using a default of 9.6 for many of my own servers). Greetings, Torsten
Before the default version can be changed, a *working* procedure must be written. Right for now people who use postgresql, if they run pkg upgrade and don't notice that postgresql changes from 9.3 to 9.5 will end up with a postgresql server that is not working.
You're right. But sadly there is no upgrade procedure nor was there ever. All bumps before resulting in broken server. This won't change since after PostgreSQL 9.6. This is the first version where girgen has managed to separate the storage-dirs of postgresql, so an update or just a reinit are possible. From this viewpoint we should bump directly to PostgreSQL 9.6, since it makes some update-ways available. But it will break other things, because the name of the postgres user changed from "pgsql" to "postgres". So either option will break things on users side without any chance to avoid this. :(
All previous bumps were done at a time where no binary packages existed, and changing the default version only changed it for new installations, existing one kept the version they had installed. Now, with packages, people run pkg upgrade, and they will kill you if you break their setup. I know there was no upgrade path before, but for the previous bumps, it was not important. One must now be written, and thoroughly tested *before* the version can be bumped.
> Now, with packages, people run pkg upgrade, and they will kill you if you > break their setup. > > I know there was no upgrade path before, but for the previous bumps, it was not > important. > > One must now be written, and thoroughly tested *before* the version can be > bumped. While i really support your point, i have to disagree: we break things on a regular basis. Have a look at UPDATING. Its - very sadly - normal to break things and just put a short notice in this file. Within the last 1 or 2 months the xorg-update killed my laptop and the qt update my GitLab. And i can count various more. The current status of the portstree just do not allow any automatic way of not breaking PostgreSQL with an update. I really hope someone will jump in and correct me, but as far as i know the work of girgin on PostgreSQL 9.6 just makes the ground for an improvement to such update-path. So, the only available update path currently is: pg_dumpall -> dumps the complete database shutdown the database remove /usr/local/pgsql/data (really) update to new version initdb new cluster restore the dump This works and is quite frustrating, especially if your database has a size of multiple TB. :/ We can put such a description in UPDATING, but things will break (like previous).
databases/postgresql-plproxy fails to build with PostgreSQL 9.5 : http://package22.nyi.freebsd.org/data/110amd64-default-PR217515/2017-03-04_10h20m28s/logs/errors/postgresql-plproxy-2.5_1.log Also, I noticed that databases/pglesslog is always ignored (depends on no longer existing PostgreSQL 9.1
I have not used dump/restore since pre 8.4 days, pg_upgrade works just fine, but you have to have the old and new servers installed at the same time. Of course it does not work with the way we currently do things, but this is mostly because people are quite happy with breaking everyone's setups each time the default changes. And because nobody on pgsql@ wants to take a day to figure out how to do it correctly.
(In reply to Mathieu Arnold from comment #6) > I have not used dump/restore since pre 8.4 days, pg_upgrade works just fine, > but you have to have the old and new servers installed at the same time. > > Of course it does not work with the way we currently do things, but this is > mostly because people are quite happy with breaking everyone's setups each > time the default changes. And because nobody on pgsql@ wants to take a day > to figure out how to do it correctly. OK. What follows is off the top of my pointy little head. But... I think it's going to be POLA no matter which/any direction is chosen. But the first couple of things that come to mind are chroot(8), of even creating a port that facilitates the entire process. Those with any sizable DB (should) understand the maintenance costs, already. But seems to me the simplest solution; in the short term, anyway; is chrooting the proposed new version, and finally moving it into it's final destination. While I's early. I haven't finished my first cup of coffee, and haven't completely thought this through; am I completely off the map? --Chris
(In reply to Torsten Zuehlsdorff from comment #4) I cannot agree. Yes, there are many breakages but it is not justification to add another - mainly in DB world. You simply cannot do silent upgrade from 9.4 to 9.6 and left users with non-working DB. It is way different then breaking xorg on personal computer. There were previously just "php" as moving default version which caused many problems after upgrade too. So now we have php56, php70, php71... and you cannot accidentally upgrade from 5.6 to 7.0.
It seems you all reading me wrong! I'm not in favor of breaking thinks. I'm annoyed but that regularly, therefore the mention of other breakage. Heck, i have an 11 TB PostgreSQL cluster. That is nothing you just dump and restore... My point is: there is currently no (automatic) update path for PostgreSQL. There could be probably one when we are going to PostgreSQL 9.6, but this will break things also. Not going to 9.6 will break other things. So, yes: breaking (especially a database) is a big no-go. But nobody mentioned even a possibility currently supported! Nobody raised, that the configuration between PostgreSQL versions are often incompatible. Let alone multiple changes in behavior causing problems. Nobody mentioned that it is a really bad idea to update things automatically too. The update of the default version is a loss in any way. It will break things no matter what you do. (I still have hope somebody proves me wrong. Please!) We just can argue with worse situation is not as worse as the other one. Best way would be to allow installation of two different versions, which would enable pg_migrate usage. But this need PostgreSQL 9.6, which will break things through the renamed username.
(In reply to Antoine Brodin from comment #5) > databases/postgresql-plproxy fails to build with PostgreSQL 9.5 Thanks for the feedback. The project last update is from 2014. I doubt it will support newer versions, but i already tried to contact the developer. Maybe we get some feedback. > Also, I noticed that databases/pglesslog is always ignored Last upstream change was 2010. I think we should drop this one from the portstree.
(In reply to Torsten Zuehlsdorff from comment #9) I am sorry if I get things wrong... As with renamed users - I think we can create two users with the same UID so both PostgreSQL versions can coexist and then remove old users after old version will be deinstalled. But as you already noted - first we must have working procedure to install two postgres versions at the same time. (this is pain for other ports two because some customers want to run two versions of PHP or MySQL etc. - it is possible with some jails juggling but ... you know)
(In reply to Miroslav Lachman from comment #8) > (In reply to Torsten Zuehlsdorff from comment #4) > > I cannot agree. Yes, there are many breakages but it is not justification to > add another - mainly in DB world. ... > cannot accidentally upgrade from 5.6 to 7.0. I'm not suggesting anything be *broken*. Quite the contrary; I'm suggesting/proposing a possible path to a *solution*. :-) --Chris
I'm just finishing up a fresh install (CURRENT) in new hardware. When I'm done. I'm going to venture a shot using a version of my proposed idea, as a possible upgrade path. It will be more intrusive than a simple upgrade -- not a problem, that's what @comment is for; a big banner with instructions || a pointer to a location with instructions. Anyway, unless someone else comes up with a better plan before I finish. I'll post something here, when there's some thing worth showing. :-) @Torsten I'm more inclined to use chroot(8) than a jail(8), it's less intrusive, simpler, and IMHO better for the task at hand. :-) In the end, I'm only suggesting this as a short-term solution. As pg6 appears to have a lot more flexibility in this regard. Thereby making most, if not all of this moot. :-) All the best. --Chris
(In reply to Mathieu Arnold from comment #6) > I have not used dump/restore since pre 8.4 days, pg_upgrade works just fine, but > you have to have the old and new servers installed at the same time. > Of course it does not work with the way we currently do things, but this is > mostly because people are quite happy with breaking everyone's setups each time > the default changes. Just one important point: it is *not* a good idea to do pg_upgrade automatically. I believe with the changes done in PostgreSQL 9.6 by girgen it should be possible to use pg_upgrade much easier. But as stated previous: automatic update is a bad idea. Behavior changes, functions are removed and so on. And since you bring the point up: we really need to do something against the breakings. Even as a port committer i'm not able to cope with the last breaks anymore. I just have two options left: pkg lock and hope best for the rest or do not upgrade at all. That is very very bad... @Chris: I wont stop you - but i did not understand your solution proposal either ;) Can you please make it more clear? Or do you want to just create a prototype for testing and show it to us?
Created attachment 180642 [details] simple script to build a temporay postgresql server in prepare for a pg_upgrade Hi, Great discussion! As stated several times in this thread, automatic upgrade is not a good idea. > Best way would be to allow installation of two different versions, which would enable pg_migrate usage. But this need PostgreSQL 9.6, which will break things through the renamed username. The change of username was made after many requests. I would have preferred to have the same UID for pgsql and postgres, but this was not supported by the ports infrastructure and I did not feel inclined to add support for it. In hindsight, perhaps that was a bad decision. It is what it is now, I guess. As you probably understand, the reason for not updating the default version of PostgreSQL in the ports tree for such a painfully long time, is just this problem of how to help our users update their databases and keep them out of trouble. My aim is to break out libpq as a separate package and let all client side ports depend on that, and let libpq always install the latest version. libpq has been stable since 84 IIRC. I have discussed this with the pgsql packagers and they seem to agree that this is a good path. It is similar to what debian does, although they do a lot more and have their own tools, some of which I don't particularly fancy. Anyway, this would allow parallel server packages installed, and it would be a great feature. Until then, a `pkg upgrade` would probably lead to conflicts if postgresql93-server is "manually" installed (as opposed to by dependency) when some client port using postgresql-client wants to update and depends on postgresql95-client. Not sure exactly what would happen? How do we best approach this? Speed up the creation of the separate port for libpq? Other suggestions? As for support for pg_upgrade with the current infrastructure, I used a simple script to build an extra server in /var/tmp to use during the upgrade. I helped som ppl who asked for support directly to pgsql@ with that script [enclosed here, FWIW]. Maybe it is a too error prone process, but I do not really see the need for chroot:ing, perhaps you could elaborate on your thoughts about alternative routes? > And because nobody on pgsql@ wants to take a day to figure out how to do it correctly. This is only partly true. It is not a simple problem, I have spent time pondering how to do this, and my take is the above, "liberating" libpq. It will take more tahn a day, though... :( Palle
A commit references this bug: Author: tz Date: Tue Mar 14 13:46:15 UTC 2017 New revision: 436153 URL: https://svnweb.freebsd.org/changeset/ports/436153 Log: databases/postgresql-plproxy: Update from 2.5 to 2.7 Changelog: https://plproxy.github.io/changelog.html This adds support for PostgreSQL 9.5 Also: - take maintainership - change MASTERSITE to GitHub - remove obsoleted check for PostgreSQL 9.1 (no longer in ports) - update homepage of project PR: 201796, 217515 Changes: head/databases/postgresql-plproxy/Makefile head/databases/postgresql-plproxy/distinfo head/databases/postgresql-plproxy/pkg-descr head/databases/postgresql-plproxy/pkg-plist
databases/pglesslock is now set for expiration databases/postgresql-plpgproxy is upgraded to 2.7 and now supports PostgreSQL 9.5. It even worked with my local PostgreSQL 9.6. So is there some consent about what is needed to raise the default version? What are the ToDo lists? Are what should be discussed further?
I would really like to see a working and tested procedure before the default is changed. Even if the instructions include things like: After upgrading, you will need some manual intervention: # pkg fetch postgresql93-server postgresql93-contrib # tar xf /var/cache/pkg/postgresql93-server-9.3.16.txz -C /tmp/pg-upgrade # tar xf /var/cache/pkg/postgresql93-contrib-9.3.16.txz -C /tmp/pg-upgrade # mv /usr/local/pgsql/data /usr/local/pgsql/data93 # /usr/local/etc/rc.d/postgresql initdb # pg_upgrade -b /tmp/pg-upgrade/... -d /usr/local/pgsql/data93 -B .. -D .. or something similar, that has been tested.
(In reply to Mathieu Arnold from comment #18) > I would really like to see a working and tested procedure before > the default is changed. Are you insisting on pg_upgrade? Because pg_dumpall does work and is tested many times. And its much easier (but also slower) for persons not so familiar with the database itself. I can write such a routine easily.
I don't really care what procedure is used, it is not for myself. pg_upgrade has always worked for me ever since it was introduced, and is orders of magnitude faster than pg_dumpall, especially on large databases.
(In reply to Torsten Zuehlsdorff from comment #19) pg_dumpall is very slow if used on DB of 10+GB It would be nice to have documented both ways of upgrade (pg_dumpall + pg_upgrade) ... but I have no time to dig in to this issue and guess others are short on time too. We are now on PG 9.4 and planing upgrade in the end of this summer.
I will cover both ways, so that the user can decide with which to go. Hopefully i will find time next week for this.
(In reply to Torsten Zuehlsdorff from comment #22) > I will cover both ways, so that the user can decide with which to go. > Hopefully i will find time next week for this. I'm sorry. My laptop broke and so i had no change yet. But i'm still on it.
Okay, it took a little longer than expected. But here we go. After some testing i decided its easier to stay with pg_upgrade. Providing 2 different procedures will cause some confusing when to use which, even if stated. Based at the notes of mat@ i came up with this: # service postgresql stop # pkg fetch postgresql93-server postgresql93-contrib # mkdir /tmp/pg-upgrade # tar xf /var/cache/pkg/postgresql93-server-9.3.17.txz -C /tmp/pg-upgrade # tar xf /var/cache/pkg/postgresql93-contrib-9.3.17.txz -C /tmp/pg-upgrade # mv /usr/local/pgsql/data /usr/local/pgsql/data93 # service postgresql initdb # su -l pgsql -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /usr/local/pgsql/data93/ -B /usr/local/bin/ -D /usr/local/pgsql/data/" I tested it on 2 different machines and it worked well. But i would like somebody else to test it, just to be sure.
So, whats next? We have an exp-run, which brought up issues and all of them should be fixed right now. Also we have an update strategy. A new exp-run to be sure? Or close this PR and proceed in 201796? I'm unsure about how the workflow should be.
Where are the results from the exp-run?
tl;dr Close this issue when the "QA/exp-run/approval by portmgr" 'task' is satisfied. To be less confusing, the attachment here should be deleted so there's no confusion as to which patch needs to be exp-run and which one is the 'latest'. The current state is that this issue is a sub(task) issue of bug 201796, and that QA (this issue) blocks the change/commit (the other issue) and given the relationship is set that way. From a workflow perspective, given it has been setup this way already, this issue should be closed once QA is satisfied/confirmed, which essentially becomes the "approved by: portmgr" for the other bug. All else being equal, the cleanest, least confusing way is a single bug with all the conversation, all the qa, process and all the approval in one place, *UNLESS* there are multiple steps/tasks to achieve a single 'goal' and each task sits with a different assignee/maintainer. Example where multiple issues with one parent "goal" task is: one security vulnerability affecting mysql/maridb/percona/whatever, each with a different maintainer and a vuxml entry and quarterly merge done by someone else (say ports-secteam). The parent is assigned to ports-secteam (as coordinator/responsible), with subtasks per port affected, and the subtasks are assigned to and actioned by the respective maintainers.
(In reply to Palle Girgensohn from comment #26) > Where are the results from the exp-run? antonie posted this link: http://package22.nyi.freebsd.org/data/110amd64-default-PR217515/2017-03-04_10h20m28s/logs/errors/ There is no more recent run currently.
Great! How do we go about so we don't all fix the same problems? Shall we just write something here, or should we have a separate PR per port?
(In reply to Palle Girgensohn from comment #29) > How do we go about so we don't all fix the same problems? Shall we just > write something here, or should we have a separate PR per port? As i noticed within this PR i already fixed every finding ;) As from my viewpoint there is nothing to do left. Maybe another exp-run, because i like it to be safe. Also it would be nice if somebody other also will check my upgrade procedure from comment 24. Because better safe than sorry.
Thanks koobs for the explanation. So, to end this PR: can portmgr please approve the Patch to update default version of PostgreSQL to 9.5?
portmgr does not have to approve this. bsd.default-versions.mk is maintained by ports@. What portmgr requires is that changes are tested and breakage fixed, once that is done, there is no need for approval.
Thanks mat for the explanation :) Since every breakage was already fixed, i will close the PR and go ahead. Thanks all!
A commit references this bug: Author: tz Date: Thu Jun 22 14:16:03 UTC 2017 New revision: 444116 URL: https://svnweb.freebsd.org/changeset/ports/444116 Log: Change PostgreSQL default version from 9.3 to 9.5 PR: 201796, 217515 Reviewed by: mat, girgen, xmj, Jov <amutu@amutu.com> and more Changes: head/Mk/bsd.default-versions.mk head/UPDATING