Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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).

share|improve this answer
    
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 Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.