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
-
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
. ThePRIMARY
column contains the primary pod name (postgres-cp4ba-1
) for EDB Postgres. - 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. - 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';
- Copy the SQL script into the EDB Postgres pod and run it.
- Copy the SQL script by running the following command.
oc cp <sql_folder>/newdb.sql postgres-cp4ba-1:/var/lib/postgresql/data/
- 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)
- Access the
psql
command in the EDB Postgres pod by running the following command.psql -U postgres
- 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
- Copy the SQL script by running the following command.
- 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.
- Run the following command and then search for
when you are in the edit view.pg_hba
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
- 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
- Run the following command and then search for
- Verify that the
pg_hba
settings are updated in the EDB Postgres pod.- Go back into the EDB Postgres pod.
oc rsh postgres-cp4ba-1
- 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. - 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
- Go back into the EDB Postgres pod.