Migration from PostgreSQL to TimescaleDB failed

[pvo]

I've tried to migrate the data form PostgreSQL format to TimescaleDB. But it failed with following error messages:

# nxdbmgr migrate /etc/netxmsd.conf.pgsql
NetXMS Database Manager Version 3.3.340 Build 3.3-340-gdde893b7a5 (UNICODE)

Source:
        Driver: pgsql.ddr
        DB Name: netxms
        DB Server: 127.0.0.1
        DB Login: netxms

Target:
        Driver: pgsql.ddr
        DB Name: timescale
        DB Server: 127.0.0.1
        DB Login: netxms

Options:
        Skip audit log.............: no
        Skip alarm log.............: no
        Skip event log.............: no
        Skip syslog................: no
        Skip SNMP trap log.........: no
        Skip collected data........: no
        Skip data collection schema: no

Confirm database migration? (Yes/No) y
Database driver pgsql.ddr loaded
Connected to source database


WARNING!!!
This operation will clear all configuration and collected data from destination database before migration.
Are you sure? (Yes/No) y
Database successfully cleared
Migrating table config
Migrating table config_clob
Migrating table config_values
Migrating table users

...

Migrating table idata_160 to idata
Migrating table tdata_160 to tdata
Migrating table idata_164 to idata
ERROR: unable to insert data to destination table (23505 ERROR:  duplicate key value violates unique constraint "2161_2161_idata_sc_default_pkey"
DETAIL:  Key (item_id, idata_timestamp)=(1227, 2020-05-19 02:01:11+02) already exists.)
ERROR: unable to insert data to destination table (23505 ERROR:  duplicate key value violates unique constraint "2161_2161_idata_sc_default_pkey"
DETAIL:  Key (item_id, idata_timestamp)=(1227, 2020-05-19 14:25:13+02) already exists.)
ERROR: unable to insert data to destination table (23505 ERROR:  duplicate key value violates unique constraint "2161_2161_idata_sc_default_pkey"
DETAIL:  Key (item_id, idata_timestamp)=(1227, 2020-05-19 05:13:12+02) already exists.)

...

ERROR: unable to insert data to destination table (23505 ERROR:  duplicate key value violates unique constraint "2721_2721_idata_sc_default_pkey"
DETAIL:  Key (item_id, idata_timestamp)=(1227, 2020-05-28 22:45:14+02) already exists.)
ERROR: unable to insert data to destination table (23505 ERROR:  duplicate key value violates unique constraint "2721_2721_idata_sc_default_pkey"
DETAIL:  Key (item_id, idata_timestamp)=(1227, 2020-05-29 01:59:14+02) already exists.)
Database migration failed.

The server version is 3.3.340 on Debian.
Did I do something wrong or PostgreSQL uses different keys than TimescaleDB?
How can I correct the problem?

[victor]

Hi,

it looks like duplicate records in source table, although it is quite strange because PostgreSQL schema should have timestamp as part of primary key as well. Please try to run query

SELECT * FROM idata_164 WHERE item_id=1227 AND idata_timestamp=1589846471;

on original database - how many records it will produce?

Best regards,
Victor

[pvo]

It returns 2 rows.

The table idata_160 has no primary key. Better to say that none of the idata_??? tables has a primary key. It is defined only on the idata table.
The idata_??? tables have item_id and idata_timestamp index only.

[pvo]

I think deleting the duplicate records will solve the problem.
Can I do it or will you need same more tests on my DB?

[victor]

Yes, this should solve the issue. Duplicate records are definitely a bug, but keeping them will not help in debugging it anyway.

Best regards,
Victor