18

I have several PostgreSQL 9.2 installations where the timezone used by PostgreSQL is GMT, despite the entire system being "Europe/Vienna". I double-checked that postgresql.conf does not contain timezone setting, so according to the documentation it should fallback to the system's timezone.

However,

# su -s /bin/bash postgres -c "psql mydb"

mydb=# show timezone;
 TimeZone 
----------
 GMT
(1 row)

mydb=# select now();
              now              
-------------------------------
 2013-11-12 08:14:21.697622+00
(1 row)

Any hints, where the GMT timezone could come from? The system user does not have TZ set and the /etc/timezone and /etc/timeinfo seem to be configured correctly.

# cat /etc/timezone 
Europe/Vienna
# date
Tue Nov 12 09:15:42 CET 2013

Any hints are appreciated, thanks in advance!

Martin C.
  • 670
  • 1
  • 6
  • 12

2 Answers2

25

The default value for the TimeZone setting has changed on release 9.2:

(..) If not explicitly set, the server initializes this variable to the time zone specified by its system environment. (...)

(...) The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. (...)

Which means that prior to version 9.2 the default value at postgresql.conf should be set during initdb phase. If you overridden that value (probably copying the old postgresql.conf while upgrading from older versions) PostgreSQL will use the "GMT" value as default.

The solution for your case is quite simple, just change the TimeZone setting on postgresql.conf to the value you want:

TimeZone = 'Europe/Vienna'

After that you need to reload the service:

# su - postgres -c "psql mydb -c 'SELECT pg_reload_conf()'"

Then all fields stored as timestamp with time zone (or timestamptz) will be shown correctly from now on. But you will have to correct by hand all (update) the fields stored as timestamp without time zone (or timestamp).

A tip I give to everyone upgrading PostgreSQL is not to copy the old postgresql.conf to the new cluster (notice I'm not sure if it what you did, but I saw this very same problem a lot because of that). Just get the one generated by initdb and add the modifications (a diff tool may be handful to this task).

MatheusOl
  • 402
  • 4
  • 6
  • Thanks a lot, I did not notice this change from 9.1 to 9.2. Yes, adding the timezone info to postgresql.conf is a trivial fix, I just couldn't explain why it would fall back to GMT. Apparently I just stumbled upon the 9.1 documentation all the time, as I wouldn't have expected such a radical change from 9.1 to 9.2 in the default behavior. – Martin C. Nov 12 '13 at 10:51
  • Your database should always be in UTC (GMT) though. Makes times easier to compare. Can always change timezone of client/session. Set timezone x in pg .http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices – Neil McGuigan Jul 04 '15 at 03:50
  • Is there any way to specify in `postgresql.conf` for version 9.2+ that the system environment timezone should still be automatically detected? – Kyle Strand Sep 19 '17 at 21:40
0

I found a workaround for this.

just create a symlink inside /usr/share/zoneinfo/ named localtime (or whatever name you wish) to be pointing at /etc/localtime

/usr/share/zoneinfo/localtime -> /etc/localtime

this way you're creating a string of links that ultimately points at your system's timezone.

/etc/localtime -> /usr/share/zoneinfo/America/Los_Angeles

Now take the name of the link you created (localtime in my case) and use it as the value of the configuration item in postgresql.conf

TimeZone = 'localtime'

restart postgresql and check the time with "SELECT now();" and "show timezone;"

MatteoBee
  • 116
  • 4