The following bug has been logged on the website:
Bug reference: 15304
Logged by: Vasilis Ventirozos
Email address: v.ventirozos@gmail.com
PostgreSQL version: 9.6.3
Operating system: ubuntu xenial
Description:
Hey all,
While testing a migration procedure on a database (v9.6.9) from floating
point datetimes to int64 (v9.6.9) using pglogical i saw that after the
initial data sync all the subsequent replicated rows containing dates are
getting nonsensical datetime values in the subscriber.
I've read:
https://www.postgresql.org/message-id/flat/26788.1487455319%40sss.pgh.pa.us#26788.1487455319@sss.pgh.pa.us
So I know that this is not something new, but i was wondering if there is
any way around this, other than just use slony (that works fine). For
example if there was a pattern, i could possibly translate the "wrong" dates
on the subscriber using a trigger. An interesting observation is that date
'2000-01-01 00:00:00' was actually transferred fine, while '1999-01-01
00:00:00' made the sync worker to error out with "date out of range".
Some examples bellow :
id | mydate | should_be |
----+------------------------------+-------------------------------+
72 | 2018-01-01 00:00:00 | Initial row before replication |
73 | 2000-01-01 00:00:00 | 2000-01-01 00:00:00 |
74 | 151547-03-14 20:46:33.353216 | 2001-01-01 00:00:00 |
75 | 151689-07-17 15:26:49.024512 | 2002-01-01 00:00:00 |
76 | 151765-01-03 19:04:55.441408 | 2003-01-01 00:00:00 |
77 | 151832-01-28 12:43:50.545408 | 2004-01-01 00:00:00 |
78 | 151874-03-11 10:09:15.259904 | 2005-01-01 00:00:00 |
79 | 151907-09-22 18:58:42.811904 | 2006-01-01 00:00:00 |
Best Regards,
Vasilis Ventirozos
OmniTI Computer Consulting Inc.