PostgreSQL在不同时区转储和还原 [英] 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
我看到数据采用旧时间戳和时区格式： 2013-11-29 14：30：00 + 02
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.
然后我尝试将以下命令放在dump.sql的开头
Then I tried to put following command in the beginning of dump.sql
SET timezone ...
但仍然无法正常工作。 ：（
But still does not work. :(
这是一次操作。一旦传输的数据将不需要同步。是否可以使用pg_dump和pg_restore或类似方法进行这种转换？
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?
推荐答案
Postgres数据类型 timstamptz （= 带有时区的时间戳）存储值作为内部的UTC时间戳（自2000年以来的整数值，以微秒为单位），与显示 的时区无关。 不 存储一些可能会误认为名称的时区信息，您可以随意存储和恢复。
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.
您 看到的内容 在您的客户端中取决于时区设置
运行（在同一会话中，以避免出现伪像）：
Run (in the same session to avoid artifacts):
SHOW timezone;
如果您看到 localtime ，则Postgres使用
If you see localtime then Postgres uses the default setting of your server OS.
设置其他时区（在会话中使用 SET timezone = ...; ，以便您的用户或在配置文件中全局查看）以不同的格式查看您的时间戳。请注意，在这种情况下，转储文件中的时区设置无效无效，仅当前会话的设置有效。
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"
这篇关于PostgreSQL在不同时区转储和还原的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
