On Friday, 25 January 2019 04:57:15 UTC+8, Jeremy Finzel wrote:
We are working to migrate several large tables from the timestamp to the timestamptz data type by using logical replication (so as to avoid long downtime for type conversions). We are using pglogical but curious if what I share below applies to native logical replication as well.
Both source and destination dbs are at localtime, which is 'America/Chicago' time zone.
The source system has a timestamp stored "at time zone UTC", like this for 6:00pm Chicago time: 2019-01-24 20:00:00.000000
I was *very surprised* to find that replicating above timestamp to timestamptz actually does so correctly, showing this value in my psql client on the subscriber:
2019-01-24 14:00:00.000000-06
How does it know/why does it assume it knows that the time zone of the timestamp data type is UTC on the provider given that my clusters are at America/Chicago? I would have actually expected an incorrect conversion of the data unless I set the timezone to UTC on the way in on the subscriber via a trigger.
That is, I was expecting to see this:
2019-01-24 20:00:00.000000-06
Which is obviously wrong. So why does it do this and is there some assumption being made somewhere in the code base that a timestamp is actually saved "at time zone UTC"?
pglogical is replicating the timestamp text, which is converted on both output and input.
