Bug 201796 - Mk/bsd.default-versions.mk: Set PostgreSQL 9.5 as default
Summary: Mk/bsd.default-versions.mk: Set PostgreSQL 9.5 as default
Status: Closed FIXED
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Ports Framework (show other bugs)
Version: Latest
Hardware: Any Any
: --- Affects Only Me
Assignee: Torsten Zuehlsdorff
URL:
Keywords: easy, needs-qa, patch, patch-ready
Depends on: 204719 217515
Blocks:
  Show dependency treegraph
 
Reported: 2015-07-23 16:28 UTC by Johannes Jost Meixner
Modified: 2017-06-22 14:18 UTC (History)
11 users (show)

See Also:
koobs: maintainer-feedback-
koobs: exp-run+


Attachments
Default Mk/bsd.default-versions.mk to postgresql94-* (389 bytes, patch)
2015-07-23 16:28 UTC, Johannes Jost Meixner
no flags Details | Diff
Patch to update default version of PostgreSQL to 9.5 (448 bytes, patch)
2017-03-03 09:57 UTC, Torsten Zuehlsdorff
no flags Details | Diff
Patch to update default version of PostgreSQL to 9.5 with UPDATING (1.98 KB, patch)
2017-06-09 07:54 UTC, Torsten Zuehlsdorff
no flags Details | Diff
Patch to update default version of PostgreSQL to 9.5 with UPDATING (2.05 KB, patch)
2017-06-12 12:55 UTC, Torsten Zuehlsdorff
no flags Details | Diff
Patch to update default version of PostgreSQL to 9.5 with UPDATING (2.20 KB, patch)
2017-06-20 14:19 UTC, Torsten Zuehlsdorff
no flags Details | Diff
Patch to update default version of PostgreSQL to 9.5 with UPDATING (2.55 KB, patch)
2017-06-21 13:08 UTC, Torsten Zuehlsdorff
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Johannes Jost Meixner freebsd_committer freebsd_triage 2015-07-23 16:28:20 UTC
Created attachment 159116 [details]
Default Mk/bsd.default-versions.mk to postgresql94-*

Postgresql 9.4 has been released on 2014-12-18. By now, it's at 9.4.4 (released 2015-06-12) and comes with many interesting features (see release notes [1]).

Patch attached sets postgresql 9.4 as default version.

[1] http://www.postgresql.org/docs/9.4/static/release.html
Comment 1 Johannes Jost Meixner freebsd_committer freebsd_triage 2015-07-24 09:53:11 UTC
assign to pgsql@
Comment 2 Michael Moll freebsd_committer freebsd_triage 2015-11-18 00:01:40 UTC
what's the status here?
Comment 3 Kubilay Kocak freebsd_committer freebsd_triage 2015-11-18 03:00:48 UTC
This is now maintainer-timeout, reset Assignee.

In terms of moving forward (without pgsql@) this might be worth an exp-run.
Comment 4 Antoine Brodin freebsd_committer freebsd_triage 2015-11-18 06:30:04 UTC
A question before doing an exp-run,  who does intend to work on this and fix the fallout, commit it, etc.?

If no ones plans to work on it, let's close this PR.
Comment 5 Palle Girgensohn freebsd_committer freebsd_triage 2015-11-18 08:31:38 UTC
Hi,

I am willing to work on this.

We had a discussion during the summer about trying to break out libpq and only use the latest released version of that, so we would not be so dependent on the correcct postgresqlNN-client for every port. It might be out of context for this requirement though, but it would be a good thing if it possible. I'll have a discussion with pother pgsql-packagers about this idea. 

So anyway, bare with us for a while, and I'll get back to this shortly.

Palle
Comment 6 Antoine Brodin freebsd_committer freebsd_triage 2015-11-18 17:42:53 UTC
Exp-run results on 10.1 i386 with default options:

http://package22.nyi.freebsd.org/jail.html?mastername=101i386-default-PR201796

0 new failure

The only "strange" thing is math/saga,  which has hardcoded lib depends on version 9.3 instead of USES=pgsql
Comment 7 Palle Girgensohn freebsd_committer freebsd_triage 2015-11-19 22:08:48 UTC
I checked wiuth math/saga maintainer, and that port can be modified to use USES=pgsql instead.

Palle
Comment 8 Kubilay Kocak freebsd_committer freebsd_triage 2015-11-19 22:12:00 UTC
I'd recommend creating separate issues that block completion of this one to track progress
Comment 9 Palle Girgensohn freebsd_committer freebsd_triage 2015-11-22 11:39:08 UTC
math/saga has been modified to use USES=pgsql per PR ports/204719
Comment 10 Palle Girgensohn freebsd_committer freebsd_triage 2015-11-22 11:42:33 UTC
Another thing that springs to mind is if a PORTVERSION bump is needed for all ports using USES=pgsql? What is the preferred pattern here?
Comment 11 Mathieu Arnold freebsd_committer freebsd_triage 2015-11-23 06:57:16 UTC
(In reply to Palle Girgensohn from comment #10)
> Another thing that springs to mind is if a PORTVERSION bump is needed for
> all ports using USES=pgsql? What is the preferred pattern here?

No, because if you are using ports, and already have postgresql installed, you will not be forced to change version.

For people using binary packages, there will be a conflict, and they will need to dump/reload, or run pg_upgrade, it would be great if there was a pkg-upgrade script for the postgresql*-server ports...
Comment 12 Torsten Zühlsdorff 2015-11-23 09:39:41 UTC
(In reply to Mathieu Arnold from comment #11)

> For people using binary packages, there will be a conflict, and they will need 
> to dump/reload, or run pg_upgrade, it would be great if there was a pkg-upgrade 
> script for the postgresql*-server ports...

Year, this would be great, but what way to use? pg_dumpall, pg_dump, pg_upgrade? 

One of my databases has several TB - for it i prefer the manual way. Others are so small a pg_dump would be fine.

Also sometimes there are changes which are incompatible. An automatic upgrade is not a good option in such a case.

Sadly this issue is not this easy :(
Comment 13 Palle Girgensohn freebsd_committer freebsd_triage 2015-11-23 10:41:14 UTC
(In reply to Torsten Zühlsdorff from comment #12)

Vi had a discussion about this within the pgsql@ team, and one way might be to extract libpq to a separate port, och make USES=pgsql just pull in that dependency. That would always be the latest version, since libpq is very stable, it rarely changes at all. Then we could install the rest of postgresqlNN-client and postgresqlNN-server in it's own (lib|share|include)/postgresqlNN directories, and use some kind of meta-port to symlink to the preferred version.

It would mean we could install several postgresql versions in parallel. This is a complex task though, and it might not even be completely possible without too much intrusive changes to postgresql. I will discuss this with some other packagers first. So this should probably be regarded as a possible long term solution at best.

We do however need some way of helping users to get two different versions of postgresql binaries in just for the sake of facilitating an upgrade running pg_upgrade. The most simple way would probably be a separate port, but that woud be a bit weird.

Other ideas?
Comment 14 Mathieu Arnold freebsd_committer freebsd_triage 2015-11-23 11:58:58 UTC
(In reply to Palle Girgensohn from comment #13)
> (In reply to Torsten Zühlsdorff from comment #12)
> 
> Vi had a discussion about this within the pgsql@ team, and one way might be
> to extract libpq to a separate port, och make USES=pgsql just pull in that
> dependency. That would always be the latest version, since libpq is very
> stable, it rarely changes at all. Then we could install the rest of
> postgresqlNN-client and postgresqlNN-server in it's own
> (lib|share|include)/postgresqlNN directories, and use some kind of meta-port
> to symlink to the preferred version.
> 
> It would mean we could install several postgresql versions in parallel. This
> is a complex task though, and it might not even be completely possible
> without too much intrusive changes to postgresql. I will discuss this with
> some other packagers first. So this should probably be regarded as a
> possible long term solution at best.
> 
> We do however need some way of helping users to get two different versions
> of postgresql binaries in just for the sake of facilitating an upgrade
> running pg_upgrade. The most simple way would probably be a separate port,
> but that woud be a bit weird.
> 
> Other ideas?

Moving pg_upgrade from -contrib to -server would be great, people have been asking this for a long time.

Most package systems are able to install more than one postgresql at a time, though we don't really need that, what we need is to be able to upgrade, it means that there should be a package with older bin/postgres binaries (it's the only one needed for pg_upgrade, along with the .so it needs, of course.)

Maybe the postgresqlXX-server package could include them.
Comment 15 Torsten Zühlsdorff 2015-12-03 10:24:35 UTC
I don't now if it is really needed to extract libpq. My point is more like the statement from Mathieu. We need pg_upgrade working, which requires existence of old and new binaries of PG.
Of course we could try to allow installation of multiple postgresql-instances. This would be handy sometimes - and in such a case. Is there an argument against this way?
Comment 16 Chris Hutchinson 2015-12-04 02:11:56 UTC
(In reply to Torsten Zühlsdorff from comment #15)
> I don't now if it is really needed to extract libpq. My point is more like
> the statement from Mathieu. We need pg_upgrade working, which requires
> existence of old and new binaries of PG.
> Of course we could try to allow installation of multiple
> postgresql-instances. This would be handy sometimes - and in such a case. Is
> there an argument against this way?

I've been pondering all this, since it was brought up in the mailing
list. I had a look at what it would take to at least use pg_upgrade.
The best results seem to be when the previous version is still
installed (not just the previous DB). Anyway, it doesn't seem like
it would be terribly difficult to incorporate pg_upgrade into the
normal course of upgrading the port. The only potential pitfall I
see, is should the newer version being installed introduces things
not previously available. Which would require whoever maintains this
port to incorporate whatever required, as recommended into the
process to compensate/accommodate for the difference(s).

Does it make any sense to break pg_upgrade out, as it's own port?
Then REQUIRE, as needed?

--Chris
Comment 17 Palle Girgensohn freebsd_committer freebsd_triage 2015-12-04 20:57:57 UTC
(In reply to Chris Hutchinson from comment #16)

Hi Chris et al,

(adding pgsql@ to the cc list)

The problem is also that pg_upgrade requires *both* postgres binaries to be intalled.


The goal, as I see it, is more than just suppporting pg_upgrade. It is to allow multiple server versions installed side by side (need by some for test frameworks, for example), and also, more importantly, to simplify dependencies for packages depending on libpq.

Today, FreeBSD install client and server for the selected version only, and it conflicts with other versions. This means all packages depending on libpq are only built for the one default version of postgresql, and changing the default is a tedious Q&A process. And as users upgrade, everything that depends on libpq needs to be updated as well, and this can be a lot. And also, we have the problem with pg_upgrade which need both the old and the new postgres (server) binaries.


I'm hacking right now to test two similar approaches.

1. Allow multiple -server versions installed side-by-side, but only allow one -client (like now), and let client always default to the latest released version. [1]

2. Allow multiple -server and -client versions side-by-side, and have a separate postgresql-libpq package.


Most port that sets USES=pgsql just require libpq, it would always depend on the same port (postgresql-client in the first alternative, postgresql-lib in the second). Packages for client applications would be built and could be used with any version of the postgresql server.


The second approach is quite similar to what Debian does today. There are some scripts i Debian that are interesing (pg_lsclusters, pg_ctlcluster, pg_upgradecluster), but for now, I think they are out of scope, just to keep the effort at a reasonable level. Maybe later... This approach would require a meta port to symlink the client binaries to $PREFIX/bin.

The first approach is a bit less complex, which is nice. We could even do without a meta port, or at least a simpler one for chosing the preferred server version. We need to move some stuff from the client to the server port, like includes and man pages, since they really are not part of the client.

I'm leaning towards the first alternative at the moment.

Palle


[1] client stuff in postgresql is backwards compatible except perhaps for some very strange old things that is rarely used. We would keep the old -client ports around in case one of these odd cases comes up. Also, new versions would be installable this way. "latest released version" could be x.y.1, i.e. we could wait for the first patch release, why not?
Comment 18 Chris Hutchinson 2015-12-05 02:21:44 UTC
(In reply to Palle Girgensohn from comment #17)
> (In reply to Chris Hutchinson from comment #16)
> 
> Hi Chris et al,
> 
> (adding pgsql@ to the cc list)
> 
> The problem is also that pg_upgrade requires *both* postgres binaries to be
> intalled.
> 
> 
> The goal, as I see it, is more than just suppporting pg_upgrade. It is to
> allow multiple server versions installed side by side (need by some for test
> frameworks, for example), and also, more importantly, to simplify
> dependencies for packages depending on libpq.
> 
> Today, FreeBSD install client and server for the selected version only, and
> it conflicts with other versions. This means all packages depending on libpq
> are only built for the one default version of postgresql, and changing the
> default is a tedious Q&A process. And as users upgrade, everything that
> depends on libpq needs to be updated as well, and this can be a lot. And
> also, we have the problem with pg_upgrade which need both the old and the
> new postgres (server) binaries.
> 
> 
> I'm hacking right now to test two similar approaches.
> 
> 1. Allow multiple -server versions installed side-by-side, but only allow
> one -client (like now), and let client always default to the latest released
> version. [1]
> 
> 2. Allow multiple -server and -client versions side-by-side, and have a
> separate postgresql-libpq package.
> 
> 
> Most port that sets USES=pgsql just require libpq, it would always depend on
> the same port (postgresql-client in the first alternative, postgresql-lib in
> the second). Packages for client applications would be built and could be
> used with any version of the postgresql server.
> 
> 
> The second approach is quite similar to what Debian does today. There are
> some scripts i Debian that are interesing (pg_lsclusters, pg_ctlcluster,
> pg_upgradecluster), but for now, I think they are out of scope, just to keep
> the effort at a reasonable level. Maybe later... This approach would require
> a meta port to symlink the client binaries to $PREFIX/bin.
> 
> The first approach is a bit less complex, which is nice. We could even do
> without a meta port, or at least a simpler one for chosing the preferred
> server version. We need to move some stuff from the client to the server
> port, like includes and man pages, since they really are not part of the
> client.
> 
> I'm leaning towards the first alternative at the moment.
> 
> Palle
> 
> 
> [1] client stuff in postgresql is backwards compatible except perhaps for
> some very strange old things that is rarely used. We would keep the old
> -client ports around in case one of these odd cases comes up. Also, new
> versions would be installable this way. "latest released version" could be
> x.y.1, i.e. we could wait for the first patch release, why not?

Thanks for such an informative reply, Palle!
Approach #1 was the way I understood would be the initial
way, going forward, from the mailing list thread.
My response, above, was an effort to help initiate the process in
some way -- any way. :)
I don't feature my being able to manage the whole task. But liked
the whole concept, in the email thread, and was hoping to help.

Feel free to assign me a task, if you think I can help. :)

Thanks again, Palle.

--Chris
Comment 19 Johannes Jost Meixner freebsd_committer freebsd_triage 2016-03-25 17:26:44 UTC
Any news?
Comment 20 Johannes Jost Meixner freebsd_committer freebsd_triage 2016-09-21 10:27:51 UTC
Half a year later, another bump.
Comment 21 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2016-09-21 10:46:06 UTC
As i noticed Palle did a bunch of work on PostgreSQL - thank you very much! :)

Since PostgreSQL 9.6 is about to released next month i would raise the question: why not set 9.5 to new default?
Comment 22 Palle Girgensohn freebsd_committer freebsd_triage 2016-09-21 12:01:58 UTC
Hi,

I have a half finished project to allow installing multiple versions of postgresql-server i parallel and this is the reason that postgresql default version is still lagging -- I prioritized getting the parallel install done instead. But I agree this it getting ridiculous, so let's update to 9.5 as default while we're waiting for me to finish this work... :)

We need a new exp-run, how is that achived?
Comment 23 Mathieu Arnold freebsd_committer freebsd_triage 2016-09-21 12:17:42 UTC
Yes, you need an exp-run, to do that, it is easy, it is documented:

http://aragorn.in.absolight.net/d2/exp-run/en_US.ISO8859-1/articles/committers-guide/ports.html#ports-exp-run
Comment 24 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-03-03 09:56:46 UTC
So i take this ticket. Half a year ago we discussed to update the default version of PostgreSQL to 9.5. I now requested a exp-run in #217515.
Lets see if there is any fallout, so we can move on. :)
Comment 25 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-03-03 09:57:08 UTC
Created attachment 180458 [details]
Patch to update default version of PostgreSQL to 9.5
Comment 26 Palle Girgensohn freebsd_committer freebsd_triage 2017-03-03 11:01:27 UTC
(In reply to Torsten Zuehlsdorff from comment #24)
Thanks for kicking my butt! :) 9.5, excellent! looking forward to the exp-run!
Comment 27 commit-hook freebsd_committer freebsd_triage 2017-03-14 13:47:14 UTC
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
Comment 28 Johannes Jost Meixner freebsd_committer freebsd_triage 2017-04-11 08:02:13 UTC
(In reply to Palle Girgensohn from comment #26)
What else do you need help with to get the defaults updated?
Comment 29 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-08 14:49:30 UTC
After clearing #217515 and learning a lot:

The road is free for updating. If no objections are raised, i will create a new patch so i can commit it next monday.
Comment 30 Johannes Jost Meixner freebsd_committer freebsd_triage 2017-06-08 19:15:19 UTC
(In reply to Torsten Zuehlsdorff from comment #29)
Excellent let's get this in before someone wants to bump to 9.6 and require another few months of testing delays ;-)
Comment 31 Palle Girgensohn freebsd_committer freebsd_triage 2017-06-08 19:44:10 UTC
(In reply to Johannes Jost Meixner from comment #30)
+1

Excellent work, well done! Let's get it committed! :)
Comment 32 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-09 07:54:32 UTC
Created attachment 183354 [details]
Patch to update default version of PostgreSQL to 9.5 with UPDATING

I updated the patch and include instructions for UPDATING.

Since nobody else besides me ever tested it: PLEASE test it!

I will NOT commit a patch with so much breakage out there if nobody else tested it!
Comment 33 Mathieu Arnold freebsd_committer freebsd_triage 2017-06-09 11:37:36 UTC
The UPDATING looks very good.

I am not sure but I think people using other extensions like, say, postgis, or plperl, or plruby, or... will need to do the same as what you tell them to do with postgresql93-contrib.

Also, I think the pkg fetch dance is only really valid when using pkg upgrade.  People who are using portmaster/portupgrade will need to archive their current, local, postgresql packages, with something like pkg create postgresql93-server.
Comment 34 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-12 12:55:01 UTC
Created attachment 183428 [details]
Patch to update default version of PostgreSQL to 9.5 with UPDATING
Comment 35 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-12 13:01:26 UTC
Thanks, mat.

I had the same thoughts. Also the update is invalidated with the next PostgreSQL release, since it contains version numbers.

You're right regarding postgis or other services (while i intended to include -pl* etc with the '*' right behind database/postgresql - obviously not too clear).

I widened the AFFECTS, but now it has two lines - i'm not sure if this will cause problems?

We can replace the "pkg fetch dance" with a pure "pkg create dance", since it should always work. But even portmaster/portupgrade user should be able to use pkg fetch without having trouble with existing software. 

I'm going to rewrite it to "pkg create" which should be more clear. Also i'm going to add an hint regarding the version number in the package.
Comment 36 Mathieu Arnold freebsd_committer freebsd_triage 2017-06-12 13:30:28 UTC
AFFECTS is not formerly defined, so any tool parsing it will have to cope. I would keep it on one line though.
Comment 37 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-20 14:19:55 UTC
Created attachment 183654 [details]
Patch to update default version of PostgreSQL to 9.5 with UPDATING

As promised i updated the description and use pkg create now. Also added a notice about different version and a note to start the database again after the update.

Since really nobody tested it till now, i will hit my colleges for some testing. They are not using FreeBSD on a daily basis, but this will provide some helpful feedback about novices.

Of course it would be fine if somebody other also test it ;)
Comment 38 Jov 2017-06-20 15:31:55 UTC
(In reply to Torsten Zuehlsdorff from comment #37)
excellent work!

from my experience for update pg,maybe should mention the space will be doubled in upgrade process.or using pg_upgrade hardlink mode is better because of space and speed.
Also mention the replication slave upgrade using rsync --hard-link to speedup.

Note,pg_upgrade has been moved to the core server from controb,so the controb port is not needed when upgrade.
Comment 39 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-21 13:08:58 UTC
Created attachment 183673 [details]
Patch to update default version of PostgreSQL to 9.5 with UPDATING

> from my experience for update pg,maybe should mention the space will be doubled 
> in upgrade process.or using pg_upgrade hardlink mode is better because of space 
> and speed.
> Also mention the replication slave upgrade using rsync --hard-link to speedup.

Thanks for the extra warning. I added it to the UPDATING instruction!

> Note,pg_upgrade has been moved to the core server from controb,so the controb 
> port is not needed when upgrade.

While i did know this, i didn't know (nor did i test) how the usage of contrib-modules effects pg_upgrade. I was under the impression that using one of the contrib-modules results in needing the contrib modules when executing pg_upgrade?

Since there is no harm, i just let it in there.
Comment 40 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-21 13:10:11 UTC
Also: i assigned a trainee for testing, since no testers came up until know. If feedback is still positive i will commit it tomorrow.
Comment 41 Jov 2017-06-21 13:18:33 UTC
(In reply to Torsten Zuehlsdorff from comment #40)
+1
Get it committed!
Comment 42 commit-hook freebsd_committer freebsd_triage 2017-06-22 14:16:14 UTC
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
Comment 43 Torsten Zuehlsdorff freebsd_committer freebsd_triage 2017-06-22 14:18:10 UTC
Committed! :) Thanks to all! :)

I did some minor changes to the UPDATING entry like pkg delete -f existing postgresql, otherwise updates via portmaster/portupgrade will fail.
Also add an install of 9.5 because this isn't done automagically. 

Tests are great.