PSA: Time zone breaking change in Debian Trixie

[BTOdell]

This isn't an issue with the pgautoupgrade project, but I wanted to bring attention to the breaking change when pairing Postgres 18 with Debian Trixie. It would have been a major bug in my company's production deployment if it wasn't caught in staging.

Debian Trixie removed a bunch of legacy time zone identifiers; notable the US/* time zones. Since Postgres simply reads the tzdata to populate its time zone info, this is effectively a breaking change when running Postgres on Debian.

Just wanted to shed some light on the issue so others don't blow up their production environments.

References:

• https://www.debian.org/releases/trixie/release-notes/issues.html#timezones-split-off-into-tzdata-legacy-package
• https://news.ycombinator.com/item?id=45218111

EDIT:
The official Postgres docker image still supports Debian Bookworm. Maybe pgautoupgrade should also support bookworm? I don't know if the underlying OS makes a difference when migrating.

[justinclift]

How did you resolve the problem at your work?

[BTOdell]

Thankfully we were able to determine that we could effectively migrate the time zone data stored in our tables to the new IANA time zone identifiers and update our application's code to use the new identifiers.

Our staging environment caught this time zone issue before it hit prod, so my team spent the day figuring out how to move forward with data migration instead of frantically restoring backups.