Re: recovery dump on database with different timezone

On Mon, Jan 23, 2017 at 6:02 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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?

> I did alter the destination database.

>
>
> * After I did change 'timezone' to UTC in postgresql.conf and
>> restart , but :(
>>
>
> From the source(backup) Postgres instance or the destination(restore)
> instance?

I did change on destination server configuration.

>
>
> * at last, i did 'pg_restore -l backup.dmp| sed "s:+00:-06:g"|psql
>>
>
> The above is not the complete command, correct?
>
the complete command is:
pg_restore backup.dmp| sed "s:+00:-06:g"|psql database

>
> 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?
>

After change configuration, returns UTC.

>
> Are the timestamp fields you are COPYing into declared with timezone or
> without timezone?
>
> the field is declared with 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
>> --
>>
>>
>
the date type is with timezone or not?

>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--