不同时区的 PostgreSQL 转储和恢复

Question

我有两个数据库在不同时区的不同服务器上运行。有几个表包含 timestamp with timezone。

我需要从一个数据库转储数据并将其导入到另一个具有正确时间戳和正确时区的数据库。

我使用以下命令转储数据:

pg_dump -a DB_NAME > dump.sql

我看到数据采用旧的时间戳和时区格式:2013-11-29 14:30:00+02

然后我使用命令到另一台服务器来恢复转储:

psql -d DB_NAME -f dump.sql

我看到时间戳和时区来自旧服务器 - 我认为这是正常的。

然后我尝试在 dump.sql 的开头放置以下命令

SET timezone ...

但是还是不行。 :(

这是一次性操作。一旦传输数据将不需要同步。有没有办法使用 pg_dump 和 pg_restore 或类似的方法进行这种转换？

Answer 1

Postgres 的数据类型timstamptz ( = timestamp with time zone) 在内部将值存储为 UTC 时间戳(自 2000 年以来计算微秒的整数值)，这与这些值显示的时区无关。它确实不存储任何时区信息，就像某些人可能认为的那样，误判了名称。您可以根据需要转储和恢复。

您在客户端中看到的内容取决于 time zone setting session 的。

运行(在同一 session 中以避免伪影):

SHOW timezone;

如果您看到 localtime，则 Postgres 使用服务器操作系统的默认设置。

设置不同的时区(在您使用 SET timezone = ...; 的 session 中，为您的用户或在配置文件中全局设置)以查看不同格式的时间戳。请注意，转储文件中的时区设置在这种情况下不会有任何影响，只有当前 session 的设置有影响。

这个相关回答中的详细解释:

• Ignoring timezones altogether in Rails and PostgreSQL

关于设置环境变量的各种方式:

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