I was trying to sync a table with Logical Replication. This needs to cover Insert, Update and Delete. However the replication only worked for Insert and not the rest.
As documented here
A published table must have a “replica identity” configured in order to be able to replicate UPDATE and DELETE operations, so that appropriate rows to update or delete can be identified on the subscriber side.
Here's the schema of the publishing table
CREATE TABLE mytest.point_a ( gid serial NOT NULL PRIMARY KEY, the_value numeric(10,2), name TEXT NOT NULL, geom geometry(Point,4326) ); CREATE INDEX test_point_a_geom_idx ON mytest.point_a USING gist (geom); ALTER TABLE mytest.point_a REPLICA IDENTITY DEFAULT;
The above table I was trying to replicate has the REPLICA IDENTITY clause and a serial integer column as Primary Key. The corresponding column in the subscribing table is just a plain bigint without Sequence.
Here's the subscribing table:
CREATE TABLE mytest.point_a ( gid bigint, --column order is different, this is on purpose name TEXT, address TEXT, --not exists in publishing geom geometry, --more flexible than the one in publishing the_value(10,2) ); --this one does not have index
All inserts are replicated but Updates and Deletes are not.
So what's missing here? Did I add REPLICA IDENTITY incorrectly?
Another relevant question: How do we know if a (publishing) table has REPLICA IDENTITY clause? In pgAdmin III and DBeaver, the DDL view of my publishing table in the above do not show any hint about it.