Thread: [GENERAL] recovery dump on database with different timezone
On Mon, Jan 23, 2017 at 6:02 PM, Adrian Klaver <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
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@aklaver.com
thanks for your comments, i will change the constraints from date to timestamp with time zone, i hope this works :)
On Mon, Jan 23, 2017 at 7:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 01/23/2017 05:14 PM, David G. Johnston wrote:
>> To your example - testing in UTC is going to always result in failure
>> for Z values <= 0 since they will all result in a UTC date of
>> 2011-01-01. Choosing +06 would result in a passed test.
> That was sort of the point, I was just using the value that the OP said
> worked:
> "if change 2011-01-01 00:00:03.925+00 to 2011-01-01 00:00:03.925-06
> works ok"
> I could not see how it did.
Well,
select '2011-01-01 00:00:03.925-06'::timestamptz >= '2011-01-01'::date;
passes if TimeZone is US central time (UTC-6) or anyplace east of there.
It fails west of there, because the "date" value is interpreted as
midnight local time for purposes of comparison to a "timestamptz" value:
regression=# set timezone = EST5EDT;
SET
regression=# select '2011-01-01 00:00:03.925-06'::timestamptz >= '2011-01-01'::date;
?column?
----------
t
(1 row)
regression=# set timezone = PST8PDT;
SET
regression=# select '2011-01-01 00:00:03.925-06'::timestamptz >= '2011-01-01'::date;
?column?
----------
f
(1 row)
The key point here is that a CHECK constraint is checked when the row
is stored, and if it depends on any GUC parameters then the
then-prevailing parameter will be used. So the OP's problem is he has
some rows that passed the constraint based on the TimeZone value that
was active when they were stored, but they don't pass the constraint
when TimeZone is UTC.
If the failing rows are failing because of this side of the range
constraint, they must have been stored under a zone setting east
of UTC. But it's just as likely that they are failing because of
the other side of the range constraint (the <= 2012-01-01 end),
implying that they were stored under a zone setting west of UTC.
regards, tom lane