Обсуждение: [GENERAL] pg on Debian servers
Apologies for something which is distro related, but I was bitten by a "silly mistake"- one of my own, I hasten to say- earlier. Several legacy programs written in Delphi ground to a halt this morning, which turned out to be because a Debian system had updated its copy of PostgreSQL and restarted the server, which broke any live connections. At least some versions of Delphi, not to mention other IDE/RAD tools with database-aware components, don't automatically try to reestablish a database session that's been interrupted. In any event, an unexpected server restart (irrespective of all investment in UPSes etc.) has the potential of playing havoc on a clustered system. Is there any way that either the package maintainer or a site administrator/programmer such as myself can mark the Postgres server packages as "manual upgrade only" or similar? Or since I'm almost certainly not the first person to be bitten by this, is there a preferred hack in mitigation? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Sat, Nov 11, 2017 at 2:03 PM, Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> wrote:
Apologies for something which is distro related, but I was bitten by a "silly mistake"- one of my own, I hasten to say- earlier.
Several legacy programs written in Delphi ground to a halt this morning, which turned out to be because a Debian system had updated its copy of PostgreSQL and restarted the server, which broke any live connections.
At least some versions of Delphi, not to mention other IDE/RAD tools with database-aware components, don't automatically try to reestablish a database session that's been interrupted. In any event, an unexpected server restart (irrespective of all investment in UPSes etc.) has the potential of playing havoc on a clustered system.
Is there any way that either the package maintainer or a site administrator/programmer such as myself can mark the Postgres server packages as "manual upgrade only" or similar? Or since I'm almost certainly not the first person to be bitten by this, is there a preferred hack in mitigation?
Certainly. Unrelated to PostgreSQL, this is a standard feature in Debian. Commonly used to prevent things like kernel upgrades from happening on the same schedule as others.
Basically, you put the package "on hold". See the debian administratino guide at https://debian-administration.org/article/67/Preventing_Debian_Package_Upgrades
On Sat, 2017-11-11 at 13:03 +0000, Mark Morgan Lloyd wrote: > Apologies for something which is distro related, but I was bitten by > a > "silly mistake"- one of my own, I hasten to say- earlier. > > Several legacy programs written in Delphi ground to a halt this > morning, > which turned out to be because a Debian system had updated its copy > of > PostgreSQL and restarted the server, which broke any live > connections. > > At least some versions of Delphi, not to mention other IDE/RAD tools > with database-aware components, don't automatically try to > reestablish a > database session that's been interrupted. In any event, an > unexpected > server restart (irrespective of all investment in UPSes etc.) has > the > potential of playing havoc on a clustered system. > > Is there any way that either the package maintainer or a site > administrator/programmer such as myself can mark the Postgres server > packages as "manual upgrade only" or similar? Or since I'm almost > certainly not the first person to be bitten by this, is there a > preferred hack in mitigation? > > -- > Mark Morgan Lloyd > markMLl .AT. telemetry.co .DOT. uk > > [Opinions above are the author's, not those of his employers or > colleagues] > > Hello Mark, Probably caused by systemd. You can disable the postgresql service and re-name the script in init.d. You then have to start postgres via a shell script. You can also mark packages to be on "hold" but I don't know exactly what happens for major version upgrades as the current version is 9 but when you run an upgrade via apt it will try to install version 10 which is no big deal as the binaries will end up in different paths, however libpq will be updated and that may cause a restart. I run upgrades without any applications running so I don't know exactly what could happen when using unattended upgrades. HTH. Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Magnus Hagander 2017-11-11 <CABUevExt7aLarQ2RE5KP9rRUTQSioAxi5FMq=JJ9neBTbC++OA@mail.gmail.com> > > Is there any way that either the package maintainer or a site > > administrator/programmer such as myself can mark the Postgres server > > packages as "manual upgrade only" or similar? Or since I'm almost certainly > > not the first person to be bitten by this, is there a preferred hack in > > mitigation? > > > Certainly. Unrelated to PostgreSQL, this is a standard feature in Debian. > Commonly used to prevent things like kernel upgrades from happening on the > same schedule as others. > > Basically, you put the package "on hold". See the debian administratino > guide at > https://debian-administration.org/article/67/Preventing_Debian_Package_Upgrades Another thing you can do is preventing package upgrades from stopping/starting services by using a policy-rc.d: https://jpetazzo.github.io/2013/10/06/policy-rc-d-do-not-start-services-automatically/ https://people.debian.org/~hmh/invokerc.d-policyrc.d-specification.txt However, if you do that, you need to take measures to actually restart into the new version manually later. Christoph -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Sat, Nov 11, 2017 at 2:23 PM, rob stone <floriparob@gmail.com> wrote:
Hello Mark,
On Sat, 2017-11-11 at 13:03 +0000, Mark Morgan Lloyd wrote:
> Apologies for something which is distro related, but I was bitten by
> a
> "silly mistake"- one of my own, I hasten to say- earlier.
>
> Several legacy programs written in Delphi ground to a halt this
> morning,
> which turned out to be because a Debian system had updated its copy
> of
> PostgreSQL and restarted the server, which broke any live
> connections.
>
> At least some versions of Delphi, not to mention other IDE/RAD tools
> with database-aware components, don't automatically try to
> reestablish a
> database session that's been interrupted. In any event, an
> unexpected
> server restart (irrespective of all investment in UPSes etc.) has
> the
> potential of playing havoc on a clustered system.
>
> Is there any way that either the package maintainer or a site
> administrator/programmer such as myself can mark the Postgres server
> packages as "manual upgrade only" or similar? Or since I'm almost
> certainly not the first person to be bitten by this, is there a
> preferred hack in mitigation?
>
> --
> Mark Morgan Lloyd
> markMLl .AT. telemetry.co .DOT. uk
>
> [Opinions above are the author's, not those of his employers or
> colleagues]
>
>
Probably caused by systemd.
Systemd has nothing to do with it, it's Debian standard to restart the services when the binaries have changed, regardless of sysvinit or systemd.
You can disable the postgresql service and
re-name the script in init.d. You then have to start postgres via a
shell script.
The init.d script is not used with systemd.
You can also mark packages to be on "hold" but I don't know exactly
what happens for major version upgrades as the current version is 9 but
when you run an upgrade via apt it will try to install version 10 which
is no big deal as the binaries will end up in different paths, however
The current version is 10. The previous version was 9.6. Version 9 was more than 5 years ago.
And the apt system will *never* try to upgrade across a major version. You do a new install to get the new version. An upgrade operation will put you at the latest minor release for the currently installed version.
libpq will be updated and that may cause a restart. I run upgrades
without any applications running so I don't know exactly what could
happen when using unattended upgrades.
libpq does get upgraded, but it does not cause restarts. A restart of a client application using libpq must be done manually by the administrator (unless there is specific code in the client application or it's packaging to deal with that).
On 11/11/17 13:45, Christoph Berg wrote: > Re: Magnus Hagander 2017-11-11 <CABUevExt7aLarQ2RE5KP9rRUTQSioAxi5FMq=JJ9neBTbC++OA@mail.gmail.com> >>> Is there any way that either the package maintainer or a site >>> administrator/programmer such as myself can mark the Postgres server >>> packages as "manual upgrade only" or similar? Or since I'm almost certainly >>> not the first person to be bitten by this, is there a preferred hack in >>> mitigation? >> >> >> Certainly. Unrelated to PostgreSQL, this is a standard feature in Debian. >> Commonly used to prevent things like kernel upgrades from happening on the >> same schedule as others. >> >> Basically, you put the package "on hold". See the debian administratino >> guide at >> https://debian-administration.org/article/67/Preventing_Debian_Package_Upgrades > > Another thing you can do is preventing package upgrades from > stopping/starting services by using a policy-rc.d: > > https://jpetazzo.github.io/2013/10/06/policy-rc-d-do-not-start-services-automatically/ > https://people.debian.org/~hmh/invokerc.d-policyrc.d-specification.txt > > However, if you do that, you need to take measures to actually restart > into the new version manually later. Thanks Christoph, Magnus and Rob (and anybody else whose contribution I've not seen yet :-) I think that the "preventing upgrades" route is the one to follow, since inhibiting the restart would obviously present a risk that something loaded dynamically could get out of step. As an at least temporary hack I've disabled unattended updates using # systemctl disable unattended-upgrades.service This is obviously a system which is deeply isolated from public exposure. In the general case I'd caution against any attempt to edit the content of /etc/init.d on recent versions of Debian, since I've come across at least one package that puts a file in there and then ignores both it and the associated control in /etc/default. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Sat, 2017-11-11 at 14:23 +0000, Mark Morgan Lloyd wrote: > I think that the "preventing upgrades" route is the one to follow, > since inhibiting the restart would obviously present a risk that > something loaded dynamically could get out of step. As an at least > temporary hack I've disabled unattended updates using > > # systemctl disable unattended-upgrades.service Unattended-upgrades is configurable and allows whitelisting package origins, as well as blacklisting packages so that they never get upgraded automatically (you can still upgrade them manually, of course). See /etc/apt/apt.conf.d/50unattended-upgrades (the default version of that file includes documentation as comments). Also see the unattended-upgrade(8) manpage, and the on/off switch in /etc/apt/apt.conf.d/20auto-upgrades -- Jan Claeys -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 11/11/17 16:45, Jan Claeys wrote: > On Sat, 2017-11-11 at 14:23 +0000, Mark Morgan Lloyd wrote: >> I think that the "preventing upgrades" route is the one to follow, >> since inhibiting the restart would obviously present a risk that >> something loaded dynamically could get out of step. As an at least >> temporary hack I've disabled unattended updates using >> >> # systemctl disable unattended-upgrades.service > > Unattended-upgrades is configurable and allows whitelisting package > origins, as well as blacklisting packages so that they never get > upgraded automatically (you can still upgrade them manually, of > course). > > See /etc/apt/apt.conf.d/50unattended-upgrades (the default version of > that file includes documentation as comments). > > Also see the unattended-upgrade(8) manpage, and the on/off switch in > /etc/apt/apt.conf.d/20auto-upgrades Thanks Jan, noted. I was, of course, working to a fairly traditional priority: get things running again, whine for a few hours, and only later implement a proper fix :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Sat, Nov 11, 2017 at 01:03:18PM +0000, Mark Morgan Lloyd wrote: > Several legacy programs written in Delphi ground to a halt this morning, > which turned out to be because a Debian system had updated its copy of > PostgreSQL and restarted the server, which broke any live connections. > > At least some versions of Delphi, not to mention other IDE/RAD tools with > database-aware components, don't automatically try to reestablish a database > session that's been interrupted. In any event, an unexpected server restart > (irrespective of all investment in UPSes etc.) has the potential of playing > havoc on a clustered system. > > Is there any way that either the package maintainer or a site > administrator/programmer such as myself can mark the Postgres server > packages as "manual upgrade only" or similar? Or since I'm almost certainly > not the first person to be bitten by this, is there a preferred hack in > mitigation? Apart from that (putting packages on hold), PostgreSQL updates on Debian don't upgrade existing clusters automatically. They do create a new cluster but the old one is kept around and stays running, IIRC even on the very same port. (Having gone all the way from PG 7.1 to PG 10 on Debian :) What did pg_lsclusters say ? There must have been something additional at play. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Sat, 2017-11-11 at 14:30 +0100, Magnus Hagander wrote: > > > > The init.d script is not used with systemd. > > > Hello Magnus, Many months ago on a bog standard Debian set-up did a re-boot and ended up with postmasters running for 9.2, 9.4, 9.5 and 9.6 all started one after the other. There was a script in init.d which read thru /usr/lib/postgresql and it started running Postgres for each version it found. Fortunately, all listening on different ports. The fix was to disable that script as well as the systemd service. Doing the upgrade to 10 in a few weeks. Will let you know how it goes. I assume you are aware of this DSA:- Debian Security Advisory DSA-4029-1 ----------------------------------------------------------------------- Package : postgresql-common CVE ID : CVE-2017-8806 It was discovered that the pg_ctlcluster, pg_createcluster and pg_upgradecluster commands handled symbolic links insecurely which could result in local denial of service by overwriting arbitrary files. For the oldstable distribution (jessie), this problem has been fixed in version 165+deb8u3. For the stable distribution (stretch), this problem has been fixed in version 181+deb9u1. Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 12/11/17 19:15, Karsten Hilbert wrote: > On Sat, Nov 11, 2017 at 01:03:18PM +0000, Mark Morgan Lloyd wrote: > >> Several legacy programs written in Delphi ground to a halt this morning, >> which turned out to be because a Debian system had updated its copy of >> PostgreSQL and restarted the server, which broke any live connections. >> >> At least some versions of Delphi, not to mention other IDE/RAD tools with >> database-aware components, don't automatically try to reestablish a database >> session that's been interrupted. In any event, an unexpected server restart >> (irrespective of all investment in UPSes etc.) has the potential of playing >> havoc on a clustered system. >> >> Is there any way that either the package maintainer or a site >> administrator/programmer such as myself can mark the Postgres server >> packages as "manual upgrade only" or similar? Or since I'm almost certainly >> not the first person to be bitten by this, is there a preferred hack in >> mitigation? > > Apart from that (putting packages on hold), PostgreSQL > updates on Debian don't upgrade existing clusters > automatically. They do create a new cluster but the old one > is kept around and stays running, IIRC even on the very same > port. > > (Having gone all the way from PG 7.1 to PG 10 on Debian :) With the caveat that Debian has only comparatively-recently introduced unattended updates as the default... I think only with Stretch. If you're still on Jessie you can yet be saved :-) > What did > > pg_lsclusters > > say ? I don't have it from the time of the problem, but currently it gives me Ver Cluster Port Status Owner Data directory Log file 9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log i.e. a single-server system, although I've since done a manual restart so that I could change some DIMMs. However syslog and postgresql-9.6-main.log show me this: Nov 11 06:27:38 postgres1 systemd[1]: Starting Daily apt upgrade and clean activities... Nov 11 06:28:05 postgres1 systemd[1]: Reloading. Nov 11 06:28:07 postgres1 systemd[1]: Reloading. Nov 11 06:28:07 postgres1 systemd[1]: Stopped PostgreSQL RDBMS. Nov 11 06:28:07 postgres1 systemd[1]: Stopping PostgreSQL Cluster 9.6-main... Nov 11 06:28:08 postgres1 systemd[1]: Stopped PostgreSQL Cluster 9.6-main. Nov 11 06:28:10 postgres1 systemd[1]: Reloading. 2017-11-11 06:28:07.587 UTC [675] LOG: received fast shutdown request 2017-11-11 06:28:07.587 UTC [675] LOG: aborting any active transactions [Session names here] 2017-11-11 06:28:07.607 UTC [730] LOG: autovacuum launcher shutting down [More session names here] 2017-11-11 06:28:07.680 UTC [727] LOG: shutting down 2017-11-11 06:28:07.984 UTC [675] LOG: database system is shut down 2017-11-11 06:28:13.039 UTC [11122] LOG: database system was shut down at 2017-11-11 06:28:07 UTC 2017-11-11 06:28:13.081 UTC [11122] LOG: MultiXact member wraparound protections are now enabled 2017-11-11 06:28:13.085 UTC [11126] LOG: autovacuum launcher started 2017-11-11 06:28:13.085 UTC [11121] LOG: database system is ready to accept connections 2017-11-11 06:28:13.371 UTC [11128] [unknown]@[unknown] LOG: incomplete startup packet All live applications saw that as a loss of database connectivity, yet when I was alerted by their squeals of anguish (MIDI on app servers has its uses :-) I found the database server running and accepting connections. > There must have been something additional at play. The apps are written in Delphi, I admit not a very recent version and they're due to be converted to Lazarus which is an open-source and portable clone. I'll defend my choice of language since it is, basically, the best "4GL" you'll find. However one flaw of Delphi etc. is that they assume that they can safely hold a database session open for an extended period. I can't speak for Delphi any more since it has, basically, priced itself out of our league particularly taking into account its lack of portability, but FPC/Lazarus appears to have something which is intended to reconnect a lost session, although it's so far unimplemented. So I've got multiple options for fixing this at the application level: either fill in the unimplemented bit of the database control in the Lazarus Class Library, or prevent apps from holding database connections open. But the real problem, I feel, is that Debian is enabling unattended upgrades without checking with the user, and while an attended upgrade normally asks for confirmation before restarting a daemon an unattended one doesn't. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general