Thread: [GENERAL] pg on Debian servers

[GENERAL] pg on Debian servers

From
Mark Morgan Lloyd
Date:
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

Re: [GENERAL] pg on Debian servers

From
Magnus Hagander
Date:


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 

--

Re: [GENERAL] pg on Debian servers

From
rob stone
Date:

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: [GENERAL] pg on Debian servers

From
Christoph Berg
Date:
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

Re: [GENERAL] pg on Debian servers

From
Magnus Hagander
Date:


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


--

Re: [GENERAL] pg on Debian servers

From
Mark Morgan Lloyd
Date:
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

Re: [GENERAL] pg on Debian servers

From
Jan Claeys
Date:
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

Re: [GENERAL] pg on Debian servers

From
Mark Morgan Lloyd
Date:
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

Re: [GENERAL] pg on Debian servers

From
Karsten Hilbert
Date:
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

Re: [GENERAL] pg on Debian servers

From
rob stone
Date:

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

Re: [GENERAL] pg on Debian servers

From
Mark Morgan Lloyd
Date:
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