pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUrgent PST time changing tonight
11+ messages / 4 participants
[nested] [flat]
* Urgent PST time changing tonight
@ 2009-03-08 07:58 DM <dm.aeqa@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: DM @ 2009-03-08 07:58 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-08 08:23 Scott Marlowe <scott.marlowe@gmail.com>
parent: DM <dm.aeqa@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2009-03-08 08:23 UTC (permalink / raw)
To: DM <dm.aeqa@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-08 08:30 DM <dm.aeqa@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: DM @ 2009-03-08 08:30 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-08 08:35 Scott Marlowe <scott.marlowe@gmail.com>
parent: DM <dm.aeqa@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2009-03-08 08:35 UTC (permalink / raw)
To: DM <dm.aeqa@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-08 08:53 DM <dm.aeqa@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: DM @ 2009-03-08 08:53 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-08 09:29 Scott Marlowe <scott.marlowe@gmail.com>
parent: DM <dm.aeqa@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2009-03-08 09:29 UTC (permalink / raw)
To: DM <dm.aeqa@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-09 19:14 Steve Crawford <scrawford@pinpointresearch.com>
parent: DM <dm.aeqa@gmail.com>
1 sibling, 3 replies; 11+ messages in thread
From: Steve Crawford @ 2009-03-09 19:14 UTC (permalink / raw)
To: DM <dm.aeqa@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-09 19:18 Scott Marlowe <scott.marlowe@gmail.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2009-03-09 19:18 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: DM <dm.aeqa@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-10 05:11 DM <dm.aeqa@gmail.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 11+ messages in thread
From: DM @ 2009-03-10 05:11 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-10 09:37 Scott Marlowe <scott.marlowe@gmail.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2009-03-10 09:37 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: DM <dm.aeqa@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Urgent PST time changing tonight
@ 2009-03-10 12:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2009-03-10 12:31 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; DM <dm.aeqa@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2009-03-10 12:31 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-08 07:58 Urgent PST time changing tonight DM <dm.aeqa@gmail.com>
2009-03-08 08:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-03-08 08:30 ` DM <dm.aeqa@gmail.com>
2009-03-08 08:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-03-08 08:53 ` DM <dm.aeqa@gmail.com>
2009-03-08 09:29 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-03-09 19:14 ` Steve Crawford <scrawford@pinpointresearch.com>
2009-03-09 19:18 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-03-10 05:11 ` DM <dm.aeqa@gmail.com>
2009-03-10 09:37 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-03-10 12:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox