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

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