From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Edmundo Robles <edmundo(at)sw-argos(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: recovery dump on database with different timezone |
Date: | 2017-01-24 00:57:18 |
Message-ID: | 0fc5a7bc-e97d-7ba6-bde5-81fdfdc03bae@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 01/23/2017 04:30 PM, Edmundo Robles wrote:
>
>
> On Mon, Jan 23, 2017 at 6:02 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>
>
> --
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2017-01-24 00:58:36 | Re: recovery dump on database with different timezone |
Previous Message | Edmundo Robles | 2017-01-24 00:30:31 | Re: recovery dump on database with different timezone |