I'm trying to migrate NetXMS from a physical host to an LXC container and also upgrade to TimescaleDB.
I installed NetXMS 5.1.3 in the container and initialized a postgres 15 database with timescaledb, then I upgraded the existing host to the same version and stopped the service in both locations.
Then I run
nxdbmgr migrate old_netxmsd.conf which started the migration, but failed with a Windows Event log:
Migrating table win_event_log
SQL query failed (22001 ERROR: value too long for type character varying(2000)):
INSERT INTO win_event_log (id,event_timestamp,node_id,zone_uin,origin_timestamp,log_name,event_source,event_severity,event_code,message,raw_data) VALUES (?,to_timestamp(?),?,?,?,?,?,?,?,?,?)
Failed input record:(Truncated the event data, but it was also printed.)
I thought about removing records in the source db which violate the new constraints to complete the migration, but what about new records that netxms will attempt to insert in the new db?
What should I do in this case?
Something strange happening here. Can you connect to the DB using postgres client and show output of
\d win_event_log
on the old and new db?
I am not sure, if the issue is related to normal postgres and timescale difference, but you can try migrating the DB in postgres format and then converting it (nxdbmgr now supports in-place conversion to timescale)
Old db:
netxms=# \d win_event_log
Table "public.win_event_log"
Column | Type | Collation | Nullable | Default
------------------+-------------------------+-----------+----------+---------
id | bigint | | not null |
event_timestamp | integer | | not null |
node_id | integer | | not null |
zone_uin | integer | | not null |
origin_timestamp | integer | | not null |
log_name | character varying(63) | | |
event_source | character varying(127) | | |
event_severity | integer | | not null |
event_code | integer | | not null |
message | character varying(2000) | | |
raw_data | text | | |
Indexes:
"win_event_log_pkey" PRIMARY KEY, btree (id)
"idx_win_event_log_node" btree (node_id)
"idx_win_event_log_timestamp" btree (event_timestamp)New db:
netxms=# \d win_event_log
Table "public.win_event_log"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
id | bigint | | not null |
event_timestamp | timestamp with time zone | | not null |
node_id | integer | | not null |
zone_uin | integer | | not null |
origin_timestamp | integer | | not null |
log_name | character varying(63) | | |
event_source | character varying(127) | | |
event_severity | integer | | not null |
event_code | integer | | not null |
message | character varying(2000) | | |
raw_data | text | | |
Indexes:
"win_event_log_pkey" PRIMARY KEY, btree (id, event_timestamp)
"idx_win_event_log_node" btree (node_id)
"idx_win_event_log_timestamp" btree (event_timestamp)
Triggers:
ts_insert_blocker BEFORE INSERT ON win_event_log FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()I also checked the length of the problematic record in the old db:
netxms=# select length(message) from win_event_log where id = 7335338;
length
--------
2000
(1 row)
I tried the normal to normal migration but it failed with the same error.
Additional info:
Old db version:
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.15 (Ubuntu 14.15-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)New db version:
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.10 (Debian 15.10-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
I used pg_dump and psql to migrate the database to the new location, which was successfull. Currently I'm waiting on the timescale conversion to complete.
Edit: TimescaleDB inplace migration also completed successfully. NetXMS is now running on the new host.
Yep, perfectly valid approach, nice to hear that it all worked.
We were not able to replicate the original issue so far, can you please check what encoding your original database has. If you connect to the db using psql, issue
\l+
command there, it should list the databases, we are interested in value in Encoding column.
Hello!
Is it possible to show (or post to me via PM) exact output of "failed record" message?
Best regards,
Victor
The old server has already been shutdown, but I'll try my best to get the info from it this week. I'll be able to post the entire record as-is since it didn't contain any sensitive data iirc.
I no longer have the failed record error message, but in the original post you can see the start of the message, it just continued with all the fields of the failed record.
Attached the database list and two binary files created with psql copy, one is just the message value, the other is the entire record.