9.6.11 vs 9.6.12: UTC to UCT change

[aronatkins]

The postgres:9.6 tag shifted from 9.6.11 to 9.6.12, and shows the following TZ difference:

Old:

$ docker run -dit --rm postgres:9.6.11
3828d6ab409d60f4c25e34e9f43d0ffc8c9ca7e9a35f35d1bf6813d83f01ccff
$ docker exec -it 3828d6ab409d60f4c25e34e9f43d0ffc8c9ca7e9a35f35d1bf6813d83f01ccff psql -Upostgres
psql (9.6.11)
Type "help" for help.

postgres=# show timezone;
 TimeZone
----------
 UTC
(1 row)

New:

$ docker run -dit --rm postgres:9.6.12
18033871846f4997f074ea52044e4f9541b5a0389363515bdbea4e585b164078
$ docker exec -it 18033871846f4997f074ea52044e4f9541b5a0389363515bdbea4e585b164078 psql -Upostgres
psql (9.6.12)
Type "help" for help.

postgres=# show timezone;
 TimeZone
----------
 UCT
(1 row)

[wglambert]

Yeah something's amiss

Alpine

$ docker run -dit --rm postgres:9.6.12-alpine
Unable to find image 'postgres:9.6.12-alpine' locally
9.6.12-alpine: Pulling from library/postgres
bdf0201b3a05: Already exists 
365f27dc05d7: Pull complete 
bf541d40dfbc: Pull complete 
2042c4eafdd8: Pull complete 
925cd0367a3b: Pull complete 
47c141dded4c: Pull complete 
22d1e98847ee: Pull complete 
76225d26fe42: Pull complete 
da0efcbccec4: Pull complete 
Digest: sha256:a7fe157e12a492fae58d10fe102c437b9e71ae911592a5ca4022181b921f0262
Status: Downloaded newer image for postgres:9.6.12-alpine
a5d248f46ff4a65a2ebcf13ab3e66de244ca992c6ea003d5e8c9bba2a9acb784

$ docker exec -it a5d psql -Upostgres
psql (9.6.12)
Type "help" for help.

postgres=# show timezone;
 TimeZone 
----------
 UTC
(1 row)

Debian

$ docker run -dit --rm postgres:9.6.12
Unable to find image 'postgres:9.6.12' locally
9.6.12: Pulling from library/postgres
743f2d6c1f65: Pull complete 
5d307000f290: Pull complete 
29837b5e9b78: Pull complete 
3090df574038: Pull complete 
dc0b4463fa0e: Pull complete 
1fb834895f59: Pull complete 
59169bd605be: Pull complete 
a950d631bfe9: Pull complete 
16bdf8f0ef20: Pull complete 
617ee59ce7bb: Pull complete 
9d8f079549dc: Pull complete 
0b321bdf6269: Pull complete 
984c8750eb3d: Pull complete 
05f589943135: Pull complete 
Digest: sha256:696a4cd94282a8e8e878564cbc75641b30dab99aec0139db519c1188aeccc49c
Status: Downloaded newer image for postgres:9.6.12
10ce63ba0798e4a2268bbbc0e6d1f849c2c31314226068ec7b0a98f665b8271c

$ docker exec -it 10c psql -Upostgres
psql (9.6.12)
Type "help" for help.

postgres=# show timezone;
 TimeZone 
----------
 UCT
(1 row)

Looking at release notes there were some changes in this area, but this looks like a bug somewhere, even though UCT is (was?) a valid timezone equivalent to UTC it looks like it's obsolete
https://www.postgresql.org/docs/9.6/release-9-6-12.html

Update time zone data files to tzdata release 2018i for DST law changes in Kazakhstan, Metlakatla, and Sao Tome and Principe. Kazakhstan's Qyzylorda zone is split in two, creating a new zone Asia/Qostanay, as some areas did not change UTC offset. Historical corrections for Hong Kong and numerous Pacific islands.

[wglambert]

The workaround is to specify a timezone with TZ, the docker-entrypoint.sh will use that value when populating the databases (if your /var/lib/postgresql/data is empty)

$ docker run -dit --rm -e TZ=UTC --name postgres postgres:9.6.12
d7009f38552d2a7d1f8c278500efa66b810671ccdc850c0fe721a2989393b24e

$ docker exec -it postgres psql -U postgres
psql (9.6.12)
Type "help" for help.

postgres=# show timezone;
 TimeZone 
----------
 UTC
(1 row)

[aronatkins]

Thanks, @wglambert. We pinned to postgres:9.6.11 as a workaround rather than floating with postgres:9.6.

[tianon]

Looking at https://www.postgresql.org/about/news/1939/, it seems like this should've been fixed in 9.6.13, but it still exhibits: 😕

Etc/UCT is now a backward-compatibility link to Etc/UTC, instead of being a separate zone that generates the abbreviation UCT, which nowadays is typically a typo. PostgreSQL will still accept UCT as an input zone abbreviation, but it won't output it.

$ docker pull postgres:9.6.13
9.6.13: Pulling from library/postgres
Digest: sha256:97fcdcff5106e995661864bebf1fd6881553471b88e2afd6f98fbcb775bf66b7
Status: Downloaded newer image for postgres:9.6.13

$ docker run -dit --rm --name test postgres:9.6.13
11a18505dfe331ba7b6ac8645ceafbafaae8c24bf015a8f6677214181d61bcf6

$ docker exec -it test psql -U postgres
psql (9.6.13)
Type "help" for help.

postgres=# show timezone;
 TimeZone 
----------
 UCT
(1 row)

I was also able to reproduce installing https://packages.debian.org/buster/postgresql-11 directly in a stock Debian Buster container, so maybe filing a bug against that Debian package is the best way to get attention on this?

[elfgoh]

Seems to be happening on postgres:9.5-alpine (9.5.17) as well. Though I do not know if it didnt occur in earlier versions of 9.5.x

[kumarharsh]

This issue is still there in postgres 11.x. What is the reason for this random change?

[pwfff]

Chiming in to say this occurred with the 9.4 update as well, and broke some of our unit tests.

[tianon]

As alluded above, this is an upstream change and I was able to reproduce in the packages in Debian Unstable -- this is going to have to be filed further upstream.

[kumarharsh]

Filed with debian here: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=929953

I'm running Windows so can't supply debian-specific info. If anyone is running debian, please fill in with extra details on the bug report if you can.

[nocive]

Not so sure about being a debian specific problem, we observed this same incorrect behaviour on alpine linux.

[kumarharsh]

Alpine seemed fine as per this comment

[df7cb]

@kumarharsh: "further upstream" is PostgreSQL upstream, not Debian.

I can confirm that PG 11.3's initdb sets timezone to "UCT" even if /etc/timezone is Etc/UTC.

[df7cb]

https://www.postgresql.org/message-id/flat/20190604085735.GD24018%40msg.df7cb.de#1630deb668720886a97b8c7e12aa6a90

[kumarharsh]

Thanks @df7cb.

[dentarg]

I think the issue is the tzdata package, the new version 2019a has this change

https://mm.icann.org/pipermail/tz-announce/2019-March/000055.html

Changes to time zone abbreviations

Etc/UCT is now a backward-compatibility link to Etc/UTC, instead
of being a separate zone that generates the abbreviation "UCT",
which nowadays is typically a typo.  (Problem reported by Isiah
Meadows.)

postgres:9.6.11 has the older version:

root@2c0e04167d3c:/# dpkg -l | grep tz
ii  tzdata                     2018i-0+deb9u1             all          time zone and daylight-saving time data

postgres:9.6.12 has the new version:

root@b7a4b4f465ba:/# dpkg -l | grep tz
ii  tzdata                     2019a-0+deb9u1             all          time zone and daylight-saving time data

postgres:9.6.12-alpine has tzdata-2018i-r0

[df7cb]

Fixed in all branches: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e3846a00c2f87402dcedf7f07950ab2d89cf5827
Will be released on Thursday (out of schedule because there's a security fix :).

[wglambert]

Closing since this is resolved with the image updates on June 20

$ docker run -dit --rm --name postgres postgres:9.6.12-alpine
ddb2a704e85a9176da08e8c272c1d07de7283d4df805ed035029aa95c7126989

$ docker exec -it postgres psql -U postgres
psql (9.6.12)
Type "help" for help.

postgres=# show timezone;
 TimeZone 
----------
 UTC
(1 row)

Latest

$ docker run -dit --rm --name postgres postgres:latest
94e05c21f89a6a26b77a3a6911718e4757a87f89aabede8bb591228c2110f66d

$ docker exec -it postgres psql -U postgres
psql (11.4 (Debian 11.4-1.pgdg90+1))
Type "help" for help.

postgres=# show timezone;
 TimeZone 
----------
 Etc/UTC
(1 row)

[tianon]

Really appreciate you raising it upstream for us, @df7cb ! (And everything else you do that we benefit from 😅👍)