PostgreSQL
This page describes how to set up a new PowerTSM database on an existing PostgreSQL instance and how to update an existing PowerTSM database to a more recent version using the provided PowerShell scripts.
Prerequisites
Section titled “Prerequisites”A list of supported PostgreSQL versions can be found here: System Requirements.
Configuration
Section titled “Configuration”To use installation/update PowerShell scripts a valid configuration.ini must be provided. This file can be found in folder Database Scripts\PostgreSQL. Use a text editor to configure the ServerConfiguration and DatabaseConfiguration sections.
Meaning of Parameters
ServerConfiguration
Section titled “ServerConfiguration”| Element | Type | Default | Required | Description |
|---|---|---|---|---|
dbhost |
string |
— | ✓ | Name of the server on which PostgreSQL is running. |
dbport |
int |
— | ✓ | Port of the server running PostgreSQL. |
usersuffix |
string |
— | Suffix to be appended to usernames during login/connect operations (e.g. @hostname). |
|
dbadmin |
string |
— | Name of the user with admin rights. | |
PGPASSWORD |
string |
— | Password of the user with admin rights (dbadmin). |
|
dbadmindatabase |
string |
— | Database of the dbadmin (needed for connection when creating a new database or user). |
DatabaseConfiguration
Section titled “DatabaseConfiguration”| Element | Type | Default | Required | Description |
|---|---|---|---|---|
dbname |
string |
— | ✓ | Name of the database to be edited. If create_db = true, this is the name the new database will have. |
dbowner |
string |
— | Username of the user who should be the owner of the database. | |
dbowner_password |
string |
— | Password of the user who should be the owner of the database. | |
tablespace |
string |
— | Specifies which tablespace is to be used for data. For some environments (e.g. cloud based environments) the specification of tablespaces is not an option. In these cases an empty string can be provided for both tablespace and tablespaceidx in order to use the default tablespaces. Example: tablespace=. |
|
tablespaceidx |
string |
— | Specifies which tablespace is to be used for indexes. An empty string can be provided to use the default tablespace (see tablespace). Example: tablespaceidx=. |
|
create_user |
bool |
— | true, if a new user should be created. false, if not. |
|
create_db |
bool |
— | Specifies whether a new database should be created. | |
dblocale |
string |
— | Locale, which is used for the database collation. Depending on whether the database is installed on Windows or Linux, this setting will require different locale names. Windows examples: English_United States.1252, German_Germany.1252.Linux examples: en_US.utf8, de_DE.utf8. |
|
create_tablespace |
bool |
— | true, if tablespaces are to be created. false, if not. |
|
data_location |
string |
— | Specifies the path where the data files are located (only required if create_tablespace = true). |
|
index_location |
string |
— | Specifies the path where the index files are located (only required if create_tablespace = true). |
Installation
Section titled “Installation”This section describes how to set up a fresh PowerTSM database on an existing PostgreSQL instance.
Pre-Deployment Configuration
Section titled “Pre-Deployment Configuration”The scripts to be imported can be found under the following path: Database Scripts\PostgreSQL.
Executing the PowerShell Scripts
Section titled “Executing the PowerShell Scripts”To set up the database via PowerShell scripts, ensure that the file configuration.ini is properly configured as described in Configuration.
- Place the installation package in a local folder.
- Open a Windows PowerShell.
- Navigate to
Database Scripts\PostgreSQL. - Run the file
install-latest.ps1.
-
Place the installation package in a local folder.
-
Open a command line interface.
-
Navigate to
Database Scripts\PostgreSQL. -
Run the file
install-latest.ps1.
Update
Section titled “Update”This section describes how to upgrade an existing database which already has PowerTSM components installed to a more recent version.
Preparation
Section titled “Preparation”In order to determine the PowerTSM database version currently in use, check the entries of the table DBVERSION. The interpretation of the entries is explained in the following example.
Reading the version from the DBVERSION table
Section titled “ Reading the version from the DBVERSION table”Content of the DBVERSION table:
| ID | NAME | DESCRIPTION | CREATIONDATE | REVISION | PATCH | MAJOR | MINOR |
|---|---|---|---|---|---|---|---|
| 1 | HAKOM Framework | - | 2021-07-01 | 6 | 0 | 3 | 8 |
Interpretation:
HAKOM Framework: version 3.8.6.0
Manual Check Scripts
Section titled “Manual Check Scripts”Before the update scripts are installed, it is important to check if manual check scripts have to be executed. You can find them under \Manual check scripts before upgrade and if necessary, import them into pgAdmin and follow the instructions as described in the scripts.
Executing the PowerShell Scripts
Section titled “Executing the PowerShell Scripts”To update the database via PowerShell scripts, ensure that the file configuration.ini is properly configured as described in Configuration.
- Place the installation package in a local folder.
- Open a Windows PowerShell.
- Navigate to the path of the next higher version than the current version.
- Run each of the following scripts if they exist in the given folder:
upgrade.ps1upgrade-Index.ps1
- Repeat steps 3 and 4 up to the desired version.
- Place the installation package in a local folder.
- Open a command line interface.
- Navigate to the path of the next higher version than the current version.
- Run each of the following scripts if they exist in the given folder:
upgrade.ps1upgrade-Index.ps1
- Repeat steps 3 and 4 up to the desired version.