Thread: Systemd may start PostgreSQL cluster before time is properly setup on the host machine

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
--
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ądzie Rejonowym dla m.st. Warszawy w Warszawie,
XII Wydział Gospodarczy Krajowego Rejestru Sądowego
pod numerem KRS: 0000517511
Wartość Kapitału Zakładowego: 611 300,00 PLN

Re: Krzysztof Tomaszewski
> 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.

Hi,

I had seen it, but didn't have the spoons to look closer it it back
then.

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

The meta problem here is that systemd makes getting this right way too
complicated. There is little advice on which of these 100 special
targets the average service should depend on. Plus the problem that
"After", "Require" and a few more others are all very similar and hard
to tell apart.

Browsing though occurrences of "time-sync.target" in Debian, everyone
seems to be doing something slightly different:

https://codesearch.debian.net/search?q=time-sync.target&literal=1

It seems to me that the correct thing to do would be simply:

After=time-sync.target

... and leave the FS dependencies the automatic dependencies added by
"RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I" which
already exists.

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

I don't think that's a problem, the system time will always be UTC
internally, and the system time zone just changes how it is formatted.
PostgreSQL is always timezone aware.

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

If that went wrong, perhaps the machine clock wasn't set to UTC?

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

TBH, I'm not going to touch the sysv script. It still works in
chroots/containers without systemd when testing something there, but
it's not relevant for anything that actually boots.

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

Again, we can consider that if there's any "best practise" set of
dependencies we should add to the service, but since the default
config isn't set to syslog, I don't see we should include
syslog.service.

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

The suggested service file from the PG documentation is this:

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity

[Install]
WantedBy=multi-user.target


I added the TimeoutStopSec=1h so rebooting a server never hangs
indefinitely (and if 1h isn't enough to write out a checkpoint, I
don't know).

Christoph

Hi

> Re: Krzysztof Tomaszewski
> > 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.

> I had seen it, but didn't have the spoons to look closer it it back
> then.

Thank you very much for taking time to look into this, I really appreciate it.
Also, I hadn't mean to put any additional pressure, just wasn't sure
do my previous message reached some wise eyes or not :)

> > 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.
>
(...)
> It seems to me that the correct thing to do would be simply:
>
> After=time-sync.target

That would also be my understanding.

> ... and leave the FS dependencies the automatic dependencies added by
> "RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I" which
> already exists.
>
> > 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.
>
> I don't think that's a problem, the system time will always be UTC
> internally, and the system time zone just changes how it is formatted.
> PostgreSQL is always timezone aware.
>
> > 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.
>
> If that went wrong, perhaps the machine clock wasn't set to UTC?

Hm, I looked at this again and on system that I observed the problem,
"RTC" is in UTC (as it run in virtual machine, it is not true hardware
clock).
Nevertheless my line of reasoning about (lack of) of time zone
information in early boot stage was probably wrong, as you pointed
out.

It seams that RTC on that system had drifted substantially (and by
similar time amount to zone shift which tricked me), and that is the
reason why PostgreSQL is getting wrong time when started before
time-sync.target. As it it virtual system, OS can not truly (re)set
the RTC, so this drift reoccur after reboot. Solution (beyond properly
managing RTC of course) seems to stil be the same, depending on
running after time-sync.target.

> > 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.
>
> TBH, I'm not going to touch the sysv script. It still works in
> chroots/containers without systemd when testing something there, but
> it's not relevant for anything that actually boots.

Sure. My thinking was really in direction of enhancing unit file only.
I just was not sure if time dependency was not cary out into unit file
intentionally for some reason.

> > 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.
>
> Again, we can consider that if there's any "best practise" set of
> dependencies we should add to the service, but since the default
> config isn't set to syslog, I don't see we should include
> syslog.service.

I probably made this point to convoluted, sorry. I did not and do not
understand way unit file in RPM package depends on systlog.service,
too. I tried to figure that out by analyzing other potential
dependencies pulled in by that dependency, but found none of actual
interest. As you pointed out, reasoning about systemd is not always
trivial.

> > 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).
>
> The suggested service file from the PG documentation is this:
>
> [Unit]
> Description=PostgreSQL database server
> Documentation=man:postgres(1)
> After=network-online.target
> Wants=network-online.target
>
> [Service]
> Type=notify
> User=postgres
> ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
> ExecReload=/bin/kill -HUP $MAINPID
> KillMode=mixed
> KillSignal=SIGINT
> TimeoutSec=infinity
>
> [Install]
> WantedBy=multi-user.target

Maybe documentation should mention After=time-sync.target too?

> I added the TimeoutStopSec=1h so rebooting a server never hangs
> indefinitely (and if 1h isn't enough to write out a checkpoint, I
> don't know).

I pointed out differences between rpm and deb packaged service unit
files mostly because I was surprised by they existence, as one of the
initial promise of using systemd unit files over init scripts was
consistency across distributions. Also the reasoning behind those
differences was not clear to me. Thanks for providing your line of
thoughts behind it.

If I may provide my thinking about it, having predictable timeout by
default is valuable. If one needs to make it longer or get rid of it
completely, then using unit file drop-ins to redefine it is always an
option, that can be applied on instance that would benefit from it. My
guess would be also, that having machine stuck during closing process,
probably with access over network cut out already, would trigger
operators to power off such machine anyway. And having TimeoutStopSec
set explicitly may at lest hint administrators, that they may need to
tune it for particular environment.

Kind regards,
Krzysztof

--
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ądzie Rejonowym dla m.st. Warszawy w Warszawie,
XII Wydział Gospodarczy Krajowego Rejestru Sądowego
pod numerem KRS: 0000517511
Wartość Kapitału Zakładowego: 611 300,00 PLN