Configure a PostgreSQL Event Datastore

MMAP uses a database to store analytic events generated by Media Server (for example, transcription data), so you must install and configure a PostgreSQL database server.

Install PostgreSQL

Install the PostgreSQL database server for your platform. For more information, refer to the 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.

  • Host
  • Port
  • User name
  • Password
  • Database name

Create the MMAP Database Schema

After installing PostgreSQL and creating a database, run the following scripts that are supplied in the MMAP archive:

  • 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 -d<database> --username=<user> -f <path-to-script>

    where,

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

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

    For example:

    psql -dpostgres --username=postgres -f schema.sql 
    psql -dpostgres --username=postgres -f stored-procedures.sql
    psql -dpostgres --username=postgres -f searchable-text.sql 
    psql -dpostgres --username=postgres -f partition-word-table.sql 

    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

    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

Configure MMAP to use PostgreSQL

Configure MMAP to use PostgreSQL as its event datastore.

To configure MMAP to use PostgreSQL

  1. Copy the PostgreSQL JDBC driver .jar file into the /mmap/modules/org/postgresql/main directory, where the mmap directory is the directory you created in Install Media Management and Analysis Platform. You might need to create some of these directories.

  2. In the /mmap/modules/org/postgresql/main directory, create a file named module.xml with the following content. (If you installed the PostgreSQL JDBC driver in another location, modify the path attribute appropriately).

    <?xml version="1.0" encoding="UTF-8"?>
    <module xmlns="urn:jboss:module:1.2" name="org.postgresql">
        <resources>
            <resource-root path="postgresql-42.2.5.jar"/>
        </resources>
        <dependencies>
            <module name="javax.api"/>
            <module name="javax.transaction.api"/>
        </dependencies>
    </module>
    
  3. Open the file /mmap/standalone/configuration/avalanche.properties in a text editor. Set the values of the following properties and then save the file:

    avalanche.vms.database.host The host name of your database server.
    avalanche.vms.database.port The port of your database server.
    avalanche.vms.database.databaseName The name of the database you created.
    avalanche.vms.database.user The name of the database user you created for the MMAP application to use.
    avalanche.vms.database.password The password for the database user.

    For example:

    # Details for connecting to the PostgreSQL database
    avalanche.vms.database.host=localhost
    avalanche.vms.database.port=5432
    avalanche.vms.database.databaseName=mmap-events
    avalanche.vms.database.user=mmap
    avalanche.vms.database.password=password