pg on Debian servers

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/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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:

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/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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