Creating required databases in PostgreSQL

Before you install, create PostgreSQL databases for IBM Business Automation Workflow, Business Automation Studio, or both, as well as for Application Engine, IBM FileNet® Content Manager, and IBM Business Automation Navigator.

1. To create the database for IBM Business Automation Workflow:
   1. Create a create-baw-instance1-database.sql file and copy it to your PostgreSQL server.
      create-baw-instance1-database.sql

      -- create user bawadmin/bawadmin
      CREATE ROLE bawadmin PASSWORD 'bawadmin_password' CREATEDB CREATEROLE INHERIT LOGIN;
      
      -- baw1db
      CREATE DATABASE baw1db OWNER bawadmin ENCODING UTF8;
      GRANT ALL PRIVILEGES ON DATABASE baw1db to bawadmin;
      \c baw1db;
      CREATE SCHEMA IF NOT EXISTS bawadmin AUTHORIZATION bawadmin;
      GRANT ALL ON SCHEMA bawadmin to bawadmin;

      In this example:

      • bawadmin is the username that Workflow Authoring uses to connect to the database.
      • bawadmin_password is the password that Workflow Authoring uses to connect to the database.
      • baw1db is the database name for the Workflow Authoring database, for example, BAWDB. It is case-sensitive.

      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-instance1-postgresql-tlscert --from-file=tls.crt=ssl_certificate_file

         Don't change tls.crt as the key name.
         For better security, you can enable client authorization for the connection. To import the client key and client certificate to Workflow Server, run the following command:

         kubectl create secret generic ibm-dba-baw-instance1-postgresql-tlscert --from-file=tls.crt="<your-client-certification-name>.crt" --from-file=ca.crt="<your-server-certification-name>.crt" --from-file=tls.key="<your-client-key-name>.key" --from-literal=sslmode=[verify-ca|verify-full]

         Don't change tls.crt, ca.crt, and tls.key as the key name.
      2. Add the secret to the database configuration in the baw_configuration section of the custom resource file. For example, the name of the secret could be baw-instance1-postgresql-ssl-secret.

         baw_configuration:
          - name: instance1
             ...
             database:
               enable_ssl: true
               db_cert_secret_name: " baw-instance1- postgresql-ssl-secret"
               ...
         

   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
        ... ...

2. To create the database for Workstream Services:
   1. Create a create-baw-instance2-database.sql file with the following content:
      create-baw-instance2-database.sql

      -- create user bawadmin/bawadmin
      CREATE ROLE bawadmin PASSWORD 'bawadmin' CREATEDB CREATEROLE INHERIT LOGIN;
      
      -- baw2db
      CREATE DATABASE baw2db OWNER bawadmin ENCODING UTF8;
      GRANT ALL PRIVILEGES ON DATABASE baw2db to bawadmin;
      \c baw2db;
      CREATE SCHEMA IF NOT EXISTS bawadmin AUTHORIZATION bawadmin;
      GRANT ALL ON SCHEMA bawadmin to bawadmin;

      Run the following commands on the PostgreSQL server:

      su - postgres
      psql -f create-database.sql

   2. Optional: To ensure that all communications between Workstream Services and PostgreSQL are encoded, import the database certificate authority (CA) certificate to Workstream Services.
      1. To create a secret to store the certificate, run the following command:

         kubectl create secret generic ibm-dba-baw-instance2-postgresql-tlscert --from-file=tls.crt=ssl_certificate_file

         Don't change tls.crt as the key name.
      2. Add the secret to the database configuration in the baw_configuration section of the custom resource file. For example, the name of the secret might be baw-instance2-postgresql-ssl-secret.

         baw_configuration:
          - name: instance2
             ...
             database:
               enable_ssl: true
               db_cert_secret_name: " baw-instance2- postgresql-ssl-secret"
               ...
         

   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
        ... ...

3. Optional: To use your own JDBC driver, package your JDBC files into a compressed file and use the sc_drivers_url configuration parameter to download them from an accessible web server.
   Follow the steps in Optional: Preparing customized versions of JDBC drivers and ICCSAP libraries.
4. To create the database for Application Engine, follow the instructions in Creating a PostgreSQL database.
5. To create the databases for IBM FileNet Content Manager, create a database for the Content Platform Engine global configuration database (GCD) and databases for the content stores. Follow the instructions in Preparing the databases.

   Business Automation Workflow needs four different object stores (DOCS, TOS, DOS, and AEOS). If you are installing the Workflow capability, you must run the instructions to create the PostgreSQL database and table spaces four times. If you are installing only the Workstreams capability, you need two object stores (DOCS and AEOS).

6. To create the database for Business Automation Navigator, follow the instructions in Creating the databases without running the provided scripts.