topic Migrating from InfluxDB to PostgreSQL in ThingWorx Developers

Migrating from InfluxDB to PostgreSQL

sabharees — Fri, 22 Dec 2023 20:27:50 GMT

I have a list of streams (InfluxDB as persistence provider) with data in it, and I would like to migrate to Postgres (the default persistence provider)

Now, how can I proceed further? Does Thingworx provide any migration tools?
If I use the export data option, which is not working,

Re: Migrating from InfluxDB to PSQL

Rocko — Fri, 24 Nov 2023 09:53:56 GMT

If there is an official tool, PTC support will know and be able to provide the link. most probably, this will be a manual effort, but you should create a ticket with support.

Re: Migrating from InfluxDB to PSQL

VladimirRosu — Fri, 24 Nov 2023 10:32:58 GMT

I suggest as @Rocko below opening a support ticket, because the Export Data option should be the thing to use here.

Out of curiosity, what exactly is not working?

Re: Migrating from InfluxDB to PSQL

sabharees — Fri, 24 Nov 2023 11:04:25 GMT

If I export the data from streams using the Export Data option, and then change the persistence provider to Thingworx PP from Influx PP,
I get a successfully imported data message in the application log but am not able to see any data in the stream.

Re: Migrating from InfluxDB to PSQL

VladimirRosu — Mon, 27 Nov 2023 11:37:10 GMT

I saw the fact you specified "successfully imported" but I did not see that you imported - is that a typo?.

Specifically the steps should be:

1. Export data from Stream using Export Data

2. Change the Stream's PP (Restart the Stream after changing the PP)

3. Import back the data

Re: Migrating from InfluxDB to PSQL

sabharees — Mon, 27 Nov 2023 11:56:35 GMT

I followed the steps, but the issue persists. I can see only one row(total rows - 15k approx) imported after receiving an import successful message in the application logs.

Re: Migrating from InfluxDB to PSQL

VladimirRosu — Mon, 27 Nov 2023 12:02:56 GMT

1. How did you try to export data? Stream by stream, or for all Streams?

2. How long does it take to export data for a single Stream? InfluxDB PP has a read timeout that is closing a connection to it in case it waits to retrieve data for more than that period. It's the Read timeout, set by default at 10 seconds.

3. How large is the data export file for a Stream you know it should have 15k rows?

Re: Migrating from InfluxDB to PSQL

sabharees — Mon, 27 Nov 2023 12:07:27 GMT

I tested the functionality with one stream and increased the read and write timeout to 30 seconds.

Backups show a file size of 453 bytes for the data-0.twx file.

Re: Migrating from InfluxDB to PSQL

VladimirRosu — Mon, 27 Nov 2023 12:09:18 GMT

Increase them to more than 30 seconds (60-120 etc, step by step). The data-0.twx is definitely small for 15k rows.

Re: Migrating from InfluxDB to PSQL

sabharees — Mon, 27 Nov 2023 12:22:07 GMT

I increased the timeout to 300 seconds, but still, the issue persists even for the stream having 550 rows.

Re: Migrating from InfluxDB to PSQL

VladimirRosu — Mon, 27 Nov 2023 12:35:11 GMT

If you look in the Influx logs and ThingWorx Application Log, isn't there any error?

What version of Influx are you using?

Re: Migrating from InfluxDB to PSQL

sabharees — Mon, 27 Nov 2023 12:46:12 GMT

I use influx OSS 2.7 in my EC2 instance.
Also, I can see some errors regarding stream queues.