Search Postgresql Archives

Re: recovery dump on database with different timezone

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

 



On 01/23/2017 04:30 PM, Edmundo Robles wrote:


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

If you output the dump file to plain text:

pg_restore -f backup.sql backup.dmp

is there something in there setting the timezone to something other then UTC?



        * 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

Not seeing how?:

production=# show timezone;
 TimeZone
----------
 UTC
(1 row)


production=# select ' 2011-01-01 00:00:03.925+00'::timestamptz < '2011-01-01'::date;
 ?column?
----------
 f
(1 row)

production=# select ' 2011-01-01 00:00:03.925+00'::timestamptz < '2011-01-01'::date;
 ?column?
----------
 f
(1 row)

        --



 the  date type  is with timezone or not?


    --
    Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>




--



--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[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