recovery dump on database with different timezone

From: Edmundo Robles <edmundo(at)sw-argos(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: recovery dump on database with different timezone
Date: 2017-01-23 23:45:33
Message-ID: CAOXzpYDyVcjLMw6On28i0oPsQccAfWRsFkwDOGB1nhiT60o+FA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

I have backed up a database with a 'UTC' timezone, but i can not to
restore it on a database with 'localtime' timezone.

I have a time constraints, the reason of the mistakes.
* First, I did: 'alter database set timezone='UTC'; ' but no restore
it...
* After I did change 'timezone' to UTC in postgresql.conf and restart
, but :(
* at last, i did 'pg_restore -l backup.dmp| sed "s:+00:-06:g"|psql database
but copy fails with some records, so some tables are empty :(
* i will try replacing +00 by empty string, but ...

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2017-01-23 23:46:48 Re: What is the purpose of PostGIS on PostgreSQL?
Previous Message Israel Brewster 2017-01-23 22:45:21 Re: pg_dump Conflict with recovery