[ADMIN] setting timezone

[Geoffrey Myers]

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

[Steve Crawford]

Post by Geoffrey Myers
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

[Tom Lane]

Post by Steve Crawford

Post by Geoffrey Myers
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]

Post by Tom Lane

Post by Steve Crawford

Post by Geoffrey Myers
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

[Geoffrey Myers]

Post by Steve Crawford

Post by Tom Lane

Post by Steve Crawford

Post by Geoffrey Myers
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.

Post by Steve Crawford
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

[Geoffrey Myers]

Post by Steve Crawford

Post by Geoffrey Myers
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.

Post by Steve Crawford
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