From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Edmundo Robles <edmundo(at)sw-argos(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: recovery dump on database with different timezone |
Date: | 2017-01-24 00:02:33 |
Message-ID: | b5af1536-d111-6817-f20a-8e1e3de1fe73@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | julyanto SUTANDANG | 2017-01-24 00:11:30 | Re: pg_dump Conflict with recovery |
Previous Message | Merlin Moncure | 2017-01-23 23:46:48 | Re: What is the purpose of PostGIS on PostgreSQL? |