PostgreSQL dump and restore in different timezone

问题:

I have two databases running on different servers in different timezones. There is a couple of tables which contains timestamp with timezone.

I need to dump data from one database and to import it to another with correct timestamp and correct timezone.

I use following command to dump data:

pg_dump -a DB_NAME > dump.sql

I see data are in old timestamp and timezone format: 2013-11-29 14:30:00+02

Then I use command to another server to restore dump:

psql -d DB_NAME -f dump.sql

And I see that timestamps and timezone is from old server - which I think is normal.

Then I tried to put following command in the beginning of dump.sql

SET timezone ...

But still does not work. :(

This is one time operation. Once transferred data will not need sync. Is there a way to do such conversion using pg_dump and pg_restore or similar?

回答1:

The Postgres the data type timstamptz ( = timestamp with time zone) stores values as UTC timestamp internally (integer value counting microseconds since 2000), which is independent of the time zone those values are displayed for. It does not store any time zone information like some might think, misjudging the name. You can dump and restore as much as you want.

What you see in your client depends on the time zone setting of the session.

Run (in the same session to avoid artifacts):

SHOW timezone;

If you see localtime then Postgres uses the default setting of your server OS.

Set a different time zone (in your session with SET timezone = ...;, for your user or globally in the config files) to see your timestamps formatted differently. Be aware that the time zone setting in your dump file does not have any effect in this case, only the setting of your current session has.

Detailed explanation in this related answer:

• Ignoring timezones altogether in Rails and PostgreSQL

About the various ways to set environment variables:

• How does the search_path influence identifier resolution and the "current schema"