Installing PostgreSQL 12 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 12, 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 PostgreSQL12.

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 12 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:

Blueworx CPD installation is a 2 stage process:

  1. Installation of the required PostgreSQL packages and the blueworx-db package
  2. 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

  1. Installation of packages:

    Install both the postgresql12-server package and the postgresql12 package on the same system as the CPD (i.e. the blueworx-db) package.

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

    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/12/data directory will contain sub-directories.

  2. 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.
  1. Installation of packages:

    Install the postgresql12-server package on the remote server.

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

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

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

  2. Initialisation of the CPD database:

    Remote server:

    Start the Postgresql server
    systemctl start postgresql-12

    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 BRM server to have remote access to PostgreSQL

    • edit
      /var/lib/pgsql/12/data/pg_hba.conf
    • add entries of
      host all all X.X.X.X/24 md5
      where
      X.X.X.X
      are the BRM and BAM server IP addresses.
      Note: If BRM and BAM are on the same system then only 1 entry is required.
    • edit
      /var/lib/pgsql/12/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-12

    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

    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.