Thread: timezone change after upgrade from postgres 9.1 to 9.3?

timezone change after upgrade from postgres 9.1 to 9.3?

From
"Anand Kumar, Karthik"
Date:
Hi,

We just upgraded our postgres database from 9.1 to 9.3. And noticed that the timezone changed from PST to GMT.
Is that known behavior? Has anyone else run into it, or am I just missing something?

I've verified the server's timezone is right, and nothing in the postgres user's profile is changing the timezone at startup. The postgres start up scripts aren't setting the timezone either, from what I can see.

Thanks,
Karthik

Re: timezone change after upgrade from postgres 9.1 to 9.3?

From
Adrian Klaver
Date:
On 01/30/2014 03:17 PM, Anand Kumar, Karthik wrote:
> Hi,
>
> We just upgraded our postgres database from 9.1 to 9.3. And noticed that
> the timezone changed from PST to GMT.
> Is that known behavior? Has anyone else run into it, or am I just
> missing something?


Well there where changes in the way timezones are set in 9.2.

http://www.postgresql.org/docs/9.3/interactive/release-9-2.html



Identify the server time zone during initdb, and set postgresql.conf
entries timezone and log_timezone accordingly (Tom Lane)

This avoids expensive time zone probes during server start.


>
> I've verified the server's timezone is right, and nothing in the
> postgres user's profile is changing the timezone at startup. The
> postgres start up scripts aren't setting the timezone either, from what
> I can see.

What is the server timezone?

What is the timezone value in postgresql.conf?

Where are you seeing the change?

>
> Thanks,
> Karthik


--
Adrian Klaver
adrian.klaver@gmail.com


Re: timezone change after upgrade from postgres 9.1 to 9.3?

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 01/30/2014 03:17 PM, Anand Kumar, Karthik wrote:
>> We just upgraded our postgres database from 9.1 to 9.3. And noticed that
>> the timezone changed from PST to GMT.
>> Is that known behavior? Has anyone else run into it, or am I just
>> missing something?

> Well there where changes in the way timezones are set in 9.2.

Yeah.  Likely theories include

1. You ran initdb in a different environment than you normally start the
server in.

2. You blindly copied the 9.1 postgresql.conf into the 9.3 installation,
overwriting what initdb had done.  If there's no uncommented timezone
setting in the .conf file, 9.3 will fall back to GMT, IIRC.  But that
approach to configuration has a lot of pitfalls besides this one.

In any case the fix is to set the zone you want in postgresql.conf.

            regards, tom lane


Re: timezone change after upgrade from postgres 9.1 to 9.3?

From
"Anand Kumar, Karthik"
Date:
Thank you.

Its a 'yes' on 2 there. I did copy the config file from the 9.1
installation. Thank you for mentioning that, will fix it.

Thanks,
Karthik




On 1/30/14 4:17 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>Adrian Klaver <adrian.klaver@gmail.com> writes:
>> On 01/30/2014 03:17 PM, Anand Kumar, Karthik wrote:
>>> We just upgraded our postgres database from 9.1 to 9.3. And noticed
>>>that
>>> the timezone changed from PST to GMT.
>>> Is that known behavior? Has anyone else run into it, or am I just
>>> missing something?
>
>> Well there where changes in the way timezones are set in 9.2.
>
>Yeah.  Likely theories include
>
>1. You ran initdb in a different environment than you normally start the
>server in.
>
>2. You blindly copied the 9.1 postgresql.conf into the 9.3 installation,
>overwriting what initdb had done.  If there's no uncommented timezone
>setting in the .conf file, 9.3 will fall back to GMT, IIRC.  But that
>approach to configuration has a lot of pitfalls besides this one.
>
>In any case the fix is to set the zone you want in postgresql.conf.
>
>            regards, tom lane