recovery dump on database with different timezone

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?

* After I did change 'timezone' to UTC in postgresql.conf and
restart , but :(

From the source(backup) Postgres instance or the destination(restore)
instance?

* at last, i did 'pg_restore -l backup.dmp| sed "s:+00:-06:g"|psql

The above is not the complete command, correct?

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?

Are the timestamp fields you are COPYing into declared with timezone or
without 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
--

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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

--

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

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

On Mon, Jan 23, 2017 at 4:45 PM, Edmundo Robles <edmundo@sw-argos.com>
wrote:

* 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)
​​

​I'm not that fluent experimenting with dump/restore mechanics (hence the
uncertainty below) but...​

​Your record only passes the check constraint if the system is in a
timezone West of UTC. Because then at least 1 hour is subtracted from the
supplied timestamp before converting it into date for comparison.

If you set the target database timezone to be the proper designation for
"localtime" the restoration might work.

Your claim that the source system was setup for UTC is suspect...

David J.

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

* 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

Another thought, do you have a .psqlrc file that is changing the timezone?

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Mon, Jan 23, 2017 at 5:57 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

* 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)

​You failed to change the timezone to -06 in the second query...

David J.
​

Edmundo Robles <edmundo@sw-argos.com> writes:

* this the constraint: "time_stamp_201012ad" CHECK (time_stamp >=
'2010-12-01'::date AND time_stamp < '2011-01-01'::date)

If that's on a timestamptz column, it's a seriously dangerous constraint,
because the limitations it enforces are dependent on the current timezone
setting (which will determine what is the midnight boundary for those
dates). It's entirely possible that your database is unrestorable because
different entries in the column were made under different timezone
settings and there is *no* zone value in which all of them will be able to
pass the constraint. Even if there is such a zone value, you already know
that UTC isn't it.

I'd suggest dropping the constraint in the source database (or editing the
dump file to remove it), restoring the data, and then looking to clean up
the data before you try to put the constraint back on. And this time,
express it as something like time_stamp >= '2010-12-01 00:00+00' etc ...

regards, tom lane

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

On 01/23/2017 05:01 PM, David G. Johnston wrote:

On Mon, Jan 23, 2017 at 5:57 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:

* 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)

​You failed to change the timezone to -06 in the second query...

Cut and paste error, what it should have been:

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

production=# select ' 2011-01-01 00:00:03.925-06'::timestamptz <
'2011-01-01'::date;
?column?
----------
f

David J.
​

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Mon, Jan 23, 2017 at 6:08 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

production=# select ' 2011-01-01 00:00:03.925-06'::timestamptz <
'2011-01-01'::date;
?column?
----------
f

​What Tom said - though I missed the part about "the timezone when the data
was entered" - I was thinking it was only ​the timezone at the time of the
dump that would impact things.

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.

David J.

On 01/23/2017 05:14 PM, David G. Johnston wrote:

On Mon, Jan 23, 2017 at 6:08 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:

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

production=# select ' 2011-01-01 00:00:03.925-06'::timestamptz <
'2011-01-01'::date;
?column?
----------
f

​What Tom said - though I missed the part about "the timezone when the
data was entered" - I was thinking it was only ​the timezone at the time
of the dump that would impact things.

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.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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

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

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

--

On 01/24/2017 06:58 AM, Edmundo Robles wrote:

thanks for your comments, i will change the constraints from date to
timestamp with time zone, i hope this works :)

There is good chance it will not work until the data is cleaned up or
unless you pick just the right timezone to use in the constraint. Tom's
point being that it looks like the data was entered under different
timezones over the life of the database. It comes down to what you 'day'
range you want the CHECK to work on. In other words what timezone do you
want to anchor the range to?

On Mon, Jan 23, 2017 at 7:48 PM, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

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

--

--
Adrian Klaver
adrian.klaver@aklaver.com

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