Adding on-demand EDB Postgres databases

If you need to create EDB Postgres databases for your CP4BA deployment, you must access the EDB Postgres pod, and then run the SQL files for the new databases inside the pod.

About this task

If you decide at any point to use the EDB Postgres instance that you created for your CP4BA deployment for another capability, then you can run SQL files for new databases in the EDB Postgres pod. You then need to modify the EDB Cluster CR to grant access for the users of this database. You might also need to add new databases for the object stores in Content Platform Engine or new projects for Document Processing.

Procedure

  1. Retrieve the EDB Cluster CR for the CP4BA deployment by running the following command.
    oc get cluster
    NAME                AGE   INSTANCES   READY   STATUS                     PRIMARY
    common-service-db   16h   2           2       Cluster in healthy state   common-service-db-1
    postgres-cp4ba      16h   1           1       Cluster in healthy state   postgres-cp4ba-1
    zen-metastore-edb   16h   2           2       Cluster in healthy state   zen-metastore-edb-2
    

    The EDB cluster for the CP4BA deployment in the example output is postgres-cp4ba. The PRIMARY column contains the primary pod name (postgres-cp4ba-1) for EDB Postgres.

  2. Create a location for the table space under the directory path of the PVC volume mount (/var/lib/postgresql/data/).
    oc rsh postgres-cp4ba-1
    mkdir /var/lib/postgresql/data/newdatabase
    exit 

    The primary pod name (postgres-cp4ba-1) is the name from the previous step.

    Tip: Ideally, each database has a separate location.
  3. Specify the location of the EDB Postgres table space in the SQL file (newdb.sql) for your new database.
    create tablespace "<NEW_TABLESPACE>" owner "<USER>" location '/var/lib/postgresql/data/newdatabase'; 
  4. Copy the SQL script into the EDB Postgres pod and run it.
    1. Copy the SQL script by running the following command.
      oc cp <sql_folder>/newdb.sql postgres-cp4ba-1:/var/lib/postgresql/data/
    2. Go inside the EDB Postgres pod by running the following command.
      oc rsh postges-cp4ba-1
      Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
    3. Access the psql command in the EDB Postgres pod by running the following command.
      psql -U postgres
    4. Run the SQL file.
      psql -U postgres psql
      Type "help" for help.
      postgres=# 
      postgres=# \i /var/lib/postgresql/data/newdb.sql 
      CREATE ROLE
      ALTER ROLE
      CREATE TABLESPACE
      ALTER TABLESPACE
      GRANT
      CREATE DATABASE
      ALTER DATABASE
      GRANT
      REVOKE
      GRANT
      GRANT
      CREATE SCHEMA
      SET
  5. If you created a user or role for the database, modify the EDB Cluster CR to add the user to the pg_hba.conf file.
    1. Run the following command and then search for pg_hba when you are in the edit view.
      oc edit cluster postgres-cp4ba

      The pg_hba section has lines similar to the following example:

      pg_hba:
          - hostssl gcddb gcduser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl icndb icnuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl os1db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl os2db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl bts bts 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
    2. Add the new database and new user to the list, and then save and exit the file.

      The pg_hba section has new lines for each new user.

      pg_hba:
          - hostssl gcddb gcduser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl icndb icnuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl os1db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl os2db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl bts bts 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
          - hostssl <newdb> <newdb_user> 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
  6. Verify that the pg_hba settings are updated in the EDB Postgres pod.
    1. Go back into the EDB Postgres pod.
      oc rsh postgres-cp4ba-1
    2. Go to the directory that contains the Postgres configurations.
      /var/lib/postgresql/data/pgdata
      Note: Configuration and data files that are used by a database cluster are stored together within the data directory of the cluster, commonly referred to as PGDATA. A common location for PGDATA is /var/lib/pgsql/data. The PGDATA directory contains several directories and control files. For example, the cluster configuration files postgresql.conf, pg_hba.conf, and pg_ident.conf are often stored in PGDATA, although it is possible to place them elsewhere. For more information, see PostgreSQL database file layout.
    3. Use the cat command to view the updated pg_hba.conf file.

      The following example shows the result of the cat command on the pg_hba.conf file.

      cat pg_hba.conf 
      #
      # FIXED RULES
      ## Grant local access ('local' user map)
      local all all peer map=local# Require client certificate authentication for the streaming_replica user
      hostssl postgres streaming_replica all cert
      hostssl replication streaming_replica all cert
      hostssl all cnp_pooler_pgbouncer all cert#
      # USER-DEFINED RULES
      #
      hostssl gcddb gcduser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
      hostssl icndb icnuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
      hostssl os1db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
      hostssl os2db osuser 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
      hostssl bts bts 10.254.0.0/16 scram-sha-256 clientcert=verify-ca
      hostssl newdb newdb 10.254.0.0/16 scram-sha-256 clientcert=verify-ca#
      # DEFAULT RULES
      #
      host all all all scram-sha-256