Migration data from thingworx to external Postgres

[rkandasamy]

we want to move some thingworx datatable data to external postgres database table and we have below queries.

1. I have a datatable schema with 10 columns. How to create the same schema in external Postgres database (Note: Using Database template, Configuration section pointing to external postgress db now) ? I have to create manually in the target postgres datatable or any way to pull the same schema available in Thingworx datatable to external postgress table and create the same table there?

2. I have 10 records in thingworx Test Table. How to copy the all records to Test table in external postgres database.? Below steps specifies moving record by record. But i         just want to know if there is a way to move the whole records.

     Steps:   

          1. call GetDataTable entries from thingworx which result infotable of 10 records

          2. Loop through the info table of 10 records, call a sql insert command of database thing which inserts single record each time it is called

Please provide your inputs.

[supandey]

Hi Rdhakrishnan,

If i have understood your use case correctly then you can try to explore and use the pg_dump utility, here's quick link for the same PostgreSQL: Documentation: 9.1: pg_dump , the way i see it can save you time in creating scripts and schema and so forth. Let me know if you had something else in mind.

Though the question is do you intend to then connect the new schema as well to a different ThingWorx installation?

[rkandasamy]

Hi Sushant Pandey,

Thanks for your reply. But I guess, you are pointing to link which describes the way to take backup in stand alone Postgres DB.

I am trying to migrate the existing data from thingworx datatables(postgres version) to external postgres database. I am stuck with two queries.

1.Should i create the source table schema in the target postgres manually or can i somehow pull the schema from thingworx datatable?

2. How can i migrate 100 records from say TESTTABLE a thingworx datatable to external postgres datatable PGTESTTABLE. if you see, initially PGTESTTABLE does not exist. I am looking for a way pull the existing schema from thingworx and also move that 100 records from thingworx to this external postgres DB table.

[supandey]

pg_dump will allow you to export the data directly out of your existing postgresql and then dump/import it in your external postgresql. Note that with this you can output script as text containing SQL commands to reconstruct the DB. In short this should allow you to meet your point 1 and 2 both without having to create service in ThingWorx to loop through the data just so you can move it to your new installation.

Like I said using this utility or not will depend what you plan to do with your new schema - as in what's your final goal/purpose of doing this. Is it only for backups or you plan to create a cluster with such a setup?

Hope this helps.

Edited:

Anyway, in either case you will have your data in the new postgresql which you can use later as you wish. BTW, i am referring to migrating data from your existing DB to a new DB (i.e. on DB level alone). Though another look at your question, it sounds like you only want to do this from the composer? Sorry I think i'm bit confused now since you are using Datatable and schemas interchangeably with the vocabulary that of a DB

[rkandasamy]

Thingworx stores data in Datatable and its fields are defined using thingworx datashape.

In postgres, we call the datashape as scehma(Generally). So i have two needs.

1. If i need to pull any particular table structure from Thingworx datatable, and create that table alone in external postgress DB(Could be accessed from PGadmin client), i should be able to do that.

2. If i need to pull data of that particular table from thingworx, and add to the newly created external postgres DB, i should be able to do that.

I am trying to figure out how this can be done.  If any body has tried this before, Please help on this.

[jkaczynski]

Hi Rdhakrishnan,

If you just want to move your data from Thingworx Data Table to other source (any DB), you need to create or find Connector with drivers to the specific external DB (e.g. using https://marketplace.thingworx.com/Items/jdbc-connector-extension this extension). Then you can use normal services to get data from Data Table (QueryDataDataTableEntries or GetDataTableEntries, no matter) and insert them to your DB (if you have a large number of rows, preferably using bulk insert, if supported).

Using the Connector you would be able also to send SQL command, so at first you can create a service that gets Data Shape of your Data Table and on its basis creates a SQL command CREATE TABLE to set up a DB schema.

Hope it helps, in case of any questions, don't hesitate to ask.

Regards,

J.