Creating schemas and preparing multischema.xml

Review and complete the prerequisites to populate the database. Create the required schemas and assign the default schema to the user. Also, prepare the multischema.xml.

1. Create users and assign the default schemas to the users. The following example is of PostgreSQL. Ensure that you update the schema as per your use case.
   Pass clear text passwords for the multischema XML file and avoid passing any JVM arguments, to ensure successful execution of both upgrade and create modes.

   DROP SCHEMA oms_meta cascade;
   CREATE USER oms_meta WITH PASSWORD 'password';
   CREATE SCHEMA oms_meta;
   ALTER SCHEMA oms_meta OWNER TO oms_meta;
   ALTER ROLE oms_meta SET search_path TO oms_meta;
   
   DROP SCHEMA oms_conf cascade;
   CREATE USER oms_conf WITH PASSWORD 'password';
   CREATE SCHEMA oms_conf;
   ALTER SCHEMA oms_conf OWNER TO oms_conf;
   ALTER ROLE oms_conf SET search_path TO oms_conf;
   
   DROP SCHEMA oms_mstr cascade;
   CREATE USER oms_mstr WITH PASSWORD 'password';
   CREATE SCHEMA oms_mstr;
   ALTER SCHEMA oms_mstr OWNER TO oms_mstr;
   ALTER ROLE oms_mstr SET search_path TO oms_mstr;
   
   DROP SCHEMA oms_tran cascade;
   CREATE USER oms_tran WITH PASSWORD 'password';
   CREATE SCHEMA oms_tran;
   ALTER SCHEMA oms_tran OWNER TO oms_tran;
   ALTER ROLE oms_tran SET search_path TO oms_tran;
   
   DROP SCHEMA oms_stat cascade;
   CREATE USER oms_stat WITH PASSWORD 'password';
   CREATE SCHEMA oms_stat;
   ALTER SCHEMA oms_stat OWNER TO oms_stat;
   ALTER ROLE oms_stat SET search_path TO oms_stat;

2. Prepare the multischema.xml file.
   Note:

   • The poolids must not start with DEFAULT_*.
   • The following multischema.xml has five different poolids for five different schema types. However, you can configure it such that the same poolid can be used for different schema types to avoid unnecessary database connections.

   <?xml version="1.0" encoding="UTF-8"?>
   <colonyconfig>
     <colonies>
       <colony name="DEFAULT" pkprefix="20" version="10.0">
         <schema poolid="DFLT_META" tabletype="METADATA" />
         <schema poolid="DFLT_STAT" tabletype="STATISTICS" />
         <schema poolid="DFLT_CONF" tabletype="CONFIGURATION" />
         <schema poolid="DFLT_TRAN" tabletype="TRANSACTION" />
         <schema poolid="DFLT_MSTR" tabletype="MASTER" />
       </colony>
     </colonies>
     <pools>
       <pool id="DFLT_META">
         <jdbc>
           <param name="url" value="jdbc:postgresql://<Database Host>:<Database Port>/<Database Name>" />
           <param name="user" value="oms_meta" />
           <param name="schema" value="oms_meta" />
           <param name="driver" value="org.postgresql.Driver" />
           <param name="password" value="<Password>" />
           <param name="dbvendor" value="postgresql" />
           <param name="datasource" value="jdbc/oms_meta" />
           <param name="dbname" value="omdb" />
         </jdbc>
       </pool>
       <pool id="DFLT_STAT">
         <jdbc>
           <param name="url" value="jdbc:postgresql://<Database Host>:<Database Port>/<Database Name>" />
           <param name="user" value="oms_stat" />
           <param name="schema" value="oms_stat" />
           <param name="driver" value="org.postgresql.Driver" />
           <param name="password" value="<Password>" />
           <param name="dbvendor" value="postgresql" />
           <param name="datasource" value="jdbc/oms_stat" />
           <param name="dbname" value="omdb" />
         </jdbc>
       </pool>
       <pool id="DFLT_CONF">
         <jdbc>
           <param name="url" value="jdbc:postgresql://<Database Host>:<Database Port>/<Database Name>" />
           <param name="user" value="oms_conf" />
           <param name="schema" value="oms_conf" />
           <param name="driver" value="org.postgresql.Driver" />
           <param name="password" value="<Password>" />
           <param name="dbvendor" value="postgresql" />
           <param name="datasource" value="jdbc/oms_conf" />
           <param name="dbname" value="omdb" />
         </jdbc>
       </pool>
       <pool id="DFLT_TRAN">
         <jdbc>
           <param name="url" value="jdbc:postgresql://<Database Host>:<Database Port>/<Database Name>" />
           <param name="user" value="oms_tran" />
           <param name="schema" value="oms_tran" />
           <param name="driver" value="org.postgresql.Driver" />
           <param name="password" value="<Password>" />
           <param name="dbvendor" value="postgresql" />
           <param name="datasource" value="jdbc/oms_tran" />
           <param name="dbname" value="omdb" />
         </jdbc>
       </pool>
       <pool id="DFLT_MSTR">
         <jdbc>
           <param name="url" value="jdbc:postgresql://<Database Host>:<Database Port>/<Database Name>" />
           <param name="user" value="oms_mstr" />
           <param name="schema" value="oms_mstr" />
           <param name="password" value="password" />
           <param name="driver" value="or<Password>esql.Driver" />
           <param name="dbvendor" value="postgresql" />
           <param name="datasource" value="jdbc/omdb_mstr" />
           <param name="dbname" value="omdb" />
         </jdbc>
       </pool>
     </pools>
   </colonyconfig>