PostgreSQL won't take new timezone

Question

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.

• Matt Johnson-Pint: Does this help?

Answer 1

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.

• Tulains Córdova: We have a two-nodes replication setup, both hardwares got the timezone right, but in node 0 Postgres returns current_time with the old TZ whereas in node 1 it returns current_time using the correct TZ from the OS. Both have '--with-system-tzdata=/usr/share/zoneinfo'.