Installing PostgreSQL 10 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 10, any earlier PostgreSQL 9.6 installation will become invalid. If you wish to migrate database content from 9.6 to 10 then please read Migrating from an earlier version of PostgreSQL Database for Blueworx CPD before proceeding.

Note: If installed PostgreSQL 9.6 should be stopped before proceeding with the installation of PostgreSQL10.

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 10 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 10 packages on the server hosting the database
PostgreSQL Client 10 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 postgresql10-server package and the postgresql10 package on the same system as the CPD (i.e. the blueworx-db) package.

For instructions on how to install the postgresql10 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 10.

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/10/data directory 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 postgresql10-server package on the remote server.

Install the postgresql10 client package on the same system as the CPD (i.e. the blueworx-db) package.

For instructions on how to install the postgresql0 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 10.

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
- ```
systemctl start postgresql-10
```
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
```
/var/lib/pgsql/10/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 server IP addresses.
  Note: If BRM , BAM and BSM are on the same system then only 1 entry is required.
- edit
```
/var/lib/pgsql/10/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:
- ```
systemctl restart postgresql-10
```
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/10/static/libpq-pgpass.html

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 URL for the remote system and password for bwadmin into the BRM configuration file brm.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.