Re: Proposal to fix postgresql package maintainance nightmare
Matthew Seaman wrote: On 04/08/2015 12:33, Kimmo Paasiala wrote: On Tue, Aug 4, 2015 at 1:07 PM, Michael Grünewald michip...@gmail.com wrote: On 21 Jul 2015, at 11:46, Baptiste Daroussin b...@freebsd.org wrote: - 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) … Could not jails be used to solve these issues by running the two (…) required versions in jailed environments? There is a little bit of setup, but if by any chance you are using some variation of the immutable server pattern, it should stay very low. You can't ask an average FreeBSD user who just wants to upgrade his/her PostgreSQL installation to set up jails that would be only used for one task. The upgrade should be achievable with just 'pkg upgrade' without any extra work. It's unlikely that 'pkg upgrade' would ever grow the facility to automatically do a fast binary update of the data directory from one postgresql release to another one. However, being able to install things in such a way that pg_upgrade(1) could be used to do all that would be very much more useful than the current 'dump databases ; upgrade packages ; reload databases' fandango. I'm just doing this - 8.4 - 9.4 and 9.0 - 9.4 on some machines... Its not that difficult to get pg_upgrade working... Don't know under pkgng, but under pkg_* tools and my mangled ports tree to make it to continue to work with pkg_* tools I just did this: ( cd /usr/ports/databases/postgresql90-server LOCALBASE=/usr/local.upgrade PKG_DBDIR=/var/db/pkg.upgrade make install ) ..to get the current version installed in it's own little environment, then shut the server down, deinstalled the existing 9.0 (using pkg_delete ) and installed 9.4 normally using '( cd /usr/ports/databases/postgresql94-server make install )' and now I can use pkg_upgrade ... My failing point is ip4r 1.x - 2.x but that's a completely separate issue in many ways... (and has it's own solutions). Regards, -- Michelle Sullivan http://www.mhix.org/ ___ freebsd-ports@freebsd.org mailing list https://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
On Tue, Aug 04, 2015 at 10:54:03AM +0300, Palle Girgensohn wrote: 21 jul 2015 kl. 15:03 skrev Baptiste Daroussin b...@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
Re: Proposal to fix postgresql package maintainance nightmare
On 21 Jul 2015, at 11:46, Baptiste Daroussin b...@freebsd.org wrote: - 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) … Could not jails be used to solve these issues by running the two (…) required versions in jailed environments? There is a little bit of setup, but if by any chance you are using some variation of the immutable server pattern, it should stay very low. ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
On Tue, Aug 4, 2015 at 1:07 PM, Michael Grünewald michip...@gmail.com wrote: On 21 Jul 2015, at 11:46, Baptiste Daroussin b...@freebsd.org wrote: - 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) … Could not jails be used to solve these issues by running the two (…) required versions in jailed environments? There is a little bit of setup, but if by any chance you are using some variation of the immutable server pattern, it should stay very low. You can't ask an average FreeBSD user who just wants to upgrade his/her PostgreSQL installation to set up jails that would be only used for one task. The upgrade should be achievable with just 'pkg upgrade' without any extra work. -Kimmo ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
On 04/08/2015 12:33, Kimmo Paasiala wrote: On Tue, Aug 4, 2015 at 1:07 PM, Michael Grünewald michip...@gmail.com wrote: On 21 Jul 2015, at 11:46, Baptiste Daroussin b...@freebsd.org wrote: - 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) … Could not jails be used to solve these issues by running the two (…) required versions in jailed environments? There is a little bit of setup, but if by any chance you are using some variation of the immutable server pattern, it should stay very low. You can't ask an average FreeBSD user who just wants to upgrade his/her PostgreSQL installation to set up jails that would be only used for one task. The upgrade should be achievable with just 'pkg upgrade' without any extra work. It's unlikely that 'pkg upgrade' would ever grow the facility to automatically do a fast binary update of the data directory from one postgresql release to another one. However, being able to install things in such a way that pg_upgrade(1) could be used to do all that would be very much more useful than the current 'dump databases ; upgrade packages ; reload databases' fandango. Cheers, Matthew signature.asc Description: OpenPGP digital signature
Re: Proposal to fix postgresql package maintainance nightmare
21 jul 2015 kl. 15:03 skrev Baptiste Daroussin b...@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? 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. 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? I'm not sure how we would handle alpha- and beta versions, but for production versions it would probably work.
Re: Proposal to fix postgresql package maintainance nightmare
4 aug 2015 kl. 12:11 skrev Baptiste Daroussin b...@freebsd.org: On Tue, Aug 04, 2015 at 10:54:03AM +0300, Palle Girgensohn wrote: 21 jul 2015 kl. 15:03 skrev Baptiste Daroussin b...@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
Re: Proposal to fix postgresql package maintainance nightmare
4 aug 2015 kl. 14:33 skrev Kimmo Paasiala kpaas...@gmail.com: On Tue, Aug 4, 2015 at 1:07 PM, Michael Grünewald michip...@gmail.com wrote: On 21 Jul 2015, at 11:46, Baptiste Daroussin b...@freebsd.org wrote: - 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) … Could not jails be used to solve these issues by running the two (…) required versions in jailed environments? There is a little bit of setup, but if by any chance you are using some variation of the immutable server pattern, it should stay very low. You can't ask an average FreeBSD user who just wants to upgrade his/her PostgreSQL installation to set up jails that would be only used for one task. The upgrade should be achievable with just 'pkg upgrade' without any extra work. -Kimmo True. Also, pg_upgrade cannot operate using different jails. But for some ppl, jails if definitely a solution. The problem we're trying to solve is not that, though, it is when not using jails. Supporting pg_upgrade is one of my main concerns, and also the ability to run several postgresql versions simultaneously for whatever reason. Palle ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
2015-07-21 21:47 GMT+02:00 Christoph Moench-Tegeder c...@burggraben.net:
## Baptiste Daroussin (b...@freebsd.org):
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.
Not that much worse than in some other environments :)
Comparing all the PostgreSQL packaging models, I like the debian model
best (PostgreSQL is my day job, so I can claim some experience here).
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)
As others already noted, using a psql (command line client) with a
version different from the respective server has it's limitations
(it works in general, but some of the meta commands may fail).
On the other hand, the ABI of libpq is stable enough for all the
supported versions of psql (and other clients). So you'd need one
package with libpq alone, and another one with psql and the other
client utils (http://www.postgresql.org/docs/9.4/static/reference-client.html
is the list). The server itself would be the another package.
The libpq package can always be built from the latest release
of PostgreSQL.
An additional postgresql-{client,server}-meta package would
provide symlinks from ${LOCALBASE}/bin/postgresbinary to
${PREFIX}/bin/postgresbinary (PREFIX being the PREFIX for
the selected PostgreSQL package, postgresbinary all the binaries
PostgreSQL installs).
For applications using PostgreSQL, the USES=pgsql logic has to
point configure and it's equivalents to the right pg_config, and
all well behaved applications should find the right libraries etc.
That way everything talk to pgsql will only depend on one postgresql-client
packages that will smoothly be upgraded to newer versions.
Using the schema outlined above, the normal PostgreSQL-using
application will only have to depend on the libpq package,
which is version-agnostic. Only those applications which
really need psql will have to depend on the (versioned)
posggresql-client package, or even better, the client-meta
package (that way, changes of the default postgresql version
will not require dependency-wrangling throughout the tree).
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)
Using the meta-packages (which could just pick up the previously
set default version), the upgrade path should be completely
transparent for most cases.
Another thing I've been thinking about is the ability to have
multiple PostgreSQL clusters on one machine. Currently there's
only the one postgresql_data variable in rc.conf. It shouldn't
be that hard to have something like
postgresql94_clusters=default stage dev
postgresql94_default_data=...
postgresql94_stage_data=...
Depending on the rc script handling (one script to rule them
all vs. each PostgreSQL has it's own script), we might even do
something line
postgresql_clusters=9.4:default 9.5:testing
postgresql_default_data=...
postgresql_testing_data=...
Is not that an other problem ? Maybe we need a generic mechanism to
manage instances (think mysql, tomcat, apache ...) ? At least a common
syntax, to avoid something like the [check|config][test|check] issue
Regards
Joris
If you want to enlist me for testing/script wrangling/etc.,
just drop me an email.
Regards,
Christoph
--
Spare Space
___
freebsd-ports@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-ports
To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
___
freebsd-ports@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-ports
To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
## Baptiste Daroussin (b...@freebsd.org):
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.
Not that much worse than in some other environments :)
Comparing all the PostgreSQL packaging models, I like the debian model
best (PostgreSQL is my day job, so I can claim some experience here).
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)
As others already noted, using a psql (command line client) with a
version different from the respective server has it's limitations
(it works in general, but some of the meta commands may fail).
On the other hand, the ABI of libpq is stable enough for all the
supported versions of psql (and other clients). So you'd need one
package with libpq alone, and another one with psql and the other
client utils (http://www.postgresql.org/docs/9.4/static/reference-client.html
is the list). The server itself would be the another package.
The libpq package can always be built from the latest release
of PostgreSQL.
An additional postgresql-{client,server}-meta package would
provide symlinks from ${LOCALBASE}/bin/postgresbinary to
${PREFIX}/bin/postgresbinary (PREFIX being the PREFIX for
the selected PostgreSQL package, postgresbinary all the binaries
PostgreSQL installs).
For applications using PostgreSQL, the USES=pgsql logic has to
point configure and it's equivalents to the right pg_config, and
all well behaved applications should find the right libraries etc.
That way everything talk to pgsql will only depend on one postgresql-client
packages that will smoothly be upgraded to newer versions.
Using the schema outlined above, the normal PostgreSQL-using
application will only have to depend on the libpq package,
which is version-agnostic. Only those applications which
really need psql will have to depend on the (versioned)
posggresql-client package, or even better, the client-meta
package (that way, changes of the default postgresql version
will not require dependency-wrangling throughout the tree).
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)
Using the meta-packages (which could just pick up the previously
set default version), the upgrade path should be completely
transparent for most cases.
Another thing I've been thinking about is the ability to have
multiple PostgreSQL clusters on one machine. Currently there's
only the one postgresql_data variable in rc.conf. It shouldn't
be that hard to have something like
postgresql94_clusters=default stage dev
postgresql94_default_data=...
postgresql94_stage_data=...
Depending on the rc script handling (one script to rule them
all vs. each PostgreSQL has it's own script), we might even do
something line
postgresql_clusters=9.4:default 9.5:testing
postgresql_default_data=...
postgresql_testing_data=...
If you want to enlist me for testing/script wrangling/etc.,
just drop me an email.
Regards,
Christoph
--
Spare Space
___
freebsd-ports@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-ports
To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
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/postgresqlmajorminor 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
Re: Proposal to fix postgresql package maintainance nightmare
On Tue, Jul 21, 2015 at 11:17:06AM +0100, Matthew Seaman wrote: On 07/21/15 10:46, Baptiste Daroussin wrote: 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. Looks like all current variants of postgresql-client provide the same shlib ABI version: libpq.so.5 --- which means under your scheme we'd end up with postgresql92-server loading libpq.so from postgresql94-client. That should work if the postgres devs have been managing their ABIs carefully, but it does seem like a potential trouble spot. Apart from that, yes! Can we have this yesterday please? not exactly, postgresql92-server will use libpq bundled inside the /usr/local/postgresql92/* to ensure if one day they missed an incompatibility we do not break any production servers :) Best regards, Bapt pgpMLYnWtXEni.pgp Description: PGP signature
Re: Proposal to fix postgresql package maintainance nightmare
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 pgpcJLTc2iz6Q.pgp Description: PGP signature
Re: Proposal to fix postgresql package maintainance nightmare
Baptiste Daroussin wrote: On Tue, Jul 21, 2015 at 01:15:27PM +0200, Michelle Sullivan wrote: Michelle Sullivan wrote: 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/postgresqlmajorminor 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. Slight addition... metaport's for 'postgresql-server' and 'postgresql-client' seem to be the ideal addition to this (forget the symlinking and pkgconfig - the metaport would take care of that ideally) I do not see the point of having metaport here? Respectfully, you don't run a massive DB infrastructure. Metaport would be good for putting in the symlinks to the correct version you need, would also take care of ensuring pkgconfig has the right version (not that I compile on the prod servers anymore, but I know some do) We in the end have one single postgresql-client (always latest version) No - in many cases one would want that, not not all - there were some differences between 8.4 and 9.0 which were not caught initially (certain operations (particularly the \char commands - which are essentially hardcoded SQL commands that look at the core schema) in 9.0 didn't work properly with 8.4 and visa-versa... (if I remember the versions correct - I'm sure someone here will know for certain which versions) ... on the other hand the libpq is backwards compatible (so far) for client/server communications. and multiple postgresql*-server which will bundle the client tools they go with and the symlinks here's your issue - I want 8.4 client and 9.4 client on the same host - how does it know which to symlink, or will both symlink? A metaport which looks at the default-version (like python does) would take care of this with less pain for those that need to test multiple versions on the same server, or are using their production servers for test (some people can't afford to keep separate test servers (I can with some 60+ servers under my wing, but when I started I couldn't - almost everything ran on the same host when I started with Pg - admittedly this was back in 2002 but even now if I was starting out, even with VMs I probably couldn't run multiple hosts - as most 'cheap hosting' is now VMs as well and you can't (usually) run a test VM within your 'cheap host' because it's already a VM and it is rare to find hosting that will allow VMs within VMs - if it's even possible.) so postgresql-client will have /usr/local/bin/psql and postgresql94-server will have: /usr/local/bin/psql94 - /usr/local/postgresql94/bin/psql This I agree with - however I would suggest postgresql-client is a metaport as python does rather than a specific python version in case of future (or past) incompatibility... eg what's going to happen when PG 10 comes out? A little more work now (possibly less) could save a lot of re-writing and headaches later. For example. Where would the metaport give something more? Best regards, Bapt -- Michelle Sullivan http://www.mhix.org/ ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
On Tue, Jul 21, 2015 at 01:15:27PM +0200, Michelle Sullivan wrote: Michelle Sullivan wrote: 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/postgresqlmajorminor 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. Slight addition... metaport's for 'postgresql-server' and 'postgresql-client' seem to be the ideal addition to this (forget the symlinking and pkgconfig - the metaport would take care of that ideally) I do not see the point of having metaport here? We in the end have one single postgresql-client (always latest version) and multiple postgresql*-server which will bundle the client tools they go with and the symlinks so postgresql-client will have /usr/local/bin/psql and postgresql94-server will have: /usr/local/bin/psql94 - /usr/local/postgresql94/bin/psql For example. Where would the metaport give something more? Best regards, Bapt pgpqWip3G8fkb.pgp Description: PGP signature
Re: Proposal to fix postgresql package maintainance nightmare
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 ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Re: Proposal to fix postgresql package maintainance nightmare
Shane Ambler wrote: 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. There have been issues with the old client connecting to the new server and the new client connecting to the old server. They are rare and only usually affect the use of 'short cut' commands (ie things like: \d and \dt in the client)... the connection itself is usually both backwards and forwards compatible just not the commands. -- Michelle Sullivan http://www.mhix.org/ ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to freebsd-ports-unsubscr...@freebsd.org
Proposal to fix postgresql package maintainance nightmare
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. 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. 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) 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. Best regards, Bapt pgpOFif4Tkptz.pgp Description: PGP signature
