Re: [GENERAL] recovery dump on database with different timezone

On 01/23/2017 04:30 PM, Edmundo Robles wrote:
>
>
> On Mon, Jan 23, 2017 at 6:02 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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

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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
>


--
Adrian Klaver
adrian.klaver@aklaver.com