pglogical
pglogical copied to clipboard
2ndQuadrant
Is there any plan for supporting REPLICA IDENTITY FULL?
I can't add a table with REPLICA IDENTITY FULL in publication,but I need the old data in subcription for app to handle the data，so is there any plain to support this?
I am also curious why replica identity full isn't supported. Scenario:
At 10:00:00.001 row with PK pk123 is deleted from site A database. At 10:00:00.100 row with PK pk123 is updated on site B database.
Replication LAG is 1 second so both changes happened before replicated change arrived from the other site.
track_commit_timestamp = ON pglogical.conflict_resolution = last_update_wins.
Since the update happened after the delete the update should win. While testing this scenario the end result was the row was deleted from both site A and B.
In order to correctly handle this situation I believe you'd need REPLICA IDENTITY set to FULL so you get before values for all columns, not just the columns being updated along with the PK. The update that replicates to site A does not find the row so the update should get changed to an insert. Since REPLICA IDENTITY is set to FULL before values for all columns on the table should be available so there is enough information to change the update to an insert. The row should not get deleted on site B since the delete happened before the last update. End result is the matching row should exist on both sites.
Comments to this scenario?
There are currently no plans to support this.
PostgreSQL has a hard limit on the page size. This means that values that are larger than around 8 KBs need to be stored by using TOAST storage. This impacts replication messages that are coming from the database. Values that were stored by using the TOAST mechanism and that have not been changed are not included in the message, unless they are part of the table’s replica identity. There is no safe way for Debezium to read the missing value out-of-bands directly from the database, as this would potentially lead to race conditions. Consequently, That means i can't use any logical decoding plugin to handle the problem when i use pglogcal, because pglogical doesn't support REPLICA IDENTITY FULL, that make me can't transfer data to kafka,is there any solution to handel this problem?
Built-in logical replication supports REPLICA IDENTITY FULL.
But built-in logical replication doesn't support two-side replication,we want to build a bicentric data center.
For what it's worth, EDB provides Postgres Distributed for bi-directional replication that also supports REPLICA IDENTITY FULL. If that's what you meant by "bicentric", there's a purpose-built product made specifically for that scenario.
