Disclaimer: The scripts and content published here are provided solely as a courtesy to PTC customers. Each script is provided "as is", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In no event shall the PTC be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the scripts, including any software vulnerabilities.
As you know, ThingWorx generates lots of IoT data that can be stored and persisted in many storage options, including PostgreSQL.
PostgreSQL is a strong, open-source, object-relational database. In today’s post, we’ll share how you can configure ThingWorx with an Azure Database for PostgreSQL.
An Azure Database for PostgreSQL provides a fully managed, enterprise-ready PostgreSQL database as a service that delivers:
- Built-in high availability with no additional cost
- Predictable performance, using inclusive pay-as-you-go pricing
- Scale as needed within seconds
- Secured to protect sensitive data at-rest and in-motion
- Automatic backups and point-in-time-restore for up to 35 days
- Enterprise-grade security and compliance
In short, PostgreSQL is super helpful tool to store and persist your IoT data. If you think it’s as cool as we do and you’re ready to get started, check out the steps in the guide below, created by Desheng.
But first, before we dive too far in, please note the following assumptions:
- This installation guide assumes your ThingWorx will be running on Ubuntu 16.04.
- We do not intend to instruct you on how to install ThingWorx in this guide. (We’ve included links at the end of the post if you’re looking for guidance on ThingWorx installation.)
- This guide is intended to be a sample. For security best practices around passwords, certificates, ports, etc., please consult ThingWorx documentation.
- Both "dxu-hademo" and "dxu-postgresdemo" appear in screenshots throughout the steps. They are interchangeable in this guide.
Now, we’re ready for the fun stuff with the following steps.
Install PostgreSQL Client
On an Ubuntu 16.04 server where you have ThingWorx installation files, please perform the following steps to install the PostgreSQL client component:
sudo sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list" wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get install postgresql-client-9.6
Start Azure Database for PostgreSQL Service
You can start to create an Azure Database for a PostgreSQL service now. Please don't select the "Basic" offering because it will not allow you to specify your vNet.
(Please also note that the configuration illustrated below is quite minimal, as it is intended for a cost-effective scenario. Please select the appropriate size based on your needs.)
For the server admin username, use postgres.
Setup Network Security
Now, select "Adding existing virtual network" and create a rule to allow your current vNet components to access this database service.
Obtain Two Parameters
Please note the server name and server admin login name. Please also note that the admin login name is different from the admin user.
Prepare for SSL Connection
It is strongly recommended to enable SSL, which Azure has enabled by default. Since the release of version 24.2.0 of the PostgreSQL JDBC driver, the SSL configuration check is enabled as well, so it's better to set up SSL from the beginning.
The current ThingWorx PostgreSQL JDBC Driver uses version 24.2.0. For version 24.2.0, set ssl=true; for version 24.2.5, set ssl=verify-full; this will be the default configuration in this guide.
The certificate that you’ll need to communicate over SSL with your Azure Database for the PostgreSQL server is located at: https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt. Please download it to your Ubuntu machine.
The downloaded certificate is named BaltimoreCyberTrustRoot.crt. Please ensure you have OpenSSL installed on your Ubuntu machine.
openssl X590 -inform DER -in BaltimoreCyberTrustRoot.crt -text -out root.crt
Now, it's time for you to use either of the following psql commands to verify your connection:
psql "sslmode=verify-ca sslrootcert=root.crt host=dxu-hademo.postgres.database.azure.com dbname=postgres user=postgres@dxu-hademo"
or:
psql -h dxu-hademo.postgres.dataase.azure.com -d postgres -U postgres@dxu-hademo
You should now be successfully connected to your database. Let's prepare for the ThingWorx connection. Run:
sudo cp root.crt /ThingworxPlatform/root.crt sudo chown tomcat:tomcat /ThingworxPlatform/root.crt sudo chmod 600 /ThingworxPlatform/root.crt
For the ThingWorx Connection string, use:
"jdbcUrl": "jdbc:postgresql://dxu-hademo.postgres.database.azure.com:5432/thingworx?sslmode=verify-full&sslrootcert=/ThingworxPlatform/root.crt"
Set Up Role in PostgreSQL
Great work so far.
Now, let’s log in to the Azure Database for the PostgreSQL service as an admin by using:
psql -h dxu-hademo.postgres.dataase.azure.com -d postgres -U postgres@dxu-hademo
You can create a new role in PostgreSQL and then quit by typing:
create user twadmin with password '<insert your password here>' \q
Copy Scripts File
From the “Attachments” section, unzip azure-postgres-script.zip and put all 5 files into the ThingWorx install folder. Your folder structure should look like the image below:
Create Database and Schema
Next, note the new parameter below, -l , which stands for login.
./thingworxAzurePostgresDBSetup.sh -h dxu-postgresdemo.postgres.database.azure.com -d thingworx -a postgres -u twadmin -l postgres@dxu-postgresdemo
Your PostgreSQL service admin password is required for this step.
./thingworxAzurePostgresSchemaSetup.sh -h dxu-postgresdemo.postgres.database.azure.com -d thingworx -u twadmin -l twadmin@dxu-postgresdemo
The password for the role of twadmin is required for this step; the password is the one you provided in the step above in “Set Up Role in PostgreSQL”.
Configure platform-settings.json File
Now, go to platform-settings.json under the /ThingworxPlatform folder and update the ConnectionInformation section.
The username should be:
twadmin@dxu-hademo.postgres.database.azure.com
Alternatively, if you don’t choose the verify-full option, your username can be simplified to:
twadmin@dxu-hademo
jdbc:postgresql://dxu-hademo.postgres.database.azure.com:5432/thingworx?sslmode=verify-full&sslrootcert=/ThingworxPlatform/root.crt
Awesome. Your ThingWorx server should now be ready to start.
Alternative
If you have access to the Java trust store file in your ThingWorx server installation directory, you can import root.crt into the Java trust store with the following line:
keytool -keystore $JAVA_HOME/jre/lib/security/cacerts -alias postgresql -import -file root.crt
-alias is not important here. In this case, you do not need to specify the sslrootcert option in the jdbcUrl string.
ThingWorx Installation Resources
If you’re looking for guidance on how to install ThingWorx, please see these resources for help:
- ThingWorx Installation Overview: Link
- ThingWorx Video Tutorials: Link
- ThingWorx Trial Downloads: Link
- PostgreSQL Documentation: Link
- Azure Database for PostgreSQL Documentation: Link
Congrats! You should now be all set to run ThingWorx on an Azure-managed PostgreSQL database.
As always, I’m here if you have any questions.
Stay connected,
Kaya
Please note that Azure Database for PostgreSQL is a managed offering of PostgreSQL by Microsoft. PTC only officially supports opensource version of PostgreSQL Database as a persistence provider but we have our community members using AWS RDS for PostgreSQL, Azure Database for PostgreSQL, and Enterprise DB for their development and production usages as an enterprise offering from different vendors which works perfectly fine. For up to date information on the latest supported products do check the System Requirements Guide.
