Keith's Ramblings…

Archive for March, 2012

Handling DST changes in PostgreSQL

with 6 comments

So most DBAs would tell you the best way to handle Daylight Saving Time in the database is to run your server in UTC time and just avoid the complications all together. That is definitely ideal! But not everyone follows that advice and if a system has been running in a timezone with DST for a long time, changing to UTC can be a huge hassle.

One big issue that can crop up due to DST is with systems that do time-based replication from one database to another via specialized jobs (not streaming replication or the built in master/slave stuff). An example would be replicating an insert-only table for archival purposes and doing it incrementally based on a timestamp column in the table. You need a way to know when the change has occurred so the replication can handle it appropriately (ex. avoid duplication or missing data due to key violation errors when the clock is set back). It would also be nice to have a way that is universal and will account for when (not if) the DST days change.

One of my coworkers (depesz) came up with a handy little function to do this check that I implemented into the incremental replication mentioned above.

UPDATE: Thanks to misha reporting an issue with to_char not always getting the correct timezone abbreviation, I’ve updated the function to instead use date_part (or extract works too) to more accurately get the timezone data to compare.

This function is called every time the replication function runs to see if the timezone is different the next day. If it is, it returns true so you know you have to handle things a little differently than usual. This is a snippet of the code from our incremental replication function to see how it’s used

We avoid the DST issues in this case by just not allowing the replication to run between 12:30am and 2:30am on a DST day. Our systems can handle having the replication delayed during that time period, so this was the easiest solution.

This is just one example of how to use that function, but it at least this gives you an idea of how a system that runs in a DST timezone can handle these types of issues.

Written by Keith

March 20th, 2012 at 11:05 am

Posted in PostgreSQL

Tagged with ,