Re: pg_restore: warning: invalid creation date in header

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>