[postgis-users] Query crashes Postgres?

[Giuseppe Broccolo]

Hi Luca,

>From what I understand from the logs you attached, the most likely scenario
is a failure at OS level, which made the Postmaster process crash, probably
stopped by the OS itself with an OOM kill.

This is compatible with what is said here (the postmaster process received
a Signal 9, a forced kill):
2021-01-27 07:06:02.165 CET [582] LOG:  Serverprozess (PID 10391) wurde
von Signal 9 beendet: Getötet

The child process which effectively triggered the issue is the backend
which was running the update:
2021-01-27 07:06:02.165 CET [582] DETAIL:  Der fehlgeschlagene Prozess
führte aus: UPDATE city_boundaries SET way =
ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 300, 'join=miter'), -300,
'join=miter')));

For the following reason (memory corruption):
2021-01-27 07:06:02.179 CET [10238] postgres at gis DETAIL:  Der Postmaster
hat diesen Serverprozess angewiesen, die aktuelle Transaktion
zurückzurollen und die Sitzung zu beenden, weil ein anderer
Serverprozess abnormal beendet wurde und möglicherweise das Shared
Memory verfälscht hat.

This made the PostgreSQL server restart in recovery mode:
2021-01-27 07:06:03.208 CET [10411] postgres at gis FATAL:  das
Datenbanksystem ist im Wiederherstellungsmodus

In the recovery mode, it started using the WALs available on the local
storage to recover from the crash:
2021-01-27 07:06:03.937 CET [10410] LOG:  Redo beginnt bei 11F/22E1D520
2021-01-27 07:06:04.317 CET [10410] LOG:  ungültige Datensatzlänge bei
11F/24124828: 24 erwartet, 0 erhalten
2021-01-27 07:06:04.317 CET [10410] LOG:  Redo fertig bei 11F/241247F0

There's effectively a "warning" message about an issue in applying a
specific location of the WALs (11F/24124828),
but I would avoid to focus for the moment on this, because it's a symptom
of the real issue that made PostgreSQL crash,
not the origin. It was then able to properly restart:
2021-01-27 07:06:05.597 CET [582] LOG:  Datenbanksystem ist bereit, um
Verbindungen anzunehmen

To have a confirmation about the source of the issue, I'd suggest to have a
look at system logs (syslog, dmesg, ...). I am
expecting some entry related to an OOM kill performed by the OS.

If this is confirmed, then the solution would be to check the configuration
of PostgreSQL, OS, hardware (specially RAM resources), etc.

Also the fact that the transaction worked properly on a reduced dataset
makes me think of a problem related to the size/complexity of the dataset,
with an undersized configuration of the whole DB server.

Hope it helps,
Giuseppe.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210127/a5c12004/attachment.html>