How can I configure ThingWorx with Azure PostgreSQL?

[Kaya]

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.

Hot Off the Press! As of our 8.4 release, our support for PostgreSQL will expand to include usage of an Azure-managed database of 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:

1. This installation guide assumes your ThingWorx will be running on Ubuntu 16.04.
2. 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.)
3. This guide is intended to be a sample. For security best practices around passwords, certificates, ports, etc., please consult ThingWorx documentation.
4. 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

The dbcUrl string should be:

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