pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsetting timezone
6+ messages / 3 participants
[nested] [flat]
* setting timezone
@ 2011-11-09 13:10 Geoffrey Myers <lists@serioustechnology.com>
0 siblings, 1 reply; 6+ messages in thread
From: Geoffrey Myers @ 2011-11-09 13:10 UTC (permalink / raw)
To: pgsql-admin
We are moving a number of databases to new hardware. Some of these
machines have the timezone set in the database differently then the
actual location of the machine as they are access from a different
timezone. We were surprised to note that when we dump the database and
reloaded it on the new machine, it did not retain the timezone setting.
Is there a way to retain this information from the original database
when reloading?
Thanks.
--
Until later, Geoffrey
"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: setting timezone
@ 2011-11-09 15:29 Steve Crawford <scrawford@pinpointresearch.com>
parent: Geoffrey Myers <lists@serioustechnology.com>
0 siblings, 2 replies; 6+ messages in thread
From: Steve Crawford @ 2011-11-09 15:29 UTC (permalink / raw)
To: Geoffrey Myers <lists@serioustechnology.com>; +Cc: pgsql-admin
On 11/09/2011 05:10 AM, Geoffrey Myers wrote:
> We are moving a number of databases to new hardware. Some of these
> machines have the timezone set in the database differently then the
> actual location of the machine as they are access from a different
> timezone. We were surprised to note that when we dump the database
> and reloaded it on the new machine, it did not retain the timezone
> setting.
>
> Is there a way to retain this information from the original database
> when reloading?
>
> Thanks.
>
Time-zone setting is an attribute of the server configuration, not of
the data contained in the database. Unless overidden by the timezone
setting in postgresql.conf, PostgreSQL will use the server's time zone
environment setting. You can specify a default time-zone for PostgreSQL
independent of the server's environment by setting "timezone" explicitly
in postgresql.conf.
Clients can override the default server setting with "SET TIMEZONE TO
...;" which is useful when a single server is supporting users across
many time zones.
Cheers,
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: setting timezone
@ 2011-11-09 16:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-11-09 16:47 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Geoffrey Myers <lists@serioustechnology.com>; pgsql-admin
Steve Crawford <scrawford@pinpointresearch.com> writes:
> On 11/09/2011 05:10 AM, Geoffrey Myers wrote:
>> Is there a way to retain this information from the original database
>> when reloading?
> Time-zone setting is an attribute of the server configuration, not of
> the data contained in the database.
In particular, pg_dump doesn't know anything about postgresql.conf.
It's up to you to copy the server's configuration files.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: setting timezone
@ 2011-11-09 19:23 Geoffrey Myers <lists@serioustechnology.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 6+ messages in thread
From: Geoffrey Myers @ 2011-11-09 19:23 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Geoffrey Myers <lists@serioustechnology.com>; pgsql-admin
Steve Crawford wrote:
> On 11/09/2011 05:10 AM, Geoffrey Myers wrote:
>> We are moving a number of databases to new hardware. Some of these
>> machines have the timezone set in the database differently then the
>> actual location of the machine as they are access from a different
>> timezone. We were surprised to note that when we dump the database
>> and reloaded it on the new machine, it did not retain the timezone
>> setting.
>>
>> Is there a way to retain this information from the original database
>> when reloading?
>>
>> Thanks.
>>
>
> Time-zone setting is an attribute of the server configuration, not of
> the data contained in the database. Unless overidden by the timezone
> setting in postgresql.conf, PostgreSQL will use the server's time zone
> environment setting. You can specify a default time-zone for PostgreSQL
> independent of the server's environment by setting "timezone" explicitly
> in postgresql.conf.
>
> Clients can override the default server setting with "SET TIMEZONE TO
> ...;" which is useful when a single server is supporting users across
> many time zones.
Thanks, that's just what I needed.
>
> Cheers,
> Steve
>
>
--
Until later, Geoffrey
"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: setting timezone
@ 2011-11-09 19:52 Steve Crawford <scrawford@pinpointresearch.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Steve Crawford @ 2011-11-09 19:52 UTC (permalink / raw)
To: Geoffrey Myers <lists@serioustechnology.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On 11/09/2011 11:41 AM, Geoffrey Myers wrote:
> Geoffrey Myers wrote:
>> Tom Lane wrote:
>>> Steve Crawford <scrawford@pinpointresearch.com> writes:
>>>> On 11/09/2011 05:10 AM, Geoffrey Myers wrote:
>>>>> Is there a way to retain this information from the original
>>>>> database when reloading?
>>>
>>>> Time-zone setting is an attribute of the server configuration, not
>>>> of the data contained in the database.
>>>
>>> In particular, pg_dump doesn't know anything about postgresql.conf.
>>> It's up to you to copy the server's configuration files.
>>>
>>> regards, tom lane
>>
>> Thanks Tom, seems we were not setting it in the postgresql.conf file.
>
> What about the situation where you have two databases in the same
> cluster that need different timezones? I'm assuming I would have to
> rely on setting it in each database, rather in the postgresql.conf file.
>
>>
>
ALTER DATABASE foo SET TIMEZONE...
Cheers,
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: setting timezone
@ 2011-11-10 16:18 Geoffrey Myers <lists@serioustechnology.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 6+ messages in thread
From: Geoffrey Myers @ 2011-11-10 16:18 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
Steve Crawford wrote:
> On 11/09/2011 11:41 AM, Geoffrey Myers wrote:
>> Geoffrey Myers wrote:
>>> Tom Lane wrote:
>>>> Steve Crawford <scrawford@pinpointresearch.com> writes:
>>>>> On 11/09/2011 05:10 AM, Geoffrey Myers wrote:
>>>>>> Is there a way to retain this information from the original
>>>>>> database when reloading?
>>>>
>>>>> Time-zone setting is an attribute of the server configuration, not
>>>>> of the data contained in the database.
>>>>
>>>> In particular, pg_dump doesn't know anything about postgresql.conf.
>>>> It's up to you to copy the server's configuration files.
>>>>
>>>> regards, tom lane
>>>
>>> Thanks Tom, seems we were not setting it in the postgresql.conf file.
>>
>> What about the situation where you have two databases in the same
>> cluster that need different timezones? I'm assuming I would have to
>> rely on setting it in each database, rather in the postgresql.conf file.
>>
>>>
>>
>
> ALTER DATABASE foo SET TIMEZONE...
As I expected, thanks.
>
> Cheers,
> Steve
>
>
>
--
Until later, Geoffrey
"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-11-10 16:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-09 13:10 setting timezone Geoffrey Myers <lists@serioustechnology.com>
2011-11-09 15:29 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-11-09 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-09 19:52 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-11-10 16:18 ` Geoffrey Myers <lists@serioustechnology.com>
2011-11-09 19:23 ` Geoffrey Myers <lists@serioustechnology.com>
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