Guidance Needed for Switching ThingWorx Database to a New Postgres Instance

[leekhil_kataray]

Hi ThingWorx Community,

I’m currently running ThingWorx version 9 (ThingWorx development.server.build-b12466) with a Postgres database. I want to switch to a new Postgres database hosted on RDS.

Here’s what I’ve done so far:

1. Created a new RDS instance.

2. Added a database named thingworx.

3. Updated the platform-settings.json file with the new database connection details.

However, when I start ThingWorx, I encounter the following error:

CRITICAL ERROR ON STARTUP: Unable to initialize and start system: ERROR: current transaction is aborted, commands ignored until end of transaction block

I’m trying to understand:

• Is updating the platform-settings.json file the correct method to point to a new Postgres database?

• Are there additional steps I need to perform to migrate ThingWorx to a new database instance?

• Could this error indicate a problem with the database setup, migration, or connection configuration?

Any guidance or recommended steps for safely switching the database would be greatly appreciated.

Thanks in advance!

[Rocko]

Well it looks like you just created the schema, but it's empty, none of the tables ThingWorx expects are there. You would have to run the db creation scripts for those to be created. They come with the Thingworx platform download.

[leekhil_kataray]

Hi Rocko 
Where can i get DB creation scripts in side Thingworx folder, below is screenshot of items inside Thingworx folder.

[Rocko]

You need the Zip file you downloaded from PTC, or re-download it. Inside you will find the install folder.

[leekhil_kataray]

I have only installer file inside the zip please check blow screentshot

[Rocko]

Drill down one more folder.

[leekhil_kataray]

This is the only file inside my zip file and that too it is of type Application so i cannot drill down into Application type files.

[Rocko]

Ah, well, that's the installer version. Apologies, I never use that. Well then how about running the installer again into a different directory?

Other than that you would have to create a ticket with support to get a download of the 9.1.2 Thingworx platform-postgres to get hold of the installer scripts. This is necessary since 9.1.2 is long out of support and therefore not available anymore in the normal download.

I would recommend though not to start with a version that old but switch to a supported version.

This depends a bit if you want to start fresh or take over data from your existing system, this wasn't exactly clear in your posting.

In any case I do recommend following the installation/upgrade guidelines described in Help Center and in the Knowledge Base on support.ptc.com. Read this before you start and plan the steps accordingly.

[leekhil_kataray]

Currently, purging data from the value_stream table does not release disk space, and we need to run a VACUUM FULL table_name command to reclaim it. To address this, our plan is to test partitioning on the value_stream table.

We will create a partitioned version of the table in a new RDS instance, load two months of data, and then test the application against it. If everything works as expected, reclaiming disk space will be much easier since we can simply drop partitions instead of running VACUUM FULL.

Once the testing is successful, we will implement the same approach in production.

[Rocko · Accepted solution]

You mean like it is suggested here? https://support.ptc.com/help/thingworx/platform/r10.0/en/#page/ThingWorx/Help/ModelandDataBestPractices/PartitioningvaluestreamtableinPostgreSQL.html

Why don't you start by cloning your existing schema, which has all the tables into the new instance? In that case Thingworx would find all the tables it needs?

[leekhil_kataray]

Thank you, Rocko.

I have tried the approach mentioned above, and ThingWorx has started successfully.

The next step I am planning is to take the schema along with the data, but excluding the value_stream table data. Right now, I have only imported the schema, so I don’t have my old entities yet, as we haven’t cloned including the schema.

I will try cloning while excluding the value_stream data and will provide an update on that.

Thanks

[leekhil_kataray]

Hi Rocko 
I wanted to provide an update regarding the cloning process excluding the value_stream table. I’m happy to report that it was completed successfully.