From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wells Oliver <wells.oliver@gmail.com>
cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg_restore: warning: invalid creation date in header
In-reply-to: 
 <CAOC+FBXAPDztW3o7DzVHeQFyaOe5Z-k3K16xtCSXqWTa03k3qA@mail.gmail.com>
References: 
 <CAOC+FBWDhDHO7G-i1_n_hjRzCnUeFO+H-Czi1y10mFhRWpBrew@mail.gmail.com>
 <50545.1623597209@sss.pgh.pa.us> <52307.1623599837@sss.pgh.pa.us>
 <CAOC+FBXMZaRa7msrbCshJPLyA7i17n_=a4DhLiHZZEqesncwKw@mail.gmail.com>
 <52961.1623600745@sss.pgh.pa.us>
 <CAOC+FBXAPDztW3o7DzVHeQFyaOe5Z-k3K16xtCSXqWTa03k3qA@mail.gmail.com>
Comments: In-reply-to Wells Oliver <wells.oliver@gmail.com>
	message dated "Sun, 13 Jun 2021 09:16:00 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <55480.1623604303.1@sss.pgh.pa.us>
Date: Sun, 13 Jun 2021 13:11:43 -0400
Message-ID: <55481.1623604303@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/55481.1623604303%40sss.pgh.pa.us>
Precedence: bulk

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