Re: pg_restore: warning: invalid creation date in header

Wells Oliver <wells(dot)oliver(at)gmail(dot)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