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