Install and Configure PostgreSQL for RHEL
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
 |
These steps assume a version of RHEL with a GUI (X11) and an active account with access to the RHEL software repositories. If you are working without a GUI, skip installing PgAdmin and refer to this support article for alternate instructions. If you do not have access to the official RHEL software sources, you can set up a free open source repository from the EPEL team. (this site is not provided or controlled by PTC).
|
1. Refer to the ThingWorx System Requirements section of this Help Center for information on supported PostgreSQL versions.
|
|
The steps in this procedure use PostgreSQL version x.x, where x.x is the supported version.
|
2. Add the PostgreSQL repository to Yum and install.
3. Install PgAdmin, the PostgreSQL admin tool:
$ sudo yum install pgadmin4
|
|
To install PgAdmin via the command line, reference https://wiki.postgresql.org/wiki/Manual_Setup_at_the_Command_Line.
|
4. Initialize and launch the database:
$ sudo /usr/pgsql-x.x/bin/postgresqlx.x-setup initdb
Set the PostgreSQL service to start on boot:
$ sudo chkconfig postgresql-x.x on
$ sudo service postgresql-x.x start
$ sudo service postgresql-x.x start
5. Set up the password for the PostgreSQL user:
$ sudo passwd postgres
6. Enter the password for the PostgreSQL user. You will use this password in later steps.
|
|
The password, which should not be easily guessed or a known, common password, should be at least 14 characters in length and include a mix of uppercase and lowercase letters, numbers, and special characters.
|
7. Set up the PostgreSQL user in psql. The <unique PostgreSQL password> value is what you entered above.
|
|
If the PostgreSQL database is not located on the same server as ThingWorx, then refer to the section Configure PostgreSQL Database Located on a Separate Server than ThingWorx and skip the next two steps. While it is optional to have the PostgreSQL database on the same server as ThingWorx in a development environment, it should be separate in all production environments.
|
$ sudo -u postgres psql -c "ALTER ROLE postgres WITH password '<unique PostgreSQL password>'"
8. If using the command line, open the following files and edit as noted. Skip this step if using PgAdmin.
◦ /var/lib/pgsql/x.x/data/postgresql.conf: Uncomment listen addresses and port. The default settings of localhost and 5432 are usually sufficient.
◦ /var/lib/pgsql/x.x/data/pg_hba.conf: Set Method to md5
9. Configure PgAdmin. Skip this step if you are not using PgAdmin.
$ sudo pgadmin4
◦ In the PgAdmin GUI, click on file->Open postgresql.conf
◦ Open /var/lib/pgsql/x.x/data/postgresql.conf
◦ Put a check next to listen addresses and port. The default settings of localhost and 5432 are usually sufficient.
◦ Save and close.
◦ Click on file->Open pg_hba.conf
◦ Open /var/lib/pgsql/x.x/data/pg_hba.conf
◦ Double-click on the database ‘all’ line with address 127.0.0.1/32
◦ Set Method to md5
◦ Click OK
◦ Save and exit
◦ Close PgAdmin.
10. Restart the PostgreSQL service:
$ sudo service postgresql-x.x restart
11. Set up PgAdmin to connect to the database:
$ sudo pgadmin4
12. Click the plug icon to add a connection to a server in the top left corner and fill out the following:
Name: PostgreSQL x.x
Host: localhost
Port: 5432
Service: <blank>
Maintenance DB: postgres
Username: postgres
Password: <unique PostgreSQL password as set previously>
Store password: Checked
Group: Servers
Host: localhost
Port: 5432
Service: <blank>
Maintenance DB: postgres
Username: postgres
Password: <unique PostgreSQL password as set previously>
Store password: Checked
Group: Servers
13. Click OK.
14. Create a new user role:
|
|
The following command can be used if you are not using PgAdmin:
sudo -u postgres psql -c "CREATE USER twadmin WITH PASSWORD '<unique postgres password>';"
|
a. Right click PostgreSQLx.x(localhost:5432).
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.
e. 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 In order to get 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 Linux, the location of the data folder, or even the configuration files can change based on distribution and installation method (download or package manager install). This location will be referred to as <PGDATA> in these instructions.
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.conf file, see the PostgreSQL documentation.
|
Enabling PostgreSQL to Listen for all Connections
On Linux installations of PostgreSQL, there is an additional configuration step required to configure the PostgreSQL server to listen for connections.
1. In the postgresql.conf file, uncomment and update the listen_addresses line:
Uncomment the listen_addresses line and change localhost to '*'
# Listen on all addresses. Requires restart.
listen_addresses = '*'
# Listen on all addresses. Requires restart.
listen_addresses = '*'
2. Restart the PostgreSQL server.
Configure and Execute the PostgreSQL Database Script
To set up the PostgreSQL database and tablespace, the thingworxPostgresDBSetup script must be configured and executed.
|
|
After 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.
|
1. Obtain the thingworxPostgresDBSetup script from the ThingWorx software download package. The script is located in the install folder. ThingWorx downloads are available in PTC Software Downloads.
2. If necessary, configure the script. Reference the options in the table below.
|
|
This example uses the x.x.x download from the PTC site. Change the file name to the version you are using.
|
$ sudo unzip MED-61111-CD-085_ThingWorx-Platform-Postgres-x-x-x.zip
$ cd install
|
Option
|
Parameter
|
Default
|
Description
|
Example
|
||
|---|---|---|---|---|---|---|
|
t or -T
|
tablespace
|
thingworx
|
Tablespace name
|
-t thingworx
|
||
|
-p or -P
|
port
|
5432
|
Port number of PostgreSQL
|
-p 5432
|
||
|
-d or -D
|
database
|
thingworx
|
PostgreSQL Database name to create
|
-d thingworx
|
||
|
-l or -L
|
tablespace_location
|
/ThingworxPostgresqlStorage
|
Required in ThingWorx 9.3.0 and before. Location in the file system where the files representing database objects are stored. User needs to create this folder manually with permissions before executing the script and then specify the path, for example, -l /ThingworxPostgresqlStorage
|
-l or -L
|
||
|
-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
|
3. To set up the database and tablespace with a default PostgreSQL installation that has a PostgreSQL database and a PostgreSQL user name, enter:
$ sudo sh thingworxPostgresDBSetup.sh -a postgres -u <user role name> -l /ThingworxPostgresqlStorage
4. Execute the script.
Configure and Execute the Model/Data Provider Schema Script
To set up the PostgreSQL model/data provider schema, the thingworxPostgresSchemaSetup script must be configured and executed. This will set up the public schema under your database on the PostgreSQL instance installed on the localhost.
1. Obtain and open the thingworxPostgresSchemaSetup file from the ThingWorx software download package. The script is located in the install folder.
2. If necessary, configure the script. Reference the options in the table below.
|
|
The script can be run with the default parameters as:
$ sudo sh thingworxPostgresSchemaSetup.sh
|
|
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.
|
|
The username should match the PostgreSQL username that was previously created.
|
Configure platform-settings.json
1. Open platform-settings.json file located in the ThingworxPlatform.
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.
|
Encrypt the PostgreSQL Password
Encrypt the password by following the steps in Encrypting Passwords.
(Optional) Installing the PostgreSQL Client Package and PostgreSQL User
In order 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 distributions documentation on how to install it. This package provides some administration tools such as psql.