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?
PostgreSQL Dump Import process can be carried out using psql command. You can use the psql utility to recover the SQL script file created by pg_dump, pg_dumpall, or any other tool that creates compatible backup files. You can run the complete script in the dump file with the psql tool.
PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
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:
About the various ways to set environment variables:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With