Proposal to fix postgresql package maintainance nightmare

[Baptiste Daroussin]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 181 bytes
Desc: not available
URL: <http://lists.freebsd.org/pipermail/freebsd-ports/attachments/20150721/f2fd224e/attachment.bin>