Implicit timezone conversion replicating from timestamp to timestamptz?

On 24/01/2019 21:57, Jeremy Finzel wrote:

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?

This only works by accident in pglogical because the binary
representations of both types are compatible in this sense. You're not
really supposed to do that.

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

This is what you get in built-in logical replication, because it goes
via textual representation.

To make it do what you actually wanted, set the time zone for the
subscription worker to UTC. The way to do that (could be easier) is to
create a separate user, use ALTER USER SET timezone = 'UTC', and create
the subscription as that user.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.