NetXMS Support Forum

Hello,

looks like I've got the next problem.
In the event log of the server I find the following entries:

Database query failed (Query: SELECT drop_chunks(to_timestamp(1628553600), 'tdata_sc_default'); Error: TS001 ERROR:  "tdata_sc_default" is not a hypertable or a continuous aggregate view
HINT:  It is only possible to drop chunks from a hypertable or continuous aggregate view)

I use, at least I thought I did, a TimescaleDB. During initialisation, it was also specified as such, and Timescale is also displayed as an extension in the database.

In timescaledb_information.hypertable, however, no tables are displayed, are these not used by Netxms?
If not, how can the old (or all) data be removed, the DB is currently over 500GB in size.

Thanks in advance

Andy

What's exactly version of Netxms? What version of Postgres and Timescale addon? Is Timescale addon actually installed?

If you connect to your Postgres database, what's the output of:

\d+ tdata_sc_default;


Hello Filipp,

Here is the requested data:

• OS: Debian 9 (Kernel 4.9.0-15-amd64)
• Netxms: 3.8.405-1
• PostgreSQL: 11.12-1.pgdg90+1
• TimescaleDB: 1.7.5~debian9

Timescale seems to be installed:

SELECT default_version, installed_version FROM pg_available_extensions where name = 'timescaledb';
default_version | installed_version
-----------------+-------------------
1.7.5           | 1.7.5


\d+ tdata_sc_default;
                                           Table "public.tdata_sc_default"
     Column      |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
-----------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
item_id         | integer                  |           | not null |         | plain    |              |
tdata_timestamp | timestamp with time zone |           | not null |         | plain    |              |
tdata_value     | text                     |           |          |         | extended |              |
Indexes:
    "tdata_sc_default_pkey" PRIMARY KEY, btree (item_id, tdata_timestamp)
    "tdata_sc_default_tdata_timestamp_idx" btree (tdata_timestamp DESC)


Hmm, so what could be the problem?

Thanks

On my system
SELECT * FROM timescaledb_information.hypertable;
shows the following:

table_schema |         table_name          | table_owner | num_dimensions | num_chunks | table_size | index_size | toast_size | total_size
--------------+-----------------------------+-------------+----------------+------------+------------+------------+------------+------------
public       | idata_sc_30                 | netxms      |              2 |         22 | 2304 kB    | 1056 kB    | 176 kB     | 3536 kB
public       | idata_sc_default            | netxms      |              2 |       1398 | 238 MB     | 110 MB     | 11 MB      | 359 MB
public       | idata_sc_90                 | netxms      |              2 |          0 |            |            |            |
public       | idata_sc_180                | netxms      |              2 |          0 |            |            |            |
public       | tdata_sc_default            | netxms      |              2 |         15 | 228 MB     | 528 kB     | 120 kB     | 228 MB
public       | tdata_sc_7                  | netxms      |              2 |          0 |            |            |            |
public       | tdata_sc_30                 | netxms      |              2 |          0 |            |            |            |
public       | tdata_sc_90                 | netxms      |              2 |          0 |            |            |            |
public       | tdata_sc_180                | netxms      |              2 |          0 |            |            |            |
public       | snmp_trap_log               | netxms      |              1 |          0 |            |            |            |
public       | idata_sc_other              | netxms      |              2 |          0 |            |            |            |
public       | tdata_sc_other              | netxms      |              2 |          0 |            |            |            |
public       | event_log                   | netxms      |              1 |         17 | 4584 kB    | 1304 kB    | 136 kB     | 6024 kB
public       | win_event_log               | netxms      |              1 |          0 |            |            |            |
public       | server_action_execution_log | netxms      |              1 |         14 | 168 kB     | 672 kB     | 112 kB     | 952 kB
public       | notification_log            | netxms      |              1 |         13 | 104 kB     | 624 kB     | 104 kB     | 832 kB
public       | syslog                      | netxms      |              1 |          0 |            |            |            |
public       | idata_sc_7                  | netxms      |              2 |         12 | 96 kB      | 384 kB     | 96 kB      | 576 kB
(18 rows)


idata and tdata (7, 30, 90, 180, default) tables should be there. You mentioned that there's no tables there on your system, that probably means that something went wrong when initializing the database.

I'd probably create a new database, initialize it and check that above tables are there.
After database creation you may need to execute that query to initialize timescale:
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
and then run nxdbmgr init.

Once you have new database you can use
nxdbmgr migrate
to copy from old database to the new one. You can omit collected data and logs by adding e.g. these keys:
-s -Z alarm -Z audit -Z event -Z snmptrap -Z syslog -Z winevent
(run nxdbmgr -h to see full list of keys).

This should happen fairly fast and after that you can start netxms. You will have all configuration in place, but no historical data and logs.
Then it depends on whether you need the historic data and logs. You can copy these in the background while netxms is running.

nxdbmgr does not currently support an option to select time range. If you need to copy part of your data, you either need to use some other tool, or you can try delete data older then some timestamp and then copy data.

Hello,

It took me a while, but I was on holiday.
With the described procedure everything worked without problems, now the hypertable is also filled.

But I still have one question: how can I copy the logs in the background? Also with nxdbmgr or directly in the database?

Many thanks Filipp, really a great help


Andy

nxdbmgr should be able to copy logs and collected data. The commands are like this:

"nxdbmgr -S -L audit -L event -L snmptrap -L syslog -L winevent migrate netxmsd.old.conf" - this will copy over all remaining logs

"nxdbmgr -D migrate netxmsd.old.conf" - this will copy historical data for all DCIs