postgresql incorrect timezone change from summer time

Question

I'm running system on CentOS 6.x with Postgresql installed:

PostgreSQL 9.0.11 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit

The problem I faced now looks like folowing:

PostgreSQL configuration:

\#timezone = unknown      # actually, defaults to TZ environment

Session configuration from psql:

select setting 
from pg_settings 
where name in ('TimeZone', 'timezone_abbreviations');

Israel
Default

----
- OS timezone is Israel:

zdump -v /etc/localtime |grep 2013

/etc/localtime  Thu Mar 28 23:59:59 2013 UTC = Fri Mar 29 01:59:59 2013 IST isdst=0 gmtoff=7200
/etc/localtime  Fri Mar 29 00:00:00 2013 UTC = Fri Mar 29 03:00:00 2013 IDT isdst=1 gmtoff=10800
/etc/localtime  Sat Oct 26 22:59:59 2013 UTC = Sun Oct 27 01:59:59 2013 IDT isdst=1 gmtoff=10800
/etc/localtime  Sat Oct 26 23:00:00 2013 UTC = Sun Oct 27 01:00:00 2013 IST isdst=0 gmtoff=7200

----

Now - the shift from summer timezone as per zdump should be done at Sun Oct 27 01:00:00. When I run

select '2013-09-06 00:00:00'::timestamptz it shows date in +03 timezone - 2013-09-06 00:00:00+03

But when I run

select '2013-09-08 00:00:00'::timestamptz it shows date in +02 timezone - 2013-09-06 00:00:00+02 but it should show it in +03 as timezone shift will be only at for 2013-09-06 00:00:00'::timestamptz Sun Oct 27 01:00:00.

So there are 2 questions:

1. How postgresql keep the date of timezone shift and how it's possible to check the current value.
2. How can I fix this problem.

Answer 1

The Postgres sources contain a copy of the TZDB in /src/timezone/data. It is updated periodically as new releases of Postgres come out.

According to the revision history, the latest version at the time of this writing is 2013d. This is indeed the version that contains the changes for Israel that you are looking for. See the 2013d release announcement. So, if you can update your Postgres version, you should see the changes you're looking for.

Looking through the release dates, it would appear that this data should be in the any of the following versions:

9.3.1
9.3
9.2.5
9.1.10
9.0.14
8.4.18

You said you were running 9.0.11, which has a release date of 2012-12-06, so you are currently using TZDB version 2012j. That explains the discrepancy in the timestamps that you described. You should update Postgres to at least version 9.0.14.

But since you are running on a Linux system with it's own copy of the tzdata, you could take a different approach and tell Postgres to use that data instead of it's own. You have to supply a parameter to configure when building postgres from sources.

From the documentation here:

--with-system-tzdata=DIRECTORY

PostgreSQL includes its own time zone database, which it requires for date and time operations. This time zone database is in fact compatible with the "zoneinfo" time zone database provided by many operating systems such as FreeBSD, Linux, and Solaris, so it would be redundant to install it again. When this option is used, the system-supplied time zone database in DIRECTORY is used instead of the one included in the PostgreSQL source distribution. DIRECTORY must be specified as an absolute path. /usr/share/zoneinfo is a likely directory on some operating systems. Note that the installation routine will not detect mismatching or erroneous time zone data. If you use this option, you are advised to run the regression tests to verify that the time zone data you have pointed to works correctly with PostgreSQL.

This option is mainly aimed at binary package distributors who know their target operating system well. The main advantage of using this option is that the PostgreSQL package won't need to be upgraded whenever any of the many local daylight-saving time rules change. Another advantage is that PostgreSQL can be cross-compiled more straightforwardly if the time zone database files do not need to be built during the installation.