Thread

1. pg on Debian servers

   Mark Morgan Lloyd <markmll.pgsql-general@telemetry.co.uk> — 2017-11-11T13:03:18Z

   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]
   
   
   

2. Re: pg on Debian servers

   Magnus Hagander <magnus@hagander.net> — 2017-11-11T13:21:16Z

   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
   
   
   -- 
    Magnus Hagander
    Me: https://www.hagander.net/ <http://www.hagander.net/>
    Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
   

3. Re: pg on Debian servers

   rob stone <floriparob@gmail.com> — 2017-11-11T13:23:46Z

   
   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 
   
   
   

4. Re: pg on Debian servers

   Christoph Berg <myon@debian.org> — 2017-11-11T13:28:05Z

   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
   
   
   

5. Re: pg on Debian servers

   Magnus Hagander <magnus@hagander.net> — 2017-11-11T13:30:07Z

   On Sat, Nov 11, 2017 at 2:23 PM, rob stone <floriparob@gmail.com> wrote:
   
   >
   >
   > 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.
   
   
   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).
   
   
   -- 
    Magnus Hagander
    Me: https://www.hagander.net/ <http://www.hagander.net/>
    Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
   

6. Re: pg on Debian servers

   Mark Morgan Lloyd <markmll.pgsql-general@telemetry.co.uk> — 2017-11-11T14:23:06Z

   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]
   
   
   

7. Re: pg on Debian servers

   Jan Claeys <lists@janc.be> — 2017-11-11T16:40:59Z

   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
   
   
   
   

8. Re: pg on Debian servers

   Mark Morgan Lloyd <markmll.pgsql-general@telemetry.co.uk> — 2017-11-11T17:29:27Z

   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]
   
   
   

9. Re: pg on Debian servers

   Karsten Hilbert <karsten.hilbert@gmx.net> — 2017-11-12T19:13:23Z

   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
   
   
   

10. Re: pg on Debian servers

    rob stone <floriparob@gmail.com> — 2017-11-12T19:52:49Z

    
    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
    
    
    
    
    

11. Re: pg on Debian servers

    Mark Morgan Lloyd <markmll.pgsql-general@telemetry.co.uk> — 2017-11-13T11:11:17Z

    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]