Re: [GENERAL] recovery dump on database with different timezone

On 01/23/2017 03:45 PM, Edmundo Robles wrote:
> Hi!
>
> I have  backed up  a database with a 'UTC' timezone, but i can not  to
> restore it on a database with 'localtime'  timezone.

Dealing with time is complex, so some inline questions below to try to
figure out exactly what the setup is.

>
>  I have a time constraints, the reason of the mistakes.
> * First, I did:  'alter database  set timezone='UTC'; '    but no
> restore it...

On the database you took the backup from or the one you are restoring to?

> * After  I  did change  'timezone' to UTC in postgresql.conf  and
> restart  , but  :(

 From the source(backup) Postgres instance or the destination(restore)
instance?

> * at last, i did 'pg_restore -l backup.dmp| sed "s:+00:-06:g"|psql

The above is not the complete command, correct?

What is the complete command?

> database  but copy fails with some records, so some tables are  empty :(
> * i will try  replacing +00 by  empty string, but ...

In psql and in the receiving database, what does:

show timezone;

return?

Are the timestamp fields you are COPYing into declared with timezone or
without timezone?

>
> Why  I can not restore it?
>
> * I have using postgresql 9.4
> * The backup  is made it with  pg_dump -Fc and
> * I try to restore with pg_restore -d database --disable-triggers
>
> * This is  the message: COPY failed for table "stlm_201012ad": ERROR:
>  new row for relation "stlm_201012ad" violates check constraint
> "time_stamp_201012ad"
> DETAIL:  Failing row contains (..., 2011-01-01 00:00:03.925+00, .., 0).
>
> * this the  constraint:  "time_stamp_201012ad" CHECK (time_stamp >=
> '2010-12-01'::date AND time_stamp < '2011-01-01'::date)
>
> if change 2011-01-01 00:00:03.925+00 to 2011-01-01 00:00:03.925-06  works ok
> --
>


--
Adrian Klaver
adrian.klaver@aklaver.com