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 Reference (DMCFG). Here is a quick cross-reference between the two sets of parameters:
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.
Last updated