Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 351
Radnev Avatar asked Nov 28 '13 15:11

Radnev


People also ask

How do I restore a dump file in PostgreSQL?

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.

Does Postgres use UTC?

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.


1 Answers

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"
like image 50
Erwin Brandstetter Avatar answered Sep 21 '22 16:09

Erwin Brandstetter