PostgreSQL won’t take new timezone

postgresqltimezone

In Venezuela a timezone change have been undertaken on May 1st 2016. The old offset for the Venezuela timezone was -04:30 and the new one is -04:00.

All database servers have been updated with the new timezones, when I run this command at the Linux prompt I get the expected output:

$ date +'%:z %Z'
-04:00 VET

Most PostgreSQL clusters happily took the new timezone after bouncing them:

Good behaved cluster:

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

postgres=# select now();
      now
-------------------------------
2016-05-02 10:08:54.532902-04
(1 row) 

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
utc_offset
------------
-04:00:00
(1 row)

In the case of two particular database servers, the clusters refuse to take the new timezone offset no matter how many times we bounce the cluster.

Ill-behaved cluster 1:

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
utc_offset
------------
-04:30:00
(1 row)

postgres=# select now();
       now
----------------------------------
2016-05-02 09:39:03.678385-04:30
(1 row) 

postgres=# show timezone;
TimeZone
-----------------
America/Caracas
(1 row)

The servers who took the change as well as the ones which didn't, all have the default value for the timezone parameter in postgresql.conf.

I need advice on how to solve this, since all other clusters in other servers took the change without a hassle.

  • What else can I check?
  • What am I missing?

One of the clusters that failed to take the new timezone is 8.2 and the other is 9.3,

To confuse it even more, one of the ill-behaving clusters (the 9.3 one) has the correct offset from the pg_timezone_names view but returns the time from the old offset:

Ill-behaved cluster 2:

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
 utc_offset 
------------
 -04:00:00
(1 fila)

postgres=# select current_time;
        timetz         
-----------------------
 10:20:16.398867-04:30
(1 fila)

postgres=# show timezone;
 TimeZone  
-----------
 localtime
(1 fila)

I'm totally lost here. Any light on this issued would be most appreciated.

Best Answer

PostgreSQL can be built to either use the operating system's time zone database or its own. Check with the command pg_config --configure whether the option --with-system-tzdata was used. If not, it is using its own time zone data, and that hasn't been updated yet. If so, to fix, either wait for the next releases, or install the packages the way you have installed it on the other hosts.

Related Question