Proposal to fix postgresql package maintainance nightmare

Tue Jul 21 10:40:40 UTC 2015

Baptiste Daroussin wrote:
> Hi,
>
> We do manage a bunch of postgresql servers on FreeBSD, 

'We'..?  (I do and many other people do)

> and I really find the
> current model of packages postgresql is a nightmare on FreeBSD.
>   

It is not the ideal that's for sure.

> 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)
>   

My number one problem.

> - 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.
>   

With good reason.

> - Nightmare each time a new default version is set in the ports tree.
>   

Only if you don't have the default version set in your local config.

> 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)
>   

Later clients don't always work 100% on earlier servers due to core
schema changes (especially across major versions) - Note: 'client' not
'library'

> 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.
>   
Not a bad idea so far...  but why not make both server and client
libraries, runtime and headers do the same thing and the 'default
version' command line tools be symlinked into /usr/local/bin ... the rc
scripts would also have to be version suffix specific as well... that
way any dependent package can still link to the libraries it wants
without conflicting with the server/client version that portmgr@ set in
the latest round of Mk/* updates so that when patching an unrelated
security issue they won't get a nasty shock with packages on their
production systems suddenly 'upgraded' to something that hasn't been tested.

> That way everything talk to pgsql will only depend on one postgresql-client
> packages that will smoothly be upgraded to newer versions.
>   

And (assuming you ignored my last bit in the paragraph above) when you
upgrade for a security patch in something like openssl you'll suddenly
find all your clients upgraded to new versions without the necessary
testing... unless you knew rebuild everything in your test env rather
than just the required patch (there's a good reason I have my own
subversion and portnap server after all the changes since you started....)

> All database administrators will have the ability to chose the production
> version they do want without having to worry about a default version.
>   

Which we do currently with setting the default version across the
production infrastructure.

> 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.
>   

That would be the good (very good) part of the proposal.

> 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 I probably covered that above.

> Note that the upgrade path will be quite easy a simple pkg upgrade will propose
> to upgrade postgresql94-server and propose to remove postgresql94-client (which
> won't be needed anymore) and if anything on the machine is linked to libpq
> postgresql-client (the new one) will be automatically pulled.
>   

That is where your proposal will not work for us that have massive
production infrastructure...without modification... sounds very debianish.

Here's my thought which follows yours mostly (maybe the same, but not
the way I read yours.)

All postgres* go under /usr/local/postgresql<major><minor>
rc scripts are always version specific (ie
/usr/local/etc/rc.d/postgresql becomes /usr/local/etc/rc.d/postgresql84,
/usr/local/etc/rc.d/postgresql93 etc)
rc.conf stuff becomes version specific (ie postgresql_enable="YES"
becomes postgresql84_enable="YES" etc)
All ports then can be linked to default versions, the default can be in
Mk/bsd.default-versions.mk, and as it is in /etc/make.conf
All pkg tools then have a dependency to which ever is currently set in
the package and if the required lib is not installed you can install it
without a conflict to what is already on the systems...
All client binaries and tools can have symlinks in /usr/local/bin as
necessary with the major/minor suffix, and the "default versions" of the
*clients and tools* can be symlinked without a major/minor version.

That way I (and others) wont need to build and test several hundred
packages just because openssl has been patched and someone changed the
'default version' for postgresql in the mean time....

This way all the upgrade tools by the postgresql devs (like the binary
upgrade tool) will work (as all the libraries, clients and servers will
be installed) and you could even run 2 different postgresql servers on
the same host (which in many cases would help with testing and upgrade
paths)

The one thing this will change is something core to FreeBSD... it would
mean the libraries/headers would be (should be) under the same path
proposed... though I see no reason why they can't go under
/usr/local/lib/pgsql84/lib and /usr/local/include/pgsql84/include
...which would follow FreeBSD 'norm's.

You biggest problem here (and that's trivial really) is pkgconfig
knowing what is the default version and consequently what should link to
what.

additional benefits of such a system would be that things like slony
which often will work against new versions of postgresql but will not
build against them will actually work without all the messing around...
it will also mean that the port maintainer for things that link against
postgres libraries can set the 'working versions' to link against and
unlike on other platforms (linux based) you won't get dependency hell
where you have two packages that can only be linked to differing
versions of libpq but the packages themselves are not dependent on one
another (hypothetical example: if DBD::Pg won't link against 9.4 and
postgresql 9.4 is needed for python, both could be installed at the same
time... a more real example (from my servers).... not all that long ago,
slony wouldn't link against 9.x but DBD::Pg would and was required to be
linked against 9.1 on the test servers when testing a new production
environment.)

-- 
Michelle Sullivan
http://www.mhix.org/