Open topic with navigation

# Configure a PostgreSQL Event Datastore

MMAP uses a database to store analytic events generated by Media Server (for example, transcription data). By default, MMAP is configured to use an embedded H2 database that can be used for initial testing purposes; however, you must install and configure a PostgreSQL event datastore for use in production systems.

## Prerequisites

- PostgreSQL

  To use PostgreSQL for the event datastore, you must install the PostgreSQL for your platform. For installation instructions, see your PostgreSQL documentation.

  Download from: | http://www.postgresql.org/

- PostgreSQL JDBC driver

  The PostgreSQL JDBC driver version binary JAR file must match the database server being used.

  Download from: | https://jdbc.postgresql.org/download.html

## Install PostgreSQL

Install the PostgreSQL database server for your platform. For more information, see your PostgreSQL documentation.

During installation and configuration of PostgreSQL, take note of the following items. These items are required later during configuration of MMAP to use the PostgreSQL server.

- Database name

- Host

- Port

- User name

- Password

## Configure MMAP to use PostgreSQL

Configure MMAP to use PostgreSQL as its event datastore.

To configure MMAP to use PostgreSQL

1. In the `/mmap/jboss-eap-6.2` directory created in step 1 of Install Media Management and Analysis Platform, navigate to the `modules/org` subdirectory.

2. In the `modules/org` directory, create a directory structure as follows:

       postgresql/main

3. Copy the PostgreSQL JDBC driver `.jar` file into the `postgresql/main` directory.

4. In the `main` directory, create a file named `module.xml` with the following content:

       <?xml version="1.0" encoding="UTF-8"?>
       <module xmlns="urn:jboss:module:1.0" name="org.postgresql">
          <resources>
             <resource-root path="postgresql-9.1-903.jdbc4.jar"/>
          </resources>
          <dependencies>
            <module name="javax.api"/>
            <module name="javax.transaction.api"/>
          </dependencies>
       </module>

   NOTE: The `<resource-root path="postgresql-9.1-903.jdbc4.jar"/>` parameter is the `.jar` file name for the PostgreSQL JDBC driver version. Replace `resource-root path` with the `.jar` file name downloaded in Prerequisites.

5. Save the `module.xml` file.

6. In the`/mmap/jboss-eap-6.2` directory, navigate to the `standalone/configuration` directory and open `avalanche.xml` in a text editor.

   1. Add three new system properties in the `<system-properties>` section:

          <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL82Dialect"/>
          <property name="avalanche.vms.database.timezone" value="UTC"/>
          <property name="hibernate.hbm2ddl.auto" value=""/>

   2. Replace the following `<datasources>` section that uses H2:

          <datasource jndi-name="java:/datasources/EventsDS" pool-name="EventsDS" enabled="true" use-java-context="true">
            <connection-url>jdbc:h2:file:${jboss.server.data.dir}/h2/events;AUTO_SERVER=TRUE;
          INIT=runscript from 'classpath:/com/autonomy/avalanche/persistence/h2/init.sql'\;</connection-url>
            <driver>h2</driver>
            <security>
               <user-name>${avalanche.vms.database.user:sa}</user-name>
               <password>${avalanche.vms.database.password:sa}</password>
            </security>
          </datasource>

      with the following `datasource` that uses PostgreSQL:

          <datasource jndi-name="java:/datasources/EventsDS" pool-name="EventsDS" enabled="true" use-java-context="true">
            <connection-url>jdbc:postgresql://host:port/events</connection-url>
            <driver>postgresql</driver>
            <security>
               <user-name>${avalanche.vms.database.user:sa}</user-name>
               <password>${avalanche.vms.database.password:sa}</password>
            </security>
          </datasource>

      In the `connection-url` section, replace the following settings with the information that you saved when you installed PostgreSQL.

      - Replace `host` with the host name of your server.

      - Replace `port` with the port number that the PostgreSQL communicates on.

      - Replace `events` with the database name.

      In the `security` section, replace the following settings with the information that you saved.

      - Replace `user-name` with the user name of a user account created for the PostgreSQL database.

      - Replace `password` with the password of the user account.

      For example:

          <datasource jndi-name="java:/datasources/EventsDS" pool-name="EventsDS" enabled="true" use-java-context="true">
            <connection-url>jdbc:postgresql://localhost:5432/postgres</connection-url>
            <driver>postgresql</driver>
            <security>
               <user-name>${avalanche.vms.database.user:postgres}</user-name>
               <password>${avalanche.vms.database.password:myPassword}</password>
            </security>
          </datasource>

   3. Add a new driver property in the `<drivers>` section:

          <driver name="postgresql" module="org.postgresql">
             <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
          </driver>

7. Save the `avalanche.xml` file.

8. Restart the JBoss application server.

## Create the MMAP Database Schema in PostgreSQL

The MMAP archive contains the following SQL scripts that create the MMAP database schema in PostgreSQL:

- `schema.sql`
- `stored-procedures.sql`
- `searchable-text.sql`
- `partition-word-table.sql`

To run the SQL scripts on Windows

- Open a command-line window and use the tool `psql.exe` (supplied in the `bin` directory of your PostgreSQL installation) to run each script. Ensure that you run `schema.sql` first. You can use the following command:

      psql.exe -f <path-to-script> -d<database> --username=<user>

  where,

  - `<path-to-script>` is the path of the script.
  - `<database>` is the database name.
  - `<user>` is the user account created for the PostgreSQL database.

  This will run the script against the PostgreSQL instance listening on the default port (`5432`).

  For example:

      psql.exe -f c:\mmap\jboss-eap-6.2\sql\postgresql\schema.sql -dpostgres --username=postgres

      psql.exe -f c:\mmap\jboss-eap-6.2\sql\postgresql\stored-procedures.sql -dpostgres --username=postgres

      psql.exe -f c:\mmap\jboss-eap-6.2\sql\postgresql\searchable-text.sql -dpostgres --username=postgres

      psql.exe -f c:\mmap\jboss-eap-6.2\sql\postgresql\partition-word-table.sql -dpostgres --username=postgres

  When prompted, type the password for the user.

To run the SQL scripts on UNIX

1. Run the script `schema.sql` by typing the following commands, where `<database>` is the database name.

       sudo su - postgres
       psql -hlocalhost -d<database> -f schema.sql

   TIP: The `-f` parameter accepts either an absolute path or a path relative to the current directory. For more information about `psql` parameters, refer to the PostgreSQL documentation.

   The script runs against the PostgreSQL instance listening on the default port (`5432`).

2. Run the scripts `stored-procedures.sql`, `searchable-text.sql`, and `partition-word-table.sql`:

       psql -hlocalhost -d<database> -f stored-procedures.sql
       psql -hlocalhost -d<database> -f searchable-text.sql
       psql -hlocalhost -d<database> -f partition-word-table.sql

© 2016-2018 Micro Focus or one of its affiliates | Send documentation feedback to Micro Focus Close Send documentation feedback To open the configured email client on this computer, open an email window. Otherwise, copy the information below to a web mail client, and send this email to swpdl.idoldocsfeedback@microfocus.com. We appreciate your feedback! Product: Topic Title: Help Topic: Feedback:

_FT_HTML5_bannerTitle.htm