I am running Postgres 9.1 on Windows 2012 servers. I am using postgres's streaming replication to replicate data between 2 servers.
It has so happened that the time zone of the slave has not been set correctly. The master time zone is UTC+4:00 where as the slave is on UTC-5:00.
The master time zone is correct whereas slave timezone is incorrect.
Would like to know –
1) what will be the effect of this on fields of type "timestamp with timezone"?
2) what will be the effect of this on fields of type "timestamp without timezone"?
3) what will happen to replication if we change the slave timezone?
Thanks for your insights.
Best Answer
They're converted from their internal UTC representation to UTC-5 at the moment. They'll be converted using UTC+4 after. So applications will see a 9-hour time shift if they're ignoring the time zone. If they respect the time zone reported in the timestamp then there will be no effect.
One notable exception to this is where the timestamp is truncated to a date. The timestamp may fall on a different day in one time zone to in another time zone. Truncation of a timestamp with time zone to a date discards time zone information. So be cautious with
date_trunc
, casts fromtimestamp with time zone
todate
, calls toextract
ordate_part
, etc.Nothing.
Nothing. Replication is block level. It doesn't care about the timezone setting in the slightest.
This is just the same as changing
TimeZone
in the master with aSET TimeZone
at the SQL level, or in the config file. So you can just try it with a standalone DB.e.g.
Note that in both cases the timestamp is the same, because 16:48 at UTC+04 is the same as 12:48 at UTC or 20:48 at UTC+8 (Australia/West).
However, when the timezone shift causes the timestamp to be in the prior or next day in that time zone...:
it's all fine with the timestamps still, but the
timestamp with time zone
truncated todate
has moved to a different day.If you're confused by the offsets seeming backwards that's because PostgreSQL respects the POSIX standard for time zone offsets which are, infuriatingly, backwards to the offsets most people use day to day.