Proposal to fix postgresql package maintainance nightmare

[Palle Girgensohn]

> 4 aug 2015 kl. 12:11 skrev Baptiste Daroussin <bapt at FreeBSD.org>:
> 
>> On Tue, Aug 04, 2015 at 10:54:03AM +0300, Palle Girgensohn wrote:
>> 
>> 
>> 
>> 
>>>> 21 jul 2015 kl. 15:03 skrev Baptiste Daroussin <bapt at FreeBSD.org>:
>>>> 
>>>>> On Tue, Jul 21, 2015 at 09:22:31PM +0930, Shane Ambler wrote:
>>>>> On 21/07/2015 19:16, Baptiste Daroussin wrote:
>>>>> Hi,
>>>>> 
>>>>> We do manage a bunch of postgresql servers on FreeBSD, and I really find the
>>>>> current model of packages postgresql is a nightmare on FreeBSD.
>>>>> 
>>>>> Let's first start with the current issues.
>>>>> - Impossible to have tools from both old and new version at the same time (which
>>>>>  is necessary to upgrade db and prepare upgrades of db)
>>>>> - Impossible to chose the version we want to run in production without having to
>>>>>  rebuild the packages and the whole ports tree with a specific default.
>>>>> - Nightmare each time a new default version is set in the ports tree.
>>>> 
>>>> Sounds like a good plan, I am not a heavy postgresql user but I have
>>>> set the default pg version in make.conf to prevent unexpected new
>>>> versions going in during port updates when I didn't think of doing
>>>> upgrade steps.
>>>> 
>>>>> Here is my proposal to fix that.
>>>>> 
>>>>> Having one single postgresql-client package always on the latest stable version
>>>>> (backward compability being very good) providing the client cli tools and the
>>>>> libraries (those libraries will be used for everything in the ports tree
>>>>> needing to talk to postgresql)
>>>>> 
>>>>> Have one full postgresql package per supported version upstream self installing
>>>>> itself into let's say: /usr/local/postgresql94 and symlinks all the client tools
>>>>> to /usr/local/bin suffixed by the version psql94 pg_bla94 etc.
>>>> 
>>>> Don't want to start a debate but thought I would mention as food for 
>>>> thought --
>>>> 
>>>> I'm not sure of any strong need to have more than one pg client version
>>>> available. The newer client can connect to any older server and I don't
>>>> know of any issues when an old client connects to a newer server.
>>> 
>>> That is why I propose only one client for regular users
>>>> 
>>>>> That way everything talk to pgsql will only depend on one postgresql-client
>>>>> packages that will smoothly be upgraded to newer versions.
>>>>> 
>>>>> All database administrators will have the ability to chose the production
>>>>> version they do want without having to worry about a default version.
>>>>> 
>>>>> They can install multiple version in parallel and deal with upgrade the way they
>>>>> want having access to both versions of the tools of the same time.
>>>>> 
>>>>> Any opinion on that change? Any idea one how to make the upgrade path as
>>>>> transparent as possible for current setup? (beside of course adding an UPDATING
>>>>> entry)
>>>> 
>>>> I think allowing multiple pg server versions is a good idea, this can
>>>> prevent old binary versions being removed before the data update process.
>>>> 
>>>> A new upgrade command could be added so we can do `service postgresql
>>>> upgrade` which tests for existing paths, defines old and new dirs and
>>>> runs pg_upgrade.
>>>> 
>>>> The rc script could either add a version postfix to the data dir path
>>>> or test PG_VERSION content to decide if data gets moved to data-old so
>>>> new versions being started won't see older version data. Lack of up to
>>>> date data dirs can lead to "You need to perform an upgrade first."
>>>> Different disk usage (filecount?) for old and new data dir can lead to
>>>> "Have you upgraded your old data?"
>>>> 
>>>> I don't think an upgrade step could be added during a port build, it
>>>> would have to be at server start in the rc script. I wouldn't add an
>>>> automatic upgrade step unless it was enabled by the user.
>>> 
>>> 100% agree, at first I would not even propose an automatic upgrade mechanism I
>>> find it too dangerous by design I would expect admins to do upgrade themselves
>>> preparing it etc.
>>> 
>>> By upgrade patch I was more thinking when a user will make pkg upgrade and get
>>> the new scheme I want everything to be safe and smooth (transparent) from what I
>>> already tested this is the case now, but hey maybe someone has figured out
>>> something that could be wrong.
>>> 
>>> Best regards,
>>> Bapt
>> 
>> Hi,
>> 
>> Sorry for not joining the conversation earlier. Did anything more happen here?
> 
> Don't worry I would not have pushed any big change like this without you
> reviewing and validating first :)
>> 
>> I did some test work a few years ago to make it possible to have multiple versions installed in parallel. My approach then was that of lib/pgsqlNN and symlinks for the default version, similar to how macports do it. 
>> 
>> Reading the discussion in this thread, one of the main goals would be to ease dependency management for ports depending on PostgreSQL. My previous approach would not really remedy that problem. 
>> 
>> Suggesting just one client install is not perfect either, since psql's internal commands, \[a-zA-Z]+, are somewhat linked to the version on the server. Though these commands rarely changes, it happens.
> 
> Yup that is what I figured out.
>> 
>> What is extremely stable, though, is libpq.so.5. And isn't that what most ports depend upon?
>> 
>> So the best would perhaps be to separate postgresql-libpq that always uses the latest version (?) and have postgresqlNN-(client|server|contrib) like now, except that the client of course is stripped from libpq?
> 
> Yes that would do the trick.
> 
> I have been busy in other area, but that is still in my target. Fine with me if
> you want to take over that job, I'll be happy to review/test it. Otherwise I may
> send you a patch when I have something working.
> 
> Best regards,
> Bapt


I won't be doing much work in August, but after that I may look into this again. If you have something working before September, you're welcome to send a patch. If later, better check before you start working so we don't do double work. :-)