Problems while upgrading from 7.3.8 to 8.4.3

[tcoufal]

Hi,

We have had a major problems with upgrading ThingWorx version from version 7.3.8 to version of 8.4.3.

We tried the whole procedure on our TEST environment and had some problems. 

What we have done:

• Setting "run as user" for all Timers and Shedulers
• Performing all update scripts one by one (as per Upgrading to ThingWorx 8.4 document)
• All scripts went fine (no errors)
• Modified platformsettings.json
• Deploying new war
• While starting Tomcat an error occurred (ERROR: duplicate key value violates unique constraint "root_entity_collection_pkey") (https://community.ptc.com/t5/ThingWorx-Developers/Upgrade-to-Thingworx-8-0-Errors/m-p/510085)
• I had to perform following sqls:

  • delete from root_entity_collection where name = 'NotificationDefinitions';

  • delete from root_entity_collection where name = 'NotificationContents';

  • delete from root_entity_collection where name = 'StyleThemes';

• Note: I dont like that I have to modify underlying DB by hand, Upgrade scripts should handle this (check if exists)

• I had to repeat this step every time before ThingWorx fully loaded

• Finally THingWorx started 

• On TEST environment all timers and all scheduler were deactivated and all communication from Shopfloor (KepServerEX) was stopped 
•  

• So we have moved  to PROD environment

• Again duplicate key issue :/ needed to delete rows as before
• on PROD environment only communication from Shopfloor was deactivated, timers and schedulers were active
• While deploying WAR we have faced severe DB deadlock issues. 
• We sort of solved them by de-activating all timers and schedulers, so the platform will not be under huge amount of stress
• After ThingWorx started (it took more then 30 minutes) we started timers and schedulers
• We had huge amount of permission related errors "[message: Execution error in service script [Type.Thing:Entity.Clock_Timer:Event.Timer] :: Not authorized for ServiceInvoke on refreshStopwatch in thing]
  at Type.Thing:Entity.Clock_Timer:Event.Timer:2"
• Note: Run as User for every timer is set to user in Admin group
• Finally we ended up with Security Context stack getting large warnings and had to roll-back.

Any suggestions when it comes to upgrading PROD environment with lot of data and extend business logic?

What would be the correct procedure. 

Deactivating, stoping etc?

Would be better to do an incremental upgrade? I.e. from 7.3.8-7.4, 7.4-8.0, 8.1 - 8.2 etc..?

[zyuan1]

I would actually suggest you to install a new TWX 8.4 server, and then migrate all the data from old server to it, that would be much more efficient. Stop all the Timer and Scheduler during the process of course.

There has been so many changes between 7.4 to 8.4, licensing, composer, number of sql tables. Your concern maybe that the data inside Persistence Provider is very huge, and that would also lead you a way to improve the data structure and efficiency:  Use additional Persistence Provider to hold Stream/Datatable/VS data, and store large amount of data in external JDBC.

[tcoufal]

Yes I was thinking about that as well. 

Our problem is that we have about 300GB in DB (mostly streams). 

We have tried to do export one time and it failed (during normal operation).

We had to export it stream by stream and in a case of big stream we had to do a partial exports. 

We had problems even when importing model (entities) to new version of ThingWorx (dependencies are nightmare). 

Questions: 

Is there a way how to move data from DB to DB not involving composer? 

If yes, is better to upgrade DB (model and data schemes) before we move the data? 

If we decide now that we would like to split data (streams) into individual persistence providers can we initialize them with TW8.4 sql scripts if we have 7.3TW platform on tomcat?

How we move data from old DB to newly created PP? 

Thanks a lot.  

[zyuan1 · Accepted solution]

To keep the data safe, you may consider install a new PP, for example, you have pgSQL 9.5, you can install a 9.6 in the same or different environment, and share the same pgAdmin UI with different port number.

By doing this, you can easily roll back if migration didn't proceed well, and if it succeed, you can delete the old Database. (the upgrade of the PP is not reversible.)

You can always Use SQL command to export the data from PP directly, without going through composer. The data are always kept under same table name.

You don't need to separate the old data into 2 persistence provider now, additional persistence provider is a suggestion for your future operations. It gives you 2 cores, twice the storage space, and it can reconnect to a totally different server for data migration.

[tcoufal]

Is table scheme for data (VS, Streams, Datatables) still the same? I mean no differences between 7.3 and 8.4? 

I will check it myself, but cannot hurt to ask. 

We are using PG in HA mode, so we stopping the replication and using slave as backup. 

Worked just fine. But still pain in the ass when it is not working. 

[zyuan1]

In 8.4, 3 tables are called: data_table, Stream, Value_stream, so no big changes. And highly possible the columns also didn't change, only the number of tables varies.

[slangley]

Hi tcoufal.

If one of the previous responses allowed you to resolve your issue, please mark the appropriate one as the Accepted Solution for the benefit of others with the same issue.

Regards.

--Sharon