Thread: setting timezone
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
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
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
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
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
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