Thread: Urgent PST time changing tonight

Urgent PST time changing tonight

From
DM
Date:
Hi All,

PST time changing tonight, I am not sure if this is going to affect the database. 
Please throw some light if the time change affect on our production system.

Briefly - We have 2 production database servers with postgresql 8.3.3 and pgpool 2.1 version running.

Here is the configuration details about time zone.

#log_timezone = unknown # actually, defaults to TZ environment setting
datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
# setting
#timezone_abbreviations = 'Default'     # Select the set of available time zone
# abbreviations.  Currently, there are
#   Default
#   Australia
#   India
# You can create your own file in
# share/timezonesets/.

log_timezone, timezone and timezone_abbreviations are set to default.

Thanks
Deepak Murthy

Re: Urgent PST time changing tonight

From
Scott Marlowe
Date:
On Sun, Mar 8, 2009 at 12:58 AM, DM <dm.aeqa@gmail.com> wrote:
> Hi All,
> PST time changing tonight, I am not sure if this is going to affect the
> database.
> Please throw some light if the time change affect on our production system.
> Briefly - We have 2 production database servers with postgresql 8.3.3 and
> pgpool 2.1 version running.

pg includes its own tz data files, so running 8.3.3 might be some
issue if you handle odd timezones, but generally the dst offset change
is no big deal.  all the timestamps are stored internally in gmt.

Re: Urgent PST time changing tonight

From
DM
Date:
Thanks Scott for the quick response, do I need to do anything at my end to solve this issue.

People access our servers from Pacific, Central, Mountain and East Time zones.

Please let me know if i have to do any settings/configuration changes to the configuration files.

thanks
Deepak Murthy
On Sun, Mar 8, 2009 at 12:23 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Sun, Mar 8, 2009 at 12:58 AM, DM <dm.aeqa@gmail.com> wrote:
> Hi All,
> PST time changing tonight, I am not sure if this is going to affect the
> database.
> Please throw some light if the time change affect on our production system.
> Briefly - We have 2 production database servers with postgresql 8.3.3 and
> pgpool 2.1 version running.

pg includes its own tz data files, so running 8.3.3 might be some
issue if you handle odd timezones, but generally the dst offset change
is no big deal.  all the timestamps are stored internally in gmt.

Re: Urgent PST time changing tonight

From
Scott Marlowe
Date:
On Sun, Mar 8, 2009 at 1:30 AM, DM <dm.aeqa@gmail.com> wrote:
> Thanks Scott for the quick response, do I need to do anything at my end to
> solve this issue.
>
> People access our servers from Pacific, Central, Mountain and East Time
> zones.
> Please let me know if i have to do any settings/configuration changes to the
> configuration files.
> thanks
> Deepak Murthy
> On Sun, Mar 8, 2009 at 12:23 AM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:

I don't think there's been any serious tz changes since 8.3.3, but it
would be a good idea to schedule a few minutes of downtime to update
to 8.3.6, the latest version of 8.3.

Re: Urgent PST time changing tonight

From
DM
Date:
Thanks Scott,

If i leave the system as is without any upgrade, what issues i might see any idea?

do postgres has any patches for the changes. i dont want to install a new databae at this time, i have 1 hr to go..

Thanks
Deepak Murthy

On Sun, Mar 8, 2009 at 12:35 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Sun, Mar 8, 2009 at 1:30 AM, DM <dm.aeqa@gmail.com> wrote:
> Thanks Scott for the quick response, do I need to do anything at my end to
> solve this issue.
>
> People access our servers from Pacific, Central, Mountain and East Time
> zones.
> Please let me know if i have to do any settings/configuration changes to the
> configuration files.
> thanks
> Deepak Murthy
> On Sun, Mar 8, 2009 at 12:23 AM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:

I don't think there's been any serious tz changes since 8.3.3, but it
would be a good idea to schedule a few minutes of downtime to update
to 8.3.6, the latest version of 8.3.

Re: Urgent PST time changing tonight

From
Scott Marlowe
Date:
On Sun, Mar 8, 2009 at 1:53 AM, DM <dm.aeqa@gmail.com> wrote:
> Thanks Scott,
> If i leave the system as is without any upgrade, what issues i might see any
> idea?
> do postgres has any patches for the changes. i dont want to install a new
> databae at this time, i have 1 hr to go..
> Thanks

Assuming that some update to DST got missed, then you're output will
all be off by an hour from what they should be when retrieved for that
timezone that has it wrong.  I remember there being something a while
back about moving one of the timezones a few days.  You can always
create and retrieve timestamps ahead of time (i.e. a timestamp in the
future) and see what offset it has to see if it's coming out right.

The real problem gets created when timestamps are inserted now for the
future.  If there's a rule that we fall back 1 week off from what your
TZ data says, and you insert it today, with the old TZ on your
machine, it will be inserted as the wrong time, with the wrong offset
to get it to GMT.  This cropped up a year ago with some change our
windows machines at work didn't have, and all the schedules created by
those machines were off by an hour until the newly created events
(created with the right timezone offset values) started showing up and
we got past the window where time actually did match up again.

So, leaving these to stew can be a real problem, if you're not keeping
up with the tz info.

Re: Urgent PST time changing tonight

From
Steve Crawford
Date:
DM wrote:
> Hi All,
>
> PST time changing tonight, I am not sure if this is going to affect
> the database....
IF you are up-to-date on your TZ files the database itself should be
fine. But external processing is another matter - especially if you have
processes that assume conventions from previous PG versions. For example:

PG 7.4 (one day = 24 hours):
select now();

              now
-------------------------------
 2009-03-09 12:05:42.533139-07

 select now()-'2 days'::interval;
           ?column?
-------------------------------
 2009-03-07 11:05:43.963526-08


But in 8.3 (day interval is DST aware):
select now();

              now
-------------------------------
 2009-03-09 12:04:21.813654-07

select now()-'2 days'::interval;
           ?column?
-------------------------------
 2009-03-07 12:04:47.693384-08

That's just in the database itself and without cross-country processing
(i.e.: if you have somewhat synchronized processes that assume that New
York is 3 hours ahead of San Francisco you can have problems if you
don't consider that New York changed to DST 3 hours ahead of San Francisco).

Then there are the scripts that calculate dates externally for feeding
into your database-backed processes (for fun, try "-d yesterday" and "-d
tomorrow" in most versions of the "date" command in the vicinity of DST
changes).

Cheers,
Steve


Re: Urgent PST time changing tonight

From
Scott Marlowe
Date:
On Mon, Mar 9, 2009 at 1:14 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> Then there are the scripts that calculate dates externally for feeding into
> your database-backed processes (for fun, try "-d yesterday" and "-d
> tomorrow" in most versions of the "date" command in the vicinity of DST
> changes).

I ran into an issue with this a while back.  We were running fairly
modern distros at work, but older ones in production, and a script I'd
written and tested on my workstation failed miserably if you ran it
between midnight and 2/3am the sunday of the time shift on the
production machines.

Re: Urgent PST time changing tonight

From
DM
Date:
Thanks Steve for sharing this info.

Thanks
Deepak

On Mon, Mar 9, 2009 at 12:14 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
DM wrote:
Hi All,

PST time changing tonight, I am not sure if this is going to affect the database....
IF you are up-to-date on your TZ files the database itself should be fine. But external processing is another matter - especially if you have processes that assume conventions from previous PG versions. For example:

PG 7.4 (one day = 24 hours):
select now();

            now
-------------------------------
2009-03-09 12:05:42.533139-07

select now()-'2 days'::interval;
         ?column?
-------------------------------
2009-03-07 11:05:43.963526-08


But in 8.3 (day interval is DST aware):
select now();

            now
-------------------------------
2009-03-09 12:04:21.813654-07

select now()-'2 days'::interval;
         ?column?
-------------------------------
2009-03-07 12:04:47.693384-08

That's just in the database itself and without cross-country processing (i.e.: if you have somewhat synchronized processes that assume that New York is 3 hours ahead of San Francisco you can have problems if you don't consider that New York changed to DST 3 hours ahead of San Francisco).

Then there are the scripts that calculate dates externally for feeding into your database-backed processes (for fun, try "-d yesterday" and "-d tomorrow" in most versions of the "date" command in the vicinity of DST changes).

Cheers,
Steve


Re: Urgent PST time changing tonight

From
Scott Marlowe
Date:
On Mon, Mar 9, 2009 at 1:14 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> IF you are up-to-date on your TZ files the database itself should be fine.
> But external processing is another matter - especially if you have processes
> that assume conventions from previous PG versions. For example:

Note that pgsql uses its own internal TZ data.  So your OS being up to
date means little if your database is not.

Re: Urgent PST time changing tonight

From
Tom Lane
Date:
Scott Marlowe <scott.marlowe@gmail.com> writes:
> Note that pgsql uses its own internal TZ data.  So your OS being up to
> date means little if your database is not.

Well, it depends.  I think practically all distro-supplied versions
are using --with-system-tzdata (or equivalent patches in older PG
releases) so that they don't need to turn PG when updating the TZ
database.  You really only want to use the built-in tzdata if you're
on a system that doesn't receive software updates in some fairly
automated manner.

But the whole discussion only applies to people who live in places
where the DST laws change without reasonable advance notice.

            regards, tom lane