Postgresql skip transaction in logical replication

user_10810041 0
Viewed 734

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 ?

postgresql
2 Answers

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.

Workaround:

  • Use an external source for the number like zookeeper
  • Use non-overlapping ranges for different servers
user_1210350

This is because the way the sequences in PostgreSQL work:

Consider this table at the source, having a sequence:

src=# CREATE TABLE s (a serial PRIMARY KEY, b text);
CREATE TABLE
src=# INSERT INTO s (b) VALUES ('foo'), ('bar'), ('baz');
INSERT 0 3
src=# SELECT * FROM s;
 a |  b
---+-----
 1 | foo
 2 | bar
 3 | baz
(3 rows)

src=# SELECT currval('s_a_seq'), nextval('s_a_seq');
 currval | nextval
---------+---------
       3 |       4
(1 row)

The sequence s_a_seq was created to back the a column, of serial type. This generates the autoincrementing values for s.a. Now let’s replicate this into dst, and insert another row:

dst=# SELECT * FROM s;
 a |  b
---+-----
 1 | foo
 2 | bar
 3 | baz
(3 rows)

dst=# INSERT INTO s (b) VALUES ('foobaz');
ERROR:  duplicate key value violates unique constraint "s_pkey"
DETAIL:  Key (a)=(1) already exists.
dst=#  SELECT currval('s_a_seq'), nextval('s_a_seq');
 currval | nextval
---------+---------
       1 |       2
(1 row)

Oops, what just happened? The destination tried to start the sequence from scratch and generated a value of 1 for a. 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.

Workaround

If you think about it logically, you can’t modify the same “autoincrement” value from two places without bidirectional synchronization. If you really need an incrementing number in each row of a table, and need to insert into that table from multiple servers, you could:

  • use an external source for the number, like ZooKeeper or etcd,
  • use non-overlapping ranges – for example, the first server generates and inserts numbers in the range 1 to 1 million, the second one in the range 1 million to 2 million, and so on.
user_120250170
Related