pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore: warning: invalid creation date in header
11+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2021-06-13 f807e3410f b7c5823ac3 73fa762417 dd53b46c78 05fccab6af bc7885b7f5 Work around portability issue with newer versions of mktime().
* pg_restore: warning: invalid creation date in header
@ 2021-06-12 23:47 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Wells Oliver @ 2021-06-12 23:47 UTC (permalink / raw)
To: pgsql-admin
These are the first lines when trying to restore a dump created on a
machine in the PDT time zone, into a machine in UTC. Not sure if it
matters, but I wanted to ask.
pg_restore: warning: invalid creation date in header
pg_restore: while PROCESSING TOC:
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-12 23:57 Ron <ronljohnsonjr@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Ron @ 2021-06-12 23:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/12/21 6:47 PM, Wells Oliver wrote:
> These are the first lines when trying to restore a dump created on a
> machine in the PDT time zone, into a machine in UTC. Not sure if it
> matters, but I wanted to ask.
>
> pg_restore: warning: invalid creation date in header
> pg_restore: while PROCESSING TOC:
Given that it's just a warning, did the restore complete?
As for the reason... since PDT is -7 to UTC, I bet the creation date is
*less than* the clock date, which pg_restore warns about.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-12 23:59 Wells Oliver <wells.oliver@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Wells Oliver @ 2021-06-12 23:59 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
That's what I figured. Restore looks fine. Just hadn't seen that before
(but also never had restored a PDT backup to a UTC machine). Just wanted to
check.
On Sat, Jun 12, 2021 at 4:57 PM Ron <ronljohnsonjr@gmail.com> wrote:
> On 6/12/21 6:47 PM, Wells Oliver wrote:
>
> These are the first lines when trying to restore a dump created on a
> machine in the PDT time zone, into a machine in UTC. Not sure if it
> matters, but I wanted to ask.
>
> pg_restore: warning: invalid creation date in header
> pg_restore: while PROCESSING TOC:
>
>
> Given that it's just a warning, did the restore complete?
>
> As for the reason... since PDT is -7 to UTC, I bet the creation date is *less
> than* the clock date, which pg_restore warns about.
>
> --
> Angular momentum makes the world go 'round.
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 15:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2021-06-13 15:13 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> These are the first lines when trying to restore a dump created on a
> machine in the PDT time zone, into a machine in UTC. Not sure if it
> matters, but I wanted to ask.
> pg_restore: warning: invalid creation date in header
Hmm. Looking at the code for that, it knows nothing of timezones.
It's complaining that mktime() failed, which perhaps is because of
bad data in the archive header ... but I'm betting it has something
to do with failing to initialize all the fields of the struct tm
it passes.
However, that code's been like that a long time and we've not heard
previous reports of this. Could you say exactly what the (receiving)
platform is? How was pg_restore built (any special switches, etc)?
The output of pg_config on that machine might be helpful.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 15:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2021-06-13 15:57 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
I wrote:
> Hmm. Looking at the code for that, it knows nothing of timezones.
> It's complaining that mktime() failed, which perhaps is because of
> bad data in the archive header ... but I'm betting it has something
> to do with failing to initialize all the fields of the struct tm
> it passes.
After reading the mktime man page, I take that back --- it's not
supposed to consider the passed-in values of the tm_wday and tm_yday
fields. The only documented reason for mktime to fail is if the
supplied values map to something outside the range of time_t,
which seems a tad unlikely. It'd be interesting perhaps to see
the raw header of your dump file, along the lines of
"od -c dumpfile | head".
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 16:02 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Wells Oliver @ 2021-06-13 16:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Tom, it's a directory format dump, what file would make the most sense to
run oc on? Or does this not work w/a directory?
On Sun, Jun 13, 2021 at 8:57 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > Hmm. Looking at the code for that, it knows nothing of timezones.
> > It's complaining that mktime() failed, which perhaps is because of
> > bad data in the archive header ... but I'm betting it has something
> > to do with failing to initialize all the fields of the struct tm
> > it passes.
>
> After reading the mktime man page, I take that back --- it's not
> supposed to consider the passed-in values of the tm_wday and tm_yday
> fields. The only documented reason for mktime to fail is if the
> supplied values map to something outside the range of time_t,
> which seems a tad unlikely. It'd be interesting perhaps to see
> the raw header of your dump file, along the lines of
> "od -c dumpfile | head".
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 16:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2021-06-13 16:12 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> Tom, it's a directory format dump, what file would make the most sense to
> run oc on? Or does this not work w/a directory?
The toc.dat file. You should see something like
$ od -c mydump/toc.dat | head
0000000 P G D M P 001 016 \0 004 \b 003 001 001 \0 \0 \0
0000020 \0 027 \0 \0 \0 \0 \n \0 \0 \0 \0 \f \0 \0 \0 \0
0000040 \r \0 \0 \0 \0 005 \0 \0 \0 \0 y \0 \0 \0 \0 001
0000060 \0 \0 \0 \0 \n \0 \0 \0 r e g r e s s i
0000100 o n \0 \a \0 \0 \0 1 4 b e t a 1 \0 \a
0000120 \0 \0 \0 1 4 b e t a 1 \0 315 \b \0 \0 \0
0000140 u 037 \0 \0 \0 \0 \0 \0 \0 \0 001 \0 \0 \0 0 \0
0000160 001 \0 \0 \0 0 \0 \b \0 \0 \0 E N C O D I
0000200 N G \0 \b \0 \0 \0 E N C O D I N G \0
0000220 002 \0 \0 \0 \0 # \0 \0 \0 S E T c l i
If there's not a "PGDMP" signature at the start then it's the wrong
file.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 16:16 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Wells Oliver @ 2021-06-13 16:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
0000000 P G D M P 001 016 \0 004 \b 003 001 001 \0 \0 \0
0000020 \0 4 \0 \0 \0 \0 - \0 \0 \0 \0 \t \0 \0 \0 \0
0000040 \v \0 \0 \0 \0 005 \0 \0 \0 \0 y \0 \0 \0 \0 001
0000060 \0 \0 \0 \0 \b \0 \0 \0 b o x s c o r e
0000100 \0 \0 \0 \0 1 3 . 2 ( U b u n t
0000120 u 1 3 . 2 - 1 . p g d g 1 8 .
0000140 0 4 + 1 ) \0 \0 \0 \0 1 3 . 2 (
0000160 U b u n t u 1 3 . 2 - 1 . p g
0000200 d g 1 8 . 0 4 + 1 ) \0 \t ! \0 \0 \0
0000220 E S \0 \0 \0 \0 \0 \0 \0 \0 001 \0 \0 \0 0 \0
On Sun, Jun 13, 2021 at 9:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wells Oliver <wells.oliver@gmail.com> writes:
> > Tom, it's a directory format dump, what file would make the most sense to
> > run oc on? Or does this not work w/a directory?
>
> The toc.dat file. You should see something like
>
> $ od -c mydump/toc.dat | head
> 0000000 P G D M P 001 016 \0 004 \b 003 001 001 \0 \0 \0
> 0000020 \0 027 \0 \0 \0 \0 \n \0 \0 \0 \0 \f \0 \0 \0 \0
> 0000040 \r \0 \0 \0 \0 005 \0 \0 \0 \0 y \0 \0 \0 \0 001
> 0000060 \0 \0 \0 \0 \n \0 \0 \0 r e g r e s s i
> 0000100 o n \0 \a \0 \0 \0 1 4 b e t a 1 \0 \a
> 0000120 \0 \0 \0 1 4 b e t a 1 \0 315 \b \0 \0 \0
> 0000140 u 037 \0 \0 \0 \0 \0 \0 \0 \0 001 \0 \0 \0 0 \0
> 0000160 001 \0 \0 \0 0 \0 \b \0 \0 \0 E N C O D I
> 0000200 N G \0 \b \0 \0 \0 E N C O D I N G \0
> 0000220 002 \0 \0 \0 \0 # \0 \0 \0 S E T c l i
>
> If there's not a "PGDMP" signature at the start then it's the wrong
> file.
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 17:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2021-06-13 17:11 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> 0000000 P G D M P 001 016 \0 004 \b 003 001 001 \0 \0 \0
> 0000020 \0 4 \0 \0 \0 \0 - \0 \0 \0 \0 \t \0 \0 \0 \0
> 0000040 \v \0 \0 \0 \0 005 \0 \0 \0 \0 y \0 \0 \0 \0 001
> 0000060 \0 \0 \0 \0 \b \0 \0 \0 b o x s c o r e
> 0000100 \0 \0 \0 \0 1 3 . 2 ( U b u n t
> 0000120 u 1 3 . 2 - 1 . p g d g 1 8 .
> 0000140 0 4 + 1 ) \0 \0 \0 \0 1 3 . 2 (
> 0000160 U b u n t u 1 3 . 2 - 1 . p g
> 0000200 d g 1 8 . 0 4 + 1 ) \0 \t ! \0 \0 \0
> 0000220 E S \0 \0 \0 \0 \0 \0 \0 \0 001 \0 \0 \0 0 \0
Hpmh ... nothing weird looking about that: it decodes as
0000000 P G D M P 001 016 \0 004 \b 003 001 001 \0 \0 \0
maj min rev int off fmt compression = -1 ..
0000020 \0 4 \0 \0 \0 \0 - \0 \0 \0 \0 \t \0 \0 \0 \0
sec = 4 .......... min = 45 ......... hr = 9 ........... md
0000040 \v \0 \0 \0 \0 005 \0 \0 \0 \0 y \0 \0 \0 \0 001
ay = 11 ...... mon = 5 .......... year = 121 ........ isdst
0000060 \0 \0 \0 \0 \b \0 \0 \0 b o x s c o r e
= 1 ...... dbname length ....
[ plays around a bit ... ] How interesting: on my RHEL8 box, feeding
those values to mktime() works just fine in either local time or UTC:
$ TZ=EST5EDT ./a.out
result = 1623419104, errno = 0 (Success)
$ TZ=UTC ./a.out
result = 1623404704, errno = 0 (Success)
But on a nearby Fedora 34 box:
$ TZ=EST5EDT ./a.out
result = 1623419104, errno = 0 (Success)
$ TZ=UTC ./a.out
result = -1, errno = 75 (Value too large for defined data type)
The newer glibc version does indeed seem to think that isdst=1 is
invalid in the UTC zone. If I change that to isdst=-1 ("don't know")
then it's happy again:
$ TZ=UTC ./a.out
result = 1623404704, errno = 0 (Success)
So, once again, the glibc crew have decided they're smarter than
POSIX and changed the behavior of stable-for-decades APIs.
Oh well.
What I'm inclined to do about this is to try first with the values
as given, and if we get a failure, try again with isdst = -1.
Really, using localtime output as the file date representation
was a damfool idea. It's less portable not more so, if you ask
me; and the fact that it loses timezone information means that
it's actually impossible to reconstruct the timestamp accurately
when pg_restore is run in a different zone than pg_dump was.
I don't think it's worth bumping the archive version just for this;
but next time we have need to do that, we should seriously consider
replacing this mess with a straight seconds-since-the-Epoch timestamp.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 17:27 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Wells Oliver @ 2021-06-13 17:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Wild! That's quite a little rabbit hole there.
I've still got a lot of testing to do, but it does not look like materially
impacted the restored data.. but definitely caught my eye.
Thanks for the explanation, Tom. You're ever the tribute to open source
software.
On Sun, Jun 13, 2021 at 10:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wells Oliver <wells.oliver@gmail.com> writes:
> > 0000000 P G D M P 001 016 \0 004 \b 003 001 001 \0 \0 \0
> > 0000020 \0 4 \0 \0 \0 \0 - \0 \0 \0 \0 \t \0 \0 \0 \0
> > 0000040 \v \0 \0 \0 \0 005 \0 \0 \0 \0 y \0 \0 \0 \0 001
> > 0000060 \0 \0 \0 \0 \b \0 \0 \0 b o x s c o r e
> > 0000100 \0 \0 \0 \0 1 3 . 2 ( U b u n t
> > 0000120 u 1 3 . 2 - 1 . p g d g 1 8 .
> > 0000140 0 4 + 1 ) \0 \0 \0 \0 1 3 . 2 (
> > 0000160 U b u n t u 1 3 . 2 - 1 . p g
> > 0000200 d g 1 8 . 0 4 + 1 ) \0 \t ! \0 \0 \0
> > 0000220 E S \0 \0 \0 \0 \0 \0 \0 \0 001 \0 \0 \0 0 \0
>
> Hpmh ... nothing weird looking about that: it decodes as
>
> 0000000 P G D M P 001 016 \0 004 \b 003 001 001 \0 \0 \0
> maj min rev int off fmt compression = -1 ..
> 0000020 \0 4 \0 \0 \0 \0 - \0 \0 \0 \0 \t \0 \0 \0 \0
> sec = 4 .......... min = 45 ......... hr = 9 ........... md
> 0000040 \v \0 \0 \0 \0 005 \0 \0 \0 \0 y \0 \0 \0 \0 001
> ay = 11 ...... mon = 5 .......... year = 121 ........ isdst
> 0000060 \0 \0 \0 \0 \b \0 \0 \0 b o x s c o r e
> = 1 ...... dbname length ....
>
> [ plays around a bit ... ] How interesting: on my RHEL8 box, feeding
> those values to mktime() works just fine in either local time or UTC:
>
> $ TZ=EST5EDT ./a.out
> result = 1623419104, errno = 0 (Success)
> $ TZ=UTC ./a.out
> result = 1623404704, errno = 0 (Success)
>
> But on a nearby Fedora 34 box:
>
> $ TZ=EST5EDT ./a.out
> result = 1623419104, errno = 0 (Success)
> $ TZ=UTC ./a.out
> result = -1, errno = 75 (Value too large for defined data type)
>
> The newer glibc version does indeed seem to think that isdst=1 is
> invalid in the UTC zone. If I change that to isdst=-1 ("don't know")
> then it's happy again:
>
> $ TZ=UTC ./a.out
> result = 1623404704, errno = 0 (Success)
>
> So, once again, the glibc crew have decided they're smarter than
> POSIX and changed the behavior of stable-for-decades APIs.
> Oh well.
>
> What I'm inclined to do about this is to try first with the values
> as given, and if we get a failure, try again with isdst = -1.
>
> Really, using localtime output as the file date representation
> was a damfool idea. It's less portable not more so, if you ask
> me; and the fact that it loses timezone information means that
> it's actually impossible to reconstruct the timestamp accurately
> when pg_restore is run in a different zone than pg_dump was.
>
> I don't think it's worth bumping the archive version just for this;
> but next time we have need to do that, we should seriously consider
> replacing this mess with a straight seconds-since-the-Epoch timestamp.
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pg_restore: warning: invalid creation date in header
@ 2021-06-13 18:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2021-06-13 18:03 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> I've still got a lot of testing to do, but it does not look like materially
> impacted the restored data.. but definitely caught my eye.
No, it doesn't affect the validity of the restored data at all.
If you did "pg_restore -l", it would print a bogus archive creation
date, but I'm pretty sure we don't use the file timestamp otherwise.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2021-06-13 18:03 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-12 23:47 pg_restore: warning: invalid creation date in header Wells Oliver <wells.oliver@gmail.com>
2021-06-12 23:57 ` Ron <ronljohnsonjr@gmail.com>
2021-06-12 23:59 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-13 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-13 15:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-13 16:02 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-13 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-13 16:16 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-13 17:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-13 17:27 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-13 18:03 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox