TimescaleDB corruption duplicate key violates constraint issues

[jongunz]

I'm new to timescaleDB and postgres, but we have had a large instance of zabbix up for several months and I went out of town two weeks ago and came back to these errors.


5456:20230828:164426.109 syncing history data... 0.127774%
5456:20230828:164426.135 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(23260, 1693255060, 226181078) already exists.

5456:20230828:164426.504 skipped 605 duplicates
5456:20230828:164426.606 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(890562, 1693255062, 766538) already exists.

5456:20230828:164426.953 skipped 12 duplicates
5456:20230828:164427.156 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(890563, 1693255063, 1179170) already exists.

5456:20230828:164427.514 skipped 4 duplicates
5456:20230828:164427.674 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(2143702, 1693255064, 637573444) already exists.

5456:20230828:164427.992 skipped 5 duplicates
5456:20230828:164428.154 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(890565, 1693255065, 877404) already exists.

5456:20230828:164428.464 skipped 77 duplicates
5456:20230828:164428.578 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(65865, 1693255065, 822837268) already exists.

5456:20230828:164428.756 skipped 375 duplicates
5456:20230828:164428.893 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(201466, 1693255066, 1241109) already exists.

5456:20230828:164429.251 skipped 15 duplicates
5456:20230828:164429.376 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2014_1027_history_pkey"
DETAIL: Key (itemid, clock, ns)=(23266, 1693255066, 235019723) already exists.

5456:20230828:164429.676 skipped 19 duplicates
5456:20230828:164430.036 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2013_1026_history_uint_pkey"
DETAIL: Key (itemid, clock, ns)=(295967, 1693255067, 1810930) already exists.

5456:20230828:164430.252 skipped 1 duplicates
5456:20230828:164430.600 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR: dup
licate key value violates unique constraint "2013_1026_history_uint_pkey"
DETAIL: Key (itemid, clock, ns)=(295968, 1693255068, 748232) already exists.

5456:20230828:164430.921 skipped 1 duplicates
5456:20230828:164532.037 cannot connect to service manager service: Cannot connect to service "service": [111]
Connection refused.


When I try to restart the system comes up for a bit and then almost immediately dies again with an error that a duplicate key exists. I thought I could just clean out the duplicate data but it's compressed and will not let me remove it. I'm not a DBA so my skill set is limited with postgresql and timescaleDB.

[lmonasterio]

Hi @jongunz! Did you find a solution for this? I have the same error...

[jongunz]

Nope. I can't remember exactly but I have had to on a few occasions simply wipe and rebuild our system due to database corruption. If you had a backup I would revert back. I would check your logs as well for any possible configurations that may need to be increased I had a few on my end that were missed due to the scrolling DB errors.