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 DOIM_HOME/pkg-repo/pkg-base/datashaper/db/ghibli-distro-datashaper-db-<version>.tar.gz to the database host or to a client that can connect to the database.
• Extract the package ghibli-distro-datashaper-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 DMCFG.

Here is a quick cross-reference between the two sets of parameters:

1. Create the tablespaces

psql -h <HOST> -p <PORT> -U <DBA_USER> \
-v tbls_ds_ge_data=ds_ge_data \
-v tbls_ds_ge_data_path="'/opt/dataone/data/tbls/ds_ge_data'" \
-f 01_DS_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

and amend all parameters according to your specific PostgreSQL installation.

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

2. Create the users

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_ds_ge_data=ds_ge_data \  
-f 02_DS_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 new username of the owner of objects (tables, views, …)
• <ADMIN_PWD> with the password for the new owner of objects
• <APP_USER> with the username used by the application to connect to the database
• <APP_PASS> with the password for the application user

and amend all parameters according to your specific PostgreSQL installation.

3. Create the database

psql -h <HOST> -p <PORT> -U <DBA_USER> \  
-v database_name=<DB_NAME> \  
-v admin_user=<ADMIN_USER> \  
-v tbls_ds_ge_data=ds_ge_data \  
-f 03_DS_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 objects (tables, views, …)

and amend all parameters according to your specific PostgreSQL installation.

4. Create the schema

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=<SCHEMA_NAME> \  
-f 04_DS_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 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
• <SCHEMA_NAME> with the name of the schema

and amend all parameters according to your specific PostgreSQL installation.

5. Load initial data

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \  
-v schema=<SCHEMA_NAME> \  
-f 05_DS_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 objects (tables, views, …)
• <DB_NAME> with the name of the database
• <SCHEMA_NAME> with the name of the schema

and amend all parameters according to your specific PostgreSQL installation.

6. Assign permissions to the application user

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \  
-v database_name=<DB_NAME> \  
-v schema=<SCHEMA_NAME> \  
-v admin_user=<ADMIN_USER> \  
-v app_user=<APP_USER> \  
-f 06_DS_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 objects (tables, views, …)
• <DB_NAME> with the name of the database
• <SCHEMA_NAME> with the name of the schema
• <APP_USER> with the username used by the application to connect to the database

and amend all parameters according to your specific PostgreSQL installation.