Обсуждение: here does postgres take its timezone information from?
Hi All, Brazil recently abolished daylight savings time, resulting in updates to system timezone information packages. Does postgres use these? If so, does it need a reload or restart to see the updated zone info? If not, how does postgres store/obtain its timezone zone information and how would this be updated? cheers, Chris
On 11/5/19 2:46 PM, Chris Withers wrote: > Hi All, > > Brazil recently abolished daylight savings time, resulting in updates to > system timezone information packages. > Does postgres use these? If so, does it need a reload or restart to see > the updated zone info? > > If not, how does postgres store/obtain its timezone zone information and > how would this be updated? https://www.postgresql.org/about/news/1960/ PostgreSQL 11.5, 10.10, 9.6.15, 9.5.19, 9.4.24, and 12 Beta 3 Released! "This update also contains tzdata release 2019b for DST law changes in Brazil, plus historical corrections for Hong Kong, Italy, and Palestine. This update also adds support for zic's new -b slim option to reduce the size of the installed zone files, though it is not currently being used by PostgreSQL." > > cheers, > > Chris > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/5/19 2:46 PM, Chris Withers wrote: > Hi All, > > Brazil recently abolished daylight savings time, resulting in updates to > system timezone information packages. > Does postgres use these? If so, does it need a reload or restart to see > the updated zone info? > > If not, how does postgres store/obtain its timezone zone information and > how would this be updated? > As to where it gets its timezone info: https://www.postgresql.org/docs/11/datetime-config-files.html > cheers, > > Chris > > -- Adrian Klaver adrian.klaver@aklaver.com
On 05/11/2019 22:54, Adrian Klaver wrote: > On 11/5/19 2:46 PM, Chris Withers wrote: >> Hi All, >> >> Brazil recently abolished daylight savings time, resulting in updates >> to system timezone information packages. >> Does postgres use these? If so, does it need a reload or restart to >> see the updated zone info? >> >> If not, how does postgres store/obtain its timezone zone information >> and how would this be updated? > > https://www.postgresql.org/about/news/1960/ > PostgreSQL 11.5, 10.10, 9.6.15, 9.5.19, 9.4.24, and 12 Beta 3 Released! > > "This update also contains tzdata release 2019b for DST law changes in > Brazil, plus historical corrections for Hong Kong, Italy, and Palestine. > This update also adds support for zic's new -b slim option to reduce the > size of the installed zone files, though it is not currently being used > by PostgreSQL." Hmm. Is there any option to use the system timezone packages? If not, why not? Chris
On 11/5/19 3:00 PM, Chris Withers wrote: > On 05/11/2019 22:54, Adrian Klaver wrote: >> On 11/5/19 2:46 PM, Chris Withers wrote: >>> Hi All, >>> >>> Brazil recently abolished daylight savings time, resulting in updates >>> to system timezone information packages. >>> Does postgres use these? If so, does it need a reload or restart to >>> see the updated zone info? >>> >>> If not, how does postgres store/obtain its timezone zone information >>> and how would this be updated? >> >> https://www.postgresql.org/about/news/1960/ >> PostgreSQL 11.5, 10.10, 9.6.15, 9.5.19, 9.4.24, and 12 Beta 3 Released! >> >> "This update also contains tzdata release 2019b for DST law changes in >> Brazil, plus historical corrections for Hong Kong, Italy, and >> Palestine. This update also adds support for zic's new -b slim option >> to reduce the size of the installed zone files, though it is not >> currently being used by PostgreSQL." > > Hmm. Is there any option to use the system timezone packages? https://www.postgresql.org/docs/11/install-procedure.html --with-system-tzdata=DIRECTORY > > If not, why not? > > Chris -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Nov 6, 2019 at 12:02 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 11/5/19 3:00 PM, Chris Withers wrote: > > Hmm. Is there any option to use the system timezone packages? > > https://www.postgresql.org/docs/11/install-procedure.html > > --with-system-tzdata=DIRECTORY By the way, you can see if your installation of PostgreSQL was built to use system-provided tzdata by running the pg_config program that was installed alongside it. That could be useful if it was built by a package maintainer (Debian etc) and you want to see how they configured it. You'll see something like CONFIGURE = '... --with-system-tzdata=/usr/share/... ' if it's using OS vendor tzdata files. I hope that most distributions do that*, because otherwise you could finish up with lots of out-of-sync copies of the tzdata database inside your database, your JVM, your libc, etc etc, and you want a single source of truth for that stuff. Once I was involved in rolling out a last minute DST rule change that happened in Australia due to politicians and an international sporting event, and we had to go hunting for copies of tzdata hiding on our servers that had to agree on when the financial markets were opening... we found many copies, and ever since then I complain wherever I see packages shipping their own copies of this stuff... Assuming you are using system tzdata, your other question was what you need to do after the tzdata files have been updated. I suspect that new PostgreSQL database sessions (processes) will see the new rules, but existing sessions may continue to see the old rules if they had loaded them already, because we cache them in per-process memory (see pg_tzset()). It would probably be safest to restart the PostgreSQL cluster. If you're using PostgreSQL's build-in tzdata, then you'll need to restart your cluster anyway once you install the version that shipped with the new tzdata rules, and depending on your package manager, that might happen automatically when you upgrade. *It looks like FreeBSD's port uses the copy of tzdata from the PostgreSQL source tree by default and thus that is what you get if you install PostgreSQL with "pkg". That's not a great default IMHO and should be changed.
Thomas Munro <thomas.munro@gmail.com> writes:
> On Wed, Nov 6, 2019 at 12:02 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 11/5/19 3:00 PM, Chris Withers wrote:
>>> Hmm. Is there any option to use the system timezone packages?
>> --with-system-tzdata=DIRECTORY
> I hope that most distributions do that*, because otherwise you
> could finish up with lots of out-of-sync copies of the tzdata database
> inside your database, your JVM, your libc, etc etc, and you want a
> single source of truth for that stuff.
Right. Our recommendation is to use --with-system-tzdata if you're on
a platform where the vendor updates their copy of tzdata regularly.
The fact that we supply a copy of tzdata at all is really just a fallback
for folks on poorly-supported platforms. (Naming no names here ...)
> Assuming you are using system tzdata, your other question was what you
> need to do after the tzdata files have been updated. I suspect that
> new PostgreSQL database sessions (processes) will see the new rules,
> but existing sessions may continue to see the old rules if they had
> loaded them already, because we cache them in per-process memory (see
> pg_tzset()). It would probably be safest to restart the PostgreSQL
> cluster.
Yeah, I think you need a restart typically. The postmaster process will
not absorb any update to timezone data it's already loaded, and child
processes will mostly inherit that data via fork(). You might be able
to finagle it by hacks like changing postgresql.conf to a different
timezone name that happens to be equivalent, but on the whole a quick
restart after updating the zone data is the best bet.
(Really, if your active zone's rules have changed, you'd be well advised
to just reboot the whole darn box. Postgres is *very far* from being
the only daemon that is going to give you issues with this.)
> *It looks like FreeBSD's port uses the copy of tzdata from the
> PostgreSQL source tree by default and thus that is what you get if you
> install PostgreSQL with "pkg". That's not a great default IMHO and
> should be changed.
Ugh. Who can we poke there?
regards, tom lane
On Wed, Nov 6, 2019 at 2:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thomas Munro <thomas.munro@gmail.com> writes: > > On Wed, Nov 6, 2019 at 12:02 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > >> On 11/5/19 3:00 PM, Chris Withers wrote: > >>> Hmm. Is there any option to use the system timezone packages? > > >> --with-system-tzdata=DIRECTORY > > > I hope that most distributions do that*, because otherwise you > > could finish up with lots of out-of-sync copies of the tzdata database > > inside your database, your JVM, your libc, etc etc, and you want a > > single source of truth for that stuff. > > Right. Our recommendation is to use --with-system-tzdata if you're on > a platform where the vendor updates their copy of tzdata regularly. > The fact that we supply a copy of tzdata at all is really just a fallback > for folks on poorly-supported platforms. (Naming no names here ...) Incidentally, that's also why I don't want to give up on libc collations quite as easily as some, despite their limitations. It should be possible to get all the software on your system to agree on the ordering of two strings and the current time! > > *It looks like FreeBSD's port uses the copy of tzdata from the > > PostgreSQL source tree by default and thus that is what you get if you > > install PostgreSQL with "pkg". That's not a great default IMHO and > > should be changed. > > Ugh. Who can we poke there? Maybe Palle? (CCed).
> 6 nov. 2019 kl. 03:03 skrev Thomas Munro <thomas.munro@gmail.com>: > > On Wed, Nov 6, 2019 at 2:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Thomas Munro <thomas.munro@gmail.com> writes: >>> On Wed, Nov 6, 2019 at 12:02 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: >>>> On 11/5/19 3:00 PM, Chris Withers wrote: >>>>> Hmm. Is there any option to use the system timezone packages? >> >>>> --with-system-tzdata=DIRECTORY >> >>> I hope that most distributions do that*, because otherwise you >>> could finish up with lots of out-of-sync copies of the tzdata database >>> inside your database, your JVM, your libc, etc etc, and you want a >>> single source of truth for that stuff. >> >> Right. Our recommendation is to use --with-system-tzdata if you're on >> a platform where the vendor updates their copy of tzdata regularly. >> The fact that we supply a copy of tzdata at all is really just a fallback >> for folks on poorly-supported platforms. (Naming no names here ...) > > Incidentally, that's also why I don't want to give up on libc > collations quite as easily as some, despite their limitations. It > should be possible to get all the software on your system to agree on > the ordering of two strings and the current time! > >>> *It looks like FreeBSD's port uses the copy of tzdata from the >>> PostgreSQL source tree by default and thus that is what you get if you >>> install PostgreSQL with "pkg". That's not a great default IMHO and >>> should be changed. >> >> Ugh. Who can we poke there? > > Maybe Palle? (CCed). Hi, The decision to use postgresql's tzdata is quite old. It was based on the assumption that postgres is updated more frequentlythan the operating system, and that for that reason it was better to use postgresql's tzdata, since it would bemore accurate more often. This is probably not true anymore, so I agree it should probably be changed to default=system-tzdataon FreeBSD. I will commit an upgrade in Thursday, and unless anybody raise their voice against it, I will change the default settingthen. Regards, Palle
On Wed, Nov 13, 2019 at 3:40 AM Palle Girgensohn <girgen@pingpong.net> wrote: > The decision to use postgresql's tzdata is quite old. It was based on the assumption that postgres is updated more frequentlythan the operating system, and that for that reason it was better to use postgresql's tzdata, since it would bemore accurate more often. This is probably not true anymore, so I agree it should probably be changed to default=system-tzdataon FreeBSD. > > I will commit an upgrade in Thursday, and unless anybody raise their voice against it, I will change the default settingthen. Thanks! FreeBSD users already have the choice between zoneinfo from base or the misc/zoneinfo port if for some reason they want to control tzdata updates separately. PostgreSQL and FreeBSD both track tzdata closely, and both pushed a commit for version 2019c into their stable branches within a couple of weeks of it being released, so I don't foresee any problem with this change, and staying in sync with libc seems to outweigh any other concerns IMHO. https://www.iana.org/time-zones https://github.com/postgres/postgres/tree/REL_12_STABLE/src/timezone/data https://github.com/freebsd/freebsd/tree/stable/12/contrib/tzdata
Thomas Munro <thomas.munro@gmail.com> writes:
> FreeBSD users already have the choice between zoneinfo from base or
> the misc/zoneinfo port if for some reason they want to control tzdata
> updates separately. PostgreSQL and FreeBSD both track tzdata closely,
> and both pushed a commit for version 2019c into their stable branches
> within a couple of weeks of it being released, so I don't foresee any
> problem with this change, and staying in sync with libc seems to
> outweigh any other concerns IMHO.
Note that the normal situation, on a platform with a well-maintained
tzdata package, is that tzdata acquired via Postgres is going to lag
behind. That's because we don't ship tzdata updates until our next
quarterly release, while the OS vendor probably has a much more
streamlined process for package updates.
In the case of 2019c, I pushed it into our code while it was still
pretty fresh, because it was just a few days to 12rc1 and I thought
the RC should contain the latest tzdata. But usually we only bother
to sync from tzdata upstream when a quarterly release is impending.
In theory, it's possible that a tzdata update could break Postgres.
But it'd probably break a lot of other code too. In practice, the
IANA people are well aware of that hazard, so there is a *long*
delay between when they introduce a new tzcode feature and when
they're willing to start relying on it in tzdata. We try to stay
fairly current on our copy of tzcode, so that should only be a live
hazard for out-of-support Postgres branches.
(I was reminded of this just the other day when I had occasion
to run the 9.2 regression tests, and they failed because of a
no-longer-valid assumption about Venezuelan time. I should
rebuild that legacy installation without --with-system-tzdata,
I guess, so that it uses tzdata it's expecting.)
regards, tom lane
Palle Girgensohn <girgen@pingpong.net> writes: >> 6 nov. 2019 kl. 03:03 skrev Thomas Munro <thomas.munro@gmail.com>: >>> *It looks like FreeBSD's port uses the copy of tzdata from the >>> PostgreSQL source tree by default and thus that is what you get if you >>> install PostgreSQL with "pkg". That's not a great default IMHO and >>> should be changed. > The decision to use postgresql's tzdata is quite old. It was based on the assumption that postgres is updated more frequentlythan the operating system, and that for that reason it was better to use postgresql's tzdata, since it would bemore accurate more often. This is probably not true anymore, so I agree it should probably be changed to default=system-tzdataon FreeBSD. > I will commit an upgrade in Thursday, and unless anybody raise their voice against it, I will change the default settingthen. So it seems that that change was not entirely without fallout: https://www.postgresql.org/message-id/flat/16118-ef1e45e342c52416%40postgresql.org I don't think this is reason to revert the change, exactly, but it's a concern. I wonder why FreeBSD editorializes on the set of zone names? regards, tom lane
On Sat, Nov 16, 2019 at 8:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Palle Girgensohn <girgen@pingpong.net> writes: > >> 6 nov. 2019 kl. 03:03 skrev Thomas Munro <thomas.munro@gmail.com>: > >>> *It looks like FreeBSD's port uses the copy of tzdata from the > >>> PostgreSQL source tree by default and thus that is what you get if you > >>> install PostgreSQL with "pkg". That's not a great default IMHO and > >>> should be changed. > > > The decision to use postgresql's tzdata is quite old. It was based on the assumption that postgres is updated more frequentlythan the operating system, and that for that reason it was better to use postgresql's tzdata, since it would bemore accurate more often. This is probably not true anymore, so I agree it should probably be changed to default=system-tzdataon FreeBSD. > > I will commit an upgrade in Thursday, and unless anybody raise their voice against it, I will change the default settingthen. > > So it seems that that change was not entirely without fallout: > > https://www.postgresql.org/message-id/flat/16118-ef1e45e342c52416%40postgresql.org > > I don't think this is reason to revert the change, exactly, > but it's a concern. I wonder why FreeBSD editorializes on > the set of zone names? Ugh. It doesn't have the old backward compatibility names like US/Pacific installed by default, which is a problem if that's what initdb picked for your cluster (or you've stored references to any of those names in other ways).
15 nov. 2019 kl. 21:32 skrev Thomas Munro <thomas.munro@gmail.com>:On Sat, Nov 16, 2019 at 8:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:Palle Girgensohn <girgen@pingpong.net> writes:6 nov. 2019 kl. 03:03 skrev Thomas Munro <thomas.munro@gmail.com>:*It looks like FreeBSD's port uses the copy of tzdata from the
PostgreSQL source tree by default and thus that is what you get if you
install PostgreSQL with "pkg". That's not a great default IMHO and
should be changed.The decision to use postgresql's tzdata is quite old. It was based on the assumption that postgres is updated more frequently than the operating system, and that for that reason it was better to use postgresql's tzdata, since it would be more accurate more often. This is probably not true anymore, so I agree it should probably be changed to default=system-tzdata on FreeBSD.
I will commit an upgrade in Thursday, and unless anybody raise their voice against it, I will change the default setting then.
So it seems that that change was not entirely without fallout:
https://www.postgresql.org/message-id/flat/16118-ef1e45e342c52416%40postgresql.org
I don't think this is reason to revert the change, exactly,
but it's a concern. I wonder why FreeBSD editorializes on
the set of zone names?
Ugh. It doesn't have the old backward compatibility names like
US/Pacific installed by default, which is a problem if that's what
initdb picked for your cluster (or you've stored references to any of
those names in other ways).
Ogh, I had no idea of this limitation.
One quick fix is to revert the change. Tom thinks this is not reason to revert. Would it be enough to edit the postgresql.conf to use the correct "modern" name for US/Pacific (PST?)? In rhar case, an update note might be sufficient?
Palle
Palle Girgensohn <girgen@pingpong.net> writes:
> 15 nov. 2019 kl. 21:32 skrev Thomas Munro <thomas.munro@gmail.com>:
>> Ugh. It doesn't have the old backward compatibility names like
>> US/Pacific installed by default, which is a problem if that's what
>> initdb picked for your cluster (or you've stored references to any of
>> those names in other ways).
> One quick fix is to revert the change. Tom thinks this is not reason to revert. Would it be enough to edit the
postgresql.confto use the correct "modern" name for US/Pacific (PST?)? In rhar case, an update note might be
sufficient?
I think the "official" name of that zone is America/Los_Angeles.
But initdb might seize on the US/Pacific alias, if available,
because it's shorter. We've seen related problems with other
time zone names, though usually it was just cosmetic and not a
reason for the postmaster to fail to start.
Yes, changing the zone name in postgresql.conf should be a sufficient
fix. In theory, a FreeBSD user ought to know the "official" alias
for their zone, since the rest of the system would expect that.
So this is slightly tedious if initdb chose a non-official alias,
but I don't think it's reason to panic.
regards, tom lane
On Sat, Nov 16, 2019 at 7:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Palle Girgensohn <girgen@pingpong.net> writes: > > 15 nov. 2019 kl. 21:32 skrev Thomas Munro <thomas.munro@gmail.com>: > >> Ugh. It doesn't have the old backward compatibility names like > >> US/Pacific installed by default, which is a problem if that's what > >> initdb picked for your cluster (or you've stored references to any of > >> those names in other ways). > > > One quick fix is to revert the change. Tom thinks this is not reason to revert. Would it be enough to edit the postgresql.confto use the correct "modern" name for US/Pacific (PST?)? In rhar case, an update note might be sufficient? > > I think the "official" name of that zone is America/Los_Angeles. > But initdb might seize on the US/Pacific alias, if available, > because it's shorter. We've seen related problems with other > time zone names, though usually it was just cosmetic and not a > reason for the postmaster to fail to start. > > Yes, changing the zone name in postgresql.conf should be a sufficient > fix. In theory, a FreeBSD user ought to know the "official" alias > for their zone, since the rest of the system would expect that. > So this is slightly tedious if initdb chose a non-official alias, > but I don't think it's reason to panic. Perhaps the best thing would be to revert this for the older PostgreSQL releases so that people doing minor version upgrades are inconvenienced by a system that can't start up after "pkg upgrade", but do it for 12 since not many people will be using that yet?
16 nov. 2019 kl. 23:06 skrev Thomas Munro <thomas.munro@gmail.com>:On Sat, Nov 16, 2019 at 7:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:Palle Girgensohn <girgen@pingpong.net> writes:15 nov. 2019 kl. 21:32 skrev Thomas Munro <thomas.munro@gmail.com>:Ugh. It doesn't have the old backward compatibility names like
US/Pacific installed by default, which is a problem if that's what
initdb picked for your cluster (or you've stored references to any of
those names in other ways).One quick fix is to revert the change. Tom thinks this is not reason to revert. Would it be enough to edit the postgresql.conf to use the correct "modern" name for US/Pacific (PST?)? In rhar case, an update note might be sufficient?
I think the "official" name of that zone is America/Los_Angeles.
But initdb might seize on the US/Pacific alias, if available,
because it's shorter. We've seen related problems with other
time zone names, though usually it was just cosmetic and not a
reason for the postmaster to fail to start.
Yes, changing the zone name in postgresql.conf should be a sufficient
fix. In theory, a FreeBSD user ought to know the "official" alias
for their zone, since the rest of the system would expect that.
So this is slightly tedious if initdb chose a non-official alias,
but I don't think it's reason to panic.
Perhaps the best thing would be to revert this for the older
PostgreSQL releases so that people doing minor version upgrades are
inconvenienced by a system that can't start up after "pkg upgrade",
but do it for 12 since not many people will be using that yet?
That could be a way, yes. Any thoughts on this from others following this thread?
Palle
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: >>> Ugh. It doesn't have the old backward compatibility names like >>> US/Pacific installed by default, which is a problem if that's what >>> initdb picked for your cluster (or you've stored references to any >>> of those names in other ways). >> One quick fix is to revert the change. Tom thinks this is not reason >> to revert. Would it be enough to edit the postgresql.conf to use the >> correct "modern" name for US/Pacific (PST?)? In rhar case, an update >> note might be sufficient? Tom> I think the "official" name of that zone is America/Los_Angeles. Tom> But initdb might seize on the US/Pacific alias, if available, And now you know why I have been saying for so many years that initdb should use the official names! -- Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> Tom> I think the "official" name of that zone is America/Los_Angeles.
> Tom> But initdb might seize on the US/Pacific alias, if available,
> And now you know why I have been saying for so many years that initdb
> should use the official names!
[ shrug... ] The installed format doesn't provide any way to
distinguish which are the "official" names. They're typically
all hardlinks to the same file.
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> Andrew Gierth <andrew@tao11.riddles.org.uk> writes: Tom> I think the "official" name of that zone is America/Los_Angeles. Tom> But initdb might seize on the US/Pacific alias, if available, >> And now you know why I have been saying for so many years that initdb >> should use the official names! Tom> [ shrug... ] The installed format doesn't provide any way to Tom> distinguish which are the "official" names. They're typically all Tom> hardlinks to the same file. zone.tab / zone1970.tab. Which I first pointed out before 8.2 came out... -- Andrew.
Palle Girgensohn <girgen@pingpong.net> writes:
> 16 nov. 2019 kl. 23:06 skrev Thomas Munro <thomas.munro@gmail.com>:
>> Perhaps the best thing would be to revert this for the older
>> PostgreSQL releases so that people doing minor version upgrades are
>> inconvenienced by a system that can't start up after "pkg upgrade",
>> but do it for 12 since not many people will be using that yet?
> That could be a way, yes. Any thoughts on this from others following this thread?
I whined about this on the tz mailing list, and got the attention of
the FreeBSD tzdata package maintainer [1]. It seems possible that he'll
change that policy, so I'd advise doing nothing until that discussion
settles.
regards, tom lane
[1] https://mm.icann.org/pipermail/tz/2019-November/028633.html