Creating required PostgreSQL databases

Edit online

Containers:
V20.0.0.2
Before you install, create PostgreSQL databases for IBM® Business Automation Workflow, IBM Business Automation Application Engine, IBM FileNet® Content Manager, IBM Business Automation Navigator, and User Management Service (UMS).

Before you begin

Use the latest PostgreSQL JDBC driver and make sure the PostgreSQL database is configured correctly for the customer workload.

Adjust the following parameters in the postgresql.conf file of the database server:

Table 1. PostgreSQL parameters
Parameter	Setting	Description
shared_buffers	minimum 1024 MB	The normal PostgreSQL performance tuning recommendation is to use about 25% of the memory for the shared buffer. Adjustments to the Linux® kernel configuration might also be required; check the PostgreSQL tuning guides.
work_mem	minimum 20 MB	This parameter applies to each session, and a large number of user sessions can cause large memory usage. This memory is critical because it is used for sort operations. The running time can increase significantly (over an hour for toolkit deployments, for example) if the value is set too low.
max_prepared_transactions	for example, 200	This value should be at least as large as the max_connections setting.
max_wal_size	for example, 6 GB	For larger workloads, the default value must be increased. You can find advice in the PostgreSQL server log files if an increase is required.
log_min_duration_statement	for example, 5000	You can optionally set this parameter. It allows additional logging of statements that exceed the specified running time in milliseconds (which corresponds to 5 seconds in this example) to identify bottlenecks and tuning areas.

Procedure

To create the database for IBM Business Automation Workflow:
1. Create a create-database.sql file with the following content:
  create-database.sql
```
-- create user bawadmin/bawadmin
CREATE ROLE bawadmin PASSWORD 'bawadmin' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

-- bawdb
CREATE DATABASE bawdb OWNER bawadmin ENCODING UTF8;
GRANT ALL PRIVILEGES ON DATABASE bawdb to bawadmin;
\c bawdb;
CREATE SCHEMA IF NOT EXISTS bawadmin AUTHORIZATION bawadmin;
```
  Run the following commands on the PostgreSQL server:
```
su - postgres
psql -f create-database.sql
```
2. Optional: To ensure that all communications between Workflow Server and PostgreSQL are encoded, import the database certificate authority (CA) certificate to Workflow Server.
  1. To create a secret to store the certificate, run the following command:
```
kubectl create secret generic ibm-dba-baw-postgresql-tlscert --from-file=tls.crt=ssl_certificate_file
```
    Don't change tls.crt as the key name.
  2. Use the resulting secret to set the baw_configuration[x].database.sslsecretname: ibm-dba-baw-postgresql-tlscert property in the custom resource configuration file. In the same file, set baw_configuration[x].database.ssl to true.
3. Optional: To configure high availability disaster recovery (HADR) for the Workflow Server database, ensure that Workflow Server automatically retrieves the necessary failover server information when it first connects to the database. As part of the setup, provide a comma-separated list of failover servers and failover ports.
  For example if there are two failover servers, such as:
  - server1.postgresql.customer.com on port 5432
  - server1.postgresql.customer.com on port 5433
  you can specify these hosts and ports in the custom resource configuration YAML file as follows:
```
database:
  ... ...
    hadr:
      standbydb_host: server1.postgresql.customer.com, server2.postgresql.customer.com
      standbydb_port: 5432,5433
      retryintervalforclientreroute: default_value_is_10_min
      maxretriesforclientreroute: default_value_is_5
  ... ...
```
To create the database for Application Engine, follow the instructions in Creating a PostgreSQL database.
To create the databases for FileNet Content Manager, create a database for the Content Platform Engine global configuration database (GCD) and databases for the Content Platform Engine content stores. Follow the instructions in Preparing the databases.

Because IBM Business Automation Workflow needs four different object stores (DOCS, TOS, DOS, and AEOS), you must run the instructions to create the PostgreSQL database and table spaces four times.
To create the database for Business Automation Navigator, follow the instructions in Preparing the Business Automation Navigator database.
To create the database for UMS, follow the instructions in Preparing the databased for the User Management Services.