MIME-Version: 1.0
From: Krzysztof Tomaszewski <ktomaszewski@kartgis.com.pl>
Date: Tue, 13 Aug 2024 19:22:17 +0200
Message-ID: 
 <CALq0ouXF0m2AGdrmW4QR_xB9TT7a6=bVPv2z6U2fUES6n3ZEpg@mail.gmail.com>
Subject: Systemd may start PostgreSQL cluster before time is properly setup on
 the host machine
To: pgsql-pkg-debian@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CALq0ouXF0m2AGdrmW4QR_xB9TT7a6%3DbVPv2z6U2fUES6n3ZEpg%40mail.gmail.com>
Precedence: bulk

Hi All,

I previously published following analysis on redmine.postgresql.org as
an issue #8009 about 2 months ago. As this system seems to be dormant
I took liberty to re-post it here. Hope it is OK.


PostgreSQL systemd unit postgresql@.service provided by
postgresql-common package is not setup to start after time-set.target
nor time-sync.target. In case of SysV init script provided by the same
package, there is proper dependency on $time in LSB stanza.

Without one of those, cluster may start before time is properly setup
(in crude or precise way respectively).

According to systemd documentatnion (systemd.special(7) and
systemd-sysv-generator(8)) when systemd generates unit for SysV init
script, it transform dependency on $time to dependency on
time-sync.target so that time-sync.target seems more appropriate than
time-set.target at least from consistency standpoint.

For example, when machine clock is setup in UTC (as it usually should)
and local time is different, PostgreSQL during start may interpret
time without timezone applied as one with it.

As esoteric and contrived as it sounds, I recently stumbled upon a
case in production environment, where `pg_postmaster_start_time()` was
returning time in the future, with shift consistent with timezone
shift in that environment. Investigation of which case led me to above
mentioned findings.

As a side note, SysV init script is also configured to be started
after $local_fs and $remote_fs. Systemd provides analogical targets
($local-fs.target and $remote-fs.target respectively) but
postgresql@.service do not use them (again, systemd-sysv-generator
support $remote_fs but interestingly ignores $local_fs in
documentation and code of sysv-generator.c for some unknown to me
reason).

This probably also should be kept consistent among starting
mechanisms, i.e. it should be added to unit file or dropped from init
script stanza.

Another thing of some potential interest may be how RPM packages
provided by PostgreSQL project, handle similar unit file. Unit file
from RPM package also lacks dependency on any time related target but
has additional dependency on syslog.target which may not (do not?)
exists at all. As syslog providers do not add dependency on time
related targets (only network related), this will not position
PostgreSQL start after time is properly setup even in implicit
(transitive) way.

There are some other differences between unit files provided directly
by PostgreSQL project for Debian and RPM based distros, that lead to
different behavior among them but are unrelated to this issue (as they
mostly relate to how they handle timeouts, with infinity for start and
stop in RPM based systems and 1h limit for stopping Postgres cluster
in Debian).

Regards,
Krzysztof Tomaszewski
--=20
ktomaszewski@kartgis.com.pl
*KartGIS sp. z o.o.* | www.kartgis.com.pl
Aleje Jerozolimskie 81
02-001 Warszawa
NIP 9512276974, REGON 141747787
Fax 22-213-96-40 <fax:222139640>

Zarejestrowana w S=C4=85dzie Rejonowym dla m.st. Warszawy w Warszawie,
XII Wydzia=C5=82 Gospodarczy Krajowego Rejestru S=C4=85dowego
pod numerem KRS: 0000517511
Warto=C5=9B=C4=87 Kapita=C5=82u Zak=C5=82adowego: 611 300,00 PLN