I'm testing out Logical replication on Postgresql (10.4 at the moment) with a quite specific use case.
I have some central DB (publisher) from where I need to replicate few tables to distributed DB's (subscribers) (across different servers).
This is the scenario:
- Table in Master DB changes (gets new column). Lets say that this new column is of type "timestamp without timezone", not null, with default value of now().
- Because of 1) replication stops working, which is fine. (
logical replication target relation <relation> is missing some replicated columns)
- After some time, distributed DBs also get the same change (new column).
- At this point, replication starts working again and new rows are replicated, including the data from new column (for new rows).
- However, for existing rows, the values (timestamps) from new column in master DB table and replicated DB table are different (by time between kicking off the migration on master table and replica tables) and do not get updated up until row changes in Master DB/source table.
My question: is there a way to "force" update new column data replication without truncating table data, updating all rows in source table or similar very time&resource consuming operations?
Also, performance related note – there will be 1-10 million rows in one of tables that is being replicated.
I'm open to all suggestions&ideas.