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

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(at)kartgis(dot)com(dot)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