Thread: pg_restore: warning: invalid creation date in header

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

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