Proposal to fix postgresql package maintainance nightmare

Shane Ambler FreeBSD at ShaneWare.Biz
Tue Jul 21 11:52:49 UTC 2015

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

FreeBSD - the place to B...Serving Data

Shane Ambler

More information about the freebsd-ports mailing list