Search Postgresql Archives

Re: recovery dump on database with different timezone

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Mon, Jan 23, 2017 at 6:02 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx



--


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux