Thread: BUG #15547: default timezone on servers running while time changedfrom PDT to PST reverting to UTC.

The following bug has been logged on the website:

Bug reference:      15547
Logged by:          Matteo Bignotti
Email address:      gugoll@gmail.com
PostgreSQL version: 9.6.1
Operating system:   CentOS 7
Description:

I have a bunch of servers that never restarted in the past couple of months
and had postgres running since before Nov. 4th and I discovered that their
default timezone changed in the background.

● postgresql.service - PostgreSQL database server
[...]
   Active: active (running) since Tue 2018-10-23 15:52:42 PDT; 1 months 18
days ago

in postgresql.conf the value of timezone and log_timezone is set to
"localtime" and I have a symlink in /usr/share/zoneinfo/ that points at the
timezone I want (also I know that's how Debian handles it). This works fine,
always, except in this situation I cannot seem to reproduce in any way. A
simple restart of postgres fixes the situation, but that's not what I wanted
to do.

I run these queries on 2 different servers in the America/Los_Angeles
timezone: psql [credentials] -c "SELECT now();" -c "show timezone;" -c
"SELECT timezone('UTC', now()) as UTC"

[no bug]
              now
-------------------------------
 2018-12-11 16:02:11.783056-08
(1 row)
 TimeZone
-----------
 localtime
(1 row)
            utc
----------------------------
 2018-12-12 00:02:11.823465
(1 row)

[with bug]
              now
-------------------------------
 2018-12-12 00:03:30.970079+00
(1 row)
 TimeZone
-----------
 localtime
(1 row)
            utc
----------------------------
 2018-12-12 00:03:30.971193
(1 row)

I really blame this situation on DST simply because that's the only thing
that happened in this timeframe that could've caused a timezone "flop".

both servers point to America/Los_Angeles and this server "with bug" is the
last one I have that exhibits, all my attempts at reproducing this have been
in vain.

Thank you

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> I have a bunch of servers that never restarted in the past couple of months
> and had postgres running since before Nov. 4th and I discovered that their
> default timezone changed in the background.

Hmph.  Ordinarily I'd expect that the default zone data would be cached
in the postmaster and inherited via fork() by backends, so that as long
as you didn't do something like change the timezone setting in
postgresql.conf, the behavior would be stable until postmaster restart.
(People who live in zones with frequent DST law changes have complained
of that ... but I'm not very much in a hurry to change it.)

So it's not at all clear what happened here.  I think that Red Hat did
push out a tzdata update in early November, but even so, the behavior
of America/Los_Angeles shouldn't have changed.  *Maybe* this could
be explained by having restarted those postmasters during the tiny
interval where the package update was happening and there wasn't anything
valid for /etc/localtime to point at --- but that stretches credulity.

If you can figure out a way to reproduce this, we'd be interested to
hear what it is.

            regards, tom lane

Matteo <gugoll@gmail.com> writes:
> I thought the same thing about tzdata but I didn't update that on any
> servers in that timeframe, the servers are only updated manually and the
> only package that I added was "psmisc-22.20-15.el7.x86_64" on Nov 14th.

Hm.  Just to clarify: do you believe that the servers with the wrong
timezone behavior actually changed behavior sometime after being started?
It'd be way easier to believe that they'd been wrong since postmaster
start.  Also, do you have the postmaster logs going back to startup?
It'd be interesting to check for possible error/warning messages at
postmaster start, and/or whenever the behavior changed.

            regards, tom lane