In our company we have use logical replication (on both servers source and receiver we have Postgresql 10.5 version), and sometimes logical replication is down on error :
ERROR: duplicate key value violates unique constraint.
In pg_replication_origin_status i see position remote_lsn (its a position on source up to which the receiver reach). I know how to use pg_replication_origin_advance, and i know that this function may skip some data. Is there a solution to get the next position after remote_lsn so that the data is not lost ?
steve :
Have you actually determined which table is causing this. This is generally caused by tables that use sequences in their table definition. Sequences are not replicated in PostgreSQL. The destination can try to start the sequence from scratch and generate a value of 1 for the sequence. This is because logical replication does not replicate the values for sequences since the next value of the sequence is not stored in the table itself.\n\nWorkaround:\n\n\nUse an external source for the number like zookeeper\nUse non-overlapping ranges for different servers\n",
2020-01-01T21:55:18