Installing PostgreSQL Database for Blueworx CPD

Note: Blueworx packages should be installed as described in Installing Blueworx (new installation) before installing PostgreSQL.

Note: All commands must be run as root, or as a user with root privileges using the sudo command.

Note: Once you begin the installation of PostgreSQL any earlier PostgreSQL installation will become invalid. If you wish to migrate database content from an earlier version of PostgreSQL then please read Migrating from an earlier version of PostgreSQL Database for Blueworx CPD before proceeding.

Note: If an earlier version of PostgreSQL is installed, it should be stopped before proceeding with the installation of the later version of PostgreSQL

The Blueworx Configuration Profile Database (CPD) uses a PostgreSQL database to store data used by the Blueworx platform. This database can be hosted locally on the BRM server or remotely on a hosted PostgreSQL database image, either through a cloud platform offering or a remote VM running the PostgreSQL server software.

The prerequisites for installing the CPD component are:

PostgreSQL Server packages on the server hosting the database
PostgreSQL Client packages and the blueworx-db package on the BRM server

Blueworx CPD installation is a 2 stage process:

Installation of the required PostgreSQL packages and the blueworx-db package
Initialization of the Blueworx CPD database structure and data

The following instructions provide the detailed steps for both a local installation and a remote installation.

Local database - PostgreSQL server installed on the same VM as the CPD rpm

Installation of packages:

Install both the postgresql*-server package and the postgresql* package on the same system as the CPD (i.e. the blueworx-db) package, where * is the version number.

For instructions on how to install the postgresql packages, see the PostgreSQL documentation : https://www.postgresql.org/download/linux/redhat/.

You must now follow the instructions in the "PostgreSQL Yum Repository" section

Note: By default this section will select the latest version of PostgreSQL. This should be changed to the required version.

Ensure that you complete all the installation steps including initialising the database and enabling automatic start.

When you have completed all steps the /var/lib/pgsql*/data directory, where * is the version number, will contain sub-directories.
Initialization of the CPD database:
Database initialization is performed using the
```
bdb install
```
script provided in the blueworx-db package. Ensure that BRM is not started and then run the following:
```
/opt/blueworx/db/bin/bdb install
```

Remote database - PostgreSQL server installed on a different VM to the CPD rpm

This section describes how to set up the Blueworx CPD on a remote VM that you have direct access to, including the ability to install packages and run commands.

Note: We do not document how to set up a the Blueworx CPD database on a remote database cloud service as each cloud provider has differing setup details. However, it is possible to take the following instructions and apply them to the cloud service. The important steps to take on the cloud database service are Create bwadmin user and database and Allow remote access to cloud service from BRM.

Installation of packages:

Install the postgresql*-server package on the remote server, where * is the version number.

Install the postgresql* client package, where * is the version number, on the same system as the CPD (i.e. the blueworx-db) package.

For instructions on how to install the postgresq packages, see the PostgreSQL documentation: https://www.postgresql.org/download/linux/redhat/.

Note: By default this will select the latest version of PostgreSQL. This should be changed to the required version.

Ensure that you complete all the installation steps including initialising the database and enabling automatic start on the remote database server.
Initialisation of the CPD database:

Remote server:
Start the Postgresql server (where * is the version number)
```
systemctl start postgresql-*
```
Create bwadmin user and database:
- Log in as root, then
```
su – postgres
```
- start psql (interactive terminal for working with Postgres), using
```
psql
```
- ```
CREATE ROLE bwadmin CREATEDB LOGIN PASSWORD 'password';
```
  Note: password should be replaced by a password of your choosing
- ```
CREATE DATABASE bwadmin OWNER bwadmin;
```
- ```
\q
```
  (to exit psql)
Allow, as required, BRM, BAM and/or BSM server to have remote access to PostgreSQL
- edit (where * is the version number)
```
/var/lib/pgsql/*/data/pg_hba.conf
```
- add entries of
```
host all all X.X.X.X/24 md5
```
  where
```
X.X.X.X
```
  are the BRM , BAM and BSM (if installed) server IP addresses.
  Note: If BRM, BAM or BSM (if installed) are on the same system then only a single entry for that system is required.
- edit (note * is the version number)
```
/var/lib/pgsql/*/data/postgresql.conf
```
- ensure the following entry is set:
```
listen_addresses = '*'
```
- ensure the following entry is set:
```
port = 5432
```
  Note: This is the default PostgreSQL port.
Become root again and restart PostgreSQL as follows (where * is the version number):
```
systemctl restart postgresql-*
```
CPD server:

Allow remote access to the PostgreSQL server by creating a .pgpass as described in the PostgreSQL help pages here: https://www.postgresql.org/docs/12/static/libpq-pgpass.html The page shown as an example is for version 12 of postgresSQL. The page matching the version installed should be used.

Use the bwadmin user and password specified in the CREATE ROLE command issued on the remote server running PostgreSQL.

The format of the .pgpass file allows the user to configure multiple passwords allowing access to multiple databases from the same PostgreSQL client machine.

In order to configure the Blueworx databases, the .pgpass file can be set up in a generic way using the wildcard character for most of the entries:
```
*:*:*:*:password
```
If you need to fully define those entries, for enhanced security or if you are managing multiple databases, then the fully defined entries required for Blueworx are the following:
```
hostname:port:bwadmin:bwadmin:password
```
```
hostname:port:bw_db:bwadmin:password
```
```
hostname:port:template1:bwadmin:password
```
In each case, hostname and port (default to use is 5432) should be replaced with the fully qualified hostname and port of the remote database and password should be replaced by the password for that database.
Add the URL for the remote system and password for bwadmin into the configuration file for the following components:
- BRM configuration file brm.config See here for details: Main Database Configuration Options
- BAM configuration file bam.config See here for details: Main Database Configuration Options
- BSM (if installed) configuration file bsm.config See here for details: Main Database Configuration Options
Initialise the Configuration Profile Database on the remote database:
- ```
/opt/blueworx/db/bin/bdb install hostname port
```
- where hostname is replaced with the hostname of the remote database server and port is replaced by the port being used by PostgreSQL on the remote database server.