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.
