Install and Configure PostgreSQL for Windows
The instructions provided below are intended for the PostgreSQL administrator (not the DB host servers). If you are including the HA layer to your implementation, refer to the ThingWorx High Availability section of this Help Center.
Install PostgreSQL and Create a New User Role
1. Refer to ThingWorx System Requirements for information on supported PostgreSQL versions.
 |
The steps in this procedure use PostgreSQL version x.x, where x.x is the supported version.
|
2. Download and install the appropriate version of PostgreSQL from http://www.postgresql.org/download/.
3. Open PostgreSQL using pgAdmin. The pgAdmin tool is available in the PostgreSQL download.
|
|
pgAdmin is an open source management tool for your databases that is included in the PostgreSQL download. The tool features full Unicode support, fast, multi-threaded query, and data editing tools and support for all PostgreSQL object types.
|
4. Create a new user role:
a. Right click PostgreSQLx.x (<IP or host name of the database>:<Port number of PostgreSQL>). Example: PostgreSQLx.x (localhost:5432)
|
|
Later in this process, this user is referred to as thingworxusername.
|
b. Select Create - Login/Group Role. On the General tab, in the Name field, enter the <PostgreSQL user role name> for PostgreSQL administration.
c. On the Privileges tab, select Yes for Can login?
d. On the Definition tab, in the Password field, enter a unique and secure password for PostgreSQL.
5. Click OK. Note the user role name and password created in this step for later use.
Configure PostgreSQL Database Located on a Separate Server than ThingWorx
|
|
This section is optional for development environments, but should be implemented in all production environments.
|
By default, the PostgreSQL server is installed in a locked-down state. The server will only listen for connections from the local machine. For ThingWorx to communicate to the PostgreSQL server, some configuration changes need to be made so that PostgreSQL knows to listen for connections from other users (thingworx user, default is twadmin) and/or other machines (ThingWorx installed on a separate server).
You will need to know where your PostgreSQL data directory resides for these steps. On Windows, the default data folder is C:\Program Files\PostgreSQL\x.x\data.
Modify the pg_hba.conf file and add the following lines based on your desired configuration:
|
If you want to allow all IPv4 addresses to connect:
|
host all all 0.0.0.0/0 md5
|
|
If you want to allow only a specific IPv4 address to connect (Replace <ipAddress> with the IP address of the machine making the connection):
|
host all all <ipAddress>/32 md5
|
|
If you want to allow all IPv6 addresses to connect:
|
host all all ::0/0 md5
|
|
If you want to allow only a specific IPv6 address to connect (Replace <ipv6Address> with the appropriate address):
|
host all all <ipv6Address>/128 md5
|
Any other combination is possible by using additional allowance lines (individual IPs or ranges) or subnet masks appropriate to the machines that require access to the PostgreSQL database.
Any change to this file requires a restart of the database service.
|
|
For additional information about configuring the pg_hba.conffile, see the official PostgreSQL documentation.
|
Configure and Execute the PostgreSQL Database Script
To set up the PostgreSQL database, the thingworxPostgresDBSetup.bat script must be configured and executed.
|
|
Starting with ThingWorx 9.3.1, the ThingworxPostgresqlStorage folder creation or any -l parameter-related steps are no longer required, and the ThingWorx data will be stored at the default location of Postgresql.
|
|
|
Starting with ThingWorx 9.3.1, tablespaces are no longer supported. It is not recommended to create a tablespace on the same file system as the data directory or on the same file system as another tablespace.
|
1. Add the <postgres-installation>/bin folder to your system PATH variable.
2. Obtain and open the thingworxPostgresDBSetup.bat script from the ThingWorx software download package. ThingWorx downloads are available in PTC Software Downloads. The script is located in the install folder.
3. If necessary, configure the script. Reference the options in the table below.
|
Option
|
Parameter
|
Default
|
Description
|
Example
|
|---|---|---|---|---|
|
-p or -P
|
port
|
5432
|
Port number of PostgreSQL
|
-p 5432
|
|
-d or -D
|
database
|
thingworx
|
PostgreSQL Database name to create
|
-d thingworx
|
|
-h or -H
|
host
|
localhost
|
Name of the host
|
-h localhost
|
|
-a or -A
|
adminusername
|
postgres
|
Administrator Name
|
-a postgres
|
|
-u or -U
|
thingworxusername
|
twadmin
|
User name that has permissions to write to the database.
|
-u twadmin
|
4. Execute the script.
Configure and Execute the Model/Data Provider Schema Script
To set up the PostgreSQL model/data provider schema, the thingworxPostgresSchemaSetup.bat script must be configured and executed. This script will set up the public schema under your database on the PostgreSQL instance installed on the localhost.
1. Obtain the thingworxPostgresSchemaSetup.bat script from the ThingWorx software download package in the install folder. ThingWorx downloads are available inPTC Software Downloads.
2. If necessary, configure the script. Reference the options in the table below.
|
Option
|
Parameter
|
Default
|
Description
|
Example
|
||
|---|---|---|---|---|---|---|
|
-h or -H
|
host
|
localhost
|
IP or host name of the database.
|
-h localhost
|
||
|
-p or -P
|
port
|
5432
|
Port number of PostgreSQL.
|
-p 5432
|
||
|
-d or -D
|
database
|
thingworx
|
Database name to use.
|
-d thingworx
|
||
|
-s or -S
|
schema
|
public
|
Schema name to use.
|
-s myschema
|
||
|
-u or -U
|
username
|
twadmin
|
Username to update the database schema
|
-u twadmin
|
||
|
-o or -O
|
option
|
all
|
There are three options:
• all: Sets up the model and data provider schemas into the specified database.
• model: Sets up the model provider schema into the specified database.
• data: Sets up the data provider schema into the specified database.
|
-o data
|
3. Execute the script.
Configure platform-settings.json
1. Open platform-settings.json file located in the ThingworxPlatform folder.
2. Refer to the PostgresPersistenceProviderPackage configuration options in platform-settings.json Configuration Details and add to the platform-settings.json.
|
|
If your PostgreSQL server is not the same as your ThingWorx server, and you are having issues with your ThingWorx installation, review your Tomcat logs and platform-settings.json file. The default installation assumes both servers are on the same machine.
|
(Optional) Encrypt the PostgreSQL Password
Encrypt the password by following the steps in Encrypting Passwords.
(Optional) Installing the PostgreSQL Client Package and PostgreSQL User
To issue PostgreSQL commands from the client machine to the PostgreSQL server, do so from a PostgreSQL user. The postgresql-client-x.x package can be installed on the client machine. Refer to your PostgreSQL distribution documentation on how to install it. This package provides some administrative tools such as psql.