Creating database objects for PostgreSQL

Database prerequisites

The database cluster must already exist.
This procedure must be performed by a DBA user.

Let's start

Copy the DOIM_HOME/pkg-repo/pkg-base/db/ghibli-distro-db-<version>.tar.gz to the database host or to a client that can connect to the database.
Extract the package ghibli-distro-db-<version>.tar.gz.
Run the steps described in the following sections.

Please notice that some of the SQL scripts require parameters whose value must be the same as the value specified in the Domain Master Configuration (DMCFG). Here is a quick cross-reference between the two sets of parameters:

SQL script parameter	DMCFG parameter
ADMIN_USER	NONE. This is an arbitrary user that will be created by the scripts and will be granted full administrative rights on both CEMAN Core schema (ceman_db_schema in DMCFG) and CEMAN IAM schema (KEYCLOAK_DB_SCHEMA in DMCFG).
APP_USER	CEMAN_DB_USR
APP_PASS	CEMAN_DB_PSW
KEYCLOAK_DB_SCHEMA	KEYCLOAK_DB_SCHEMA
CEMAN_DB_SCHEMA	ceman_db_schema

1. Create the CEMAN tablespaces

(01_CEMAN_PREPARE_TBLS.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \ 
-v tbls_d1_ge_data=d1_ge_data \  
-v tbls_d1_ge_data_path="'/opt/dataone/data/tbls/d1_ge_data'" \
-v tbls_d1_ge_indexes=d1_ge_indexes \
-v tbls_d1_ge_indexes_path="'/opt/dataone/data/tbls/d1_ge_indexes'" \ 
-v tbls_d1_ru_data=d1_ru_data \
-v tbls_d1_ru_data_path="'/opt/dataone/data/tbls/d1_ru_data'" \  
-v tbls_d1_ru_indexes=d1_ru_indexes \
-v tbls_d1_ru_indexes_path="'/opt/dataone/data/tbls/d1_ru_indexes'" \
-v tbls_d1_hi_data=d1_hi_data \
-v tbls_d1_hi_data_path="'/opt/dataone/data/tbls/d1_hi_data'" \
-v tbls_d1_hi_indexes=d1_hi_indexes \
-v tbls_d1_hi_indexes_path="'/opt/dataone/data/tbls/d1_hi_indexes'" \
-f 01_CEMAN_PREPARE_TBLS.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<DBA_USER> with the username of a DBA user

This is the list of the required space-separated script parameters:

tbls_d1_ge_data: tablespace name for generic data
tbls_d1_ge_data_path: absolute datafile path for generic data tablespace
tbls_d1_ge_indexes: tablespace name for generic indexes
tbls_d1_ge_indexes_path: absolute datafile path for generic indexes tablespace
tbls_d1_ru_data: tablespace name for runtime data
tbls_d1_ru_data_path: absolute datafile path for runtime data tablespace
tbls_d1_ru_indexes: tablespace name for runtime indexes
tbls_d1_ru_indexes_path: absolute datafile path for runtime indexes tablespace
tbls_d1_hi_data: tablespace name for historic data
tbls_d1_hi_data_path: absolute datafile path for historic data tablespace
tbls_d1_hi_indexes: tablespace name for historic indexes
tbls_d1_hi_indexes_path: absolute datafile path for historic indexes tablespace

Amend all parameters according to your specific PostgreSQL installation.

If the output shows a message like "WARNING: there is no transaction in progress", it could be related to auto commit configuration.

2. Create the CEMAN users

(02_CEMAN_PREPARE_USERS.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \
-v admin_user=<ADMIN_USER> \
-v admin_pass="'<ADMIN_PWD>'" \
-v app_user=<APP_USER> \
-v app_pass="'<APP_PASS>'" \
-v tbls_d1_ge_data=d1_ge_data \
-v tbls_d1_ge_indexes=d1_ge_indexes \ 
-v tbls_d1_ru_data=d1_ru_data \ 
-v tbls_d1_ru_indexes=d1_ru_indexes \
-v tbls_d1_hi_data=d1_hi_data \ 
-v tbls_d1_hi_indexes=d1_hi_indexes \ 
-f 02_CEMAN_PREPARE_USERS.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<DBA_USER> with the username of a DBA user
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<ADMIN_PWD> with the password for the owner of the objects
<APP_USER> with the username used by the application to connect to the database
<APP_PASS> with the password for the application user

Amend all parameters according to your specific PostegreSQL installation.

3. Create the CEMAN database

(03_CEMAN_CREATE_DB.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \
-v database_name=<DB_NAME> \
-v admin_user=<ADMIN_USER> \
-v tbls_d1_ge_data=d1_ge_data \
-f 03_CEMAN_CREATE_DB.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<DBA_USER> with the username of a DBA user
<DB_NAME> with the name of the database
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)

Amend all parameters according to your specific PostegreSQL installation.

4. Create the CEMAN schema

(04_CEMAN_CREATE_SCHEMA.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \ 
-v database_name=<DB_NAME> \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-v schema=<CEMAN_DB_SCHEMA> \ 
-v tbls_d1_ge_data=d1_ge_data \ 
-v tbls_d1_ge_indexes=d1_ge_indexes \ 
-v tbls_d1_ru_data=d1_ru_data \ 
-v tbls_d1_ru_indexes=d1_ru_indexes \ 
-v tbls_d1_hi_data=d1_hi_data \ 
-v tbls_d1_hi_indexes=d1_hi_indexes \ 
-f 04_CEMAN_CREATE_SCHEMA.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<APP_USER> with the username used by the application to connect to the database
<CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

5. Create CEMAN workflow objects

(05_CEMAN_CREATE_SCHEMA_WORKFLOW.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v database_name=<DB_NAME> \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-v schema=<CEMAN_DB_SCHEMA> \ 
-v tbls_d1_ge_data=d1_ge_data \ 
-v tbls_d1_ge_indexes=d1_ge_indexes \ 
-v tbls_d1_ru_data=d1_ru_data \ 
-v tbls_d1_ru_indexes=d1_ru_indexes \ 
-v tbls_d1_hi_data=d1_hi_data \ 
-v tbls_d1_hi_indexes=d1_hi_indexes \ 
-f 05_CEMAN_CREATE_SCHEMA_WORKFLOW.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<APP_USER> with the username used by the application to connect to the database
<CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

6. Load CEMAN initial data

(06_CEMAN_INITIAL_DML.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v schema=<CEMAN_DB_SCHEMA> \ 
-f 06_CEMAN_INITIAL_DML.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

7. Assign CEMAN permissions to the application user

(07_CEMAN_GRANT_USER.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v database_name=<DB_NAME> \ 
-v schema=<CEMAN_DB_SCHEMA> \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-f 07_CEMAN_GRANT_USER.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<CEMAN_DB_SCHEMA> with the name of the schema
<APP_USER> with the username used by the application to connect to the database

Amend all parameters according to your specific PostgreSQL installation.

8. Create the KEYCLOAK tablespaces

(08_IAM_PREPARE_TBLS.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \
-v tbls_kc_ge_data=kc_ge_data \ 
-v tbls_kc_ge_data_path="'/opt/dataone/data/tbls/d1_kc_data'" \ 
-f 08_IAM_PREPARE_TBLS.sql \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER>

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<DBA_USER> with the username of a DBA user

This is the list of the required space-separated script parameters:

tbls_kc_ge_data: tablespace name for keycloak data
tbls_kc_ge_data_path: absolute datafile path for keycloak data tablespace

Amend all parameters according to your specific PostgreSQL installation.

If the output shows a message like "WARNING: there is no transaction in progress", it could be related to auto commit configuration.

9. Create the KEYCLOAK schema

(09_IAM_CREATE_SCHEMA.sql)

 psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v tbls_kc_ge_data=kc_ge_data \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-v schema=<KEYCLOAK_DB_SCHEMA> \ 
-f 09_IAM_CREATE_SCHEMA.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<APP_USER> with the username used by the application to connect to the database
<KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema

Amend all parameters according to your specific PostgreSQL installation.

10. Create the KEYCLOAK schema authorization

(10_IAM_CREATE_SCHEMA_AUTHORIZATION.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v keycloak_schema=<KEYCLOAK_DB_SCHEMA> \ 
-v dataone_schema=<CEMAN_DB_SCHEMA> \ 
-v app_user=<APP_USER> \ 
-f 10_IAM_CREATE_SCHEMA_AUTHORIZATION.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<APP_USER> with the username used by the application to connect to the database
<KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema
<CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

11. Assign KEYCLOAK permissions to the application user

(11_IAM_GRANT_USER.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v database_name=<DB_NAME> \ 
-v schema=<KEYCLOAK_DB_SCHEMA> \ 
-v app_user=<APP_USER> \ 
-f 11_IAM_GRANT_USER.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema
<APP_USER> with the username used by the application to connect to the database

Amend all parameters according to your specific PostgreSQL installation.

12. Create the KEYCLOAK Triggers

(12_IAM_ADD_TRIGGERS.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v keycloak_schema=<KEYCLOAK_DB_SCHEMA> \ 
-v dataone_schema=<CEMAN_DB_SCHEMA> \ 
-v app_user=<APP_USER> \ 
-v tbls_kc_ge_data=kc_ge_data \ 
-f 12_IAM_ADD_TRIGGERS.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema
<CEMAN_DB_SCHEMA> with the name of the schema
<APP_USER> with the username used by the application to connect to the database

Amend all parameters according to your specific PostgreSQL installation.

13. Load KEYCLOAK initial data

(13_IAM_INITIAL_DML.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v keycloak_schema=<KEYCLOAK_DB_SCHEMA> \ 
-f 13_IAM_INITIAL_DML.sql

Replace:

<HOST> with the PostgreSQL hostname or IP
<PORT> with the PostgreSQL port
<ADMIN_USER> with the username of the owner of the objects (tables, views, …)
<DB_NAME> with the name of the database
<KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema

Amend all parameters according to your specific PostgreSQL installation.

Updated 5 months ago