Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Management Console (3.7)
  3. Setting Up MMC
  4. Persisting MMC Data On External Databases

  5. Persisting MMC Data to PostgreSQL

Persisting MMC Data to PostgreSQL

This document covers how to persist environment and transaction data to PostgreSQL databases. Follow the instructions to PostgreSQL Installation, then follow the specific instructions for Persisting Environment Data or Persisting Transaction Data persistence to set up your user, create your database, and configure your MMC instance to persist your data.

Remember that environment data and transaction data must exist in two separate databases (regardless of whether they are on the same server or use the same platform).

Jump to:

Assumptions

This document assumes that you have an understanding of the data persistence options for environment and transaction data and that you are familiar with the basic Architecture of the Mule Management Console. It also assumes that you have installed:

  • MMC Version 3.6.0. Download it from the customer portal if you need to upgrade to this version.

  • a Web application server such as Tcat, Tomcat, or JBoss. Please refer to the Hardware and Software Requirements for the latest information about hardware requirements and supported application servers, Java Runtime Environments, and browsers.

For practical purposes, this document mentions Tomcat as the servlet container; however, the instructions contained here apply also to Tcat or JBoss. If you are using Tcat or JBoss, make sure to unpack the Management Console .war file in the appropriate directory. For additional information, see Installing the Production Version of MMC.

In this document, MMC_HOME refers to the directory where MMC is installed.

If you are using the trial version, this directory is inside the root of the embedded Tomcat server from which MMC runs. Depending on where you placed the bundled installation, the path is similar to:

INSTALLATION_ROOT/mmc-distribution-mule-console-bundle-3.6.0/mmc-3.6.0/apache-tomcat-7.0.52/webapps/mmc-3.6.0

If you are using a production installation in an application server such as Tomcat, the path should be similar to TOMCAT_ROOT/webapps/mmc-console-3.6.0. For example: /var/lib/tomcat6/webapps/mmc-console-3.6.0

PostgreSQL Installation

The PostgreSQL installation outlined here is basic: the database server is installed on the local machine and listening on the default port, with security deactivated (unsecured connection to the database by the database owner).

Tools

  • PostgreSQL 9.1.3 (Download)

  • pgAdmin III (select it when installing Postgres)

  • PostgresSQL driver, included with the MMC installation. See the Database Driver section for details

Installing the Database

The easiest way to install the database is by using the Enterprise One-Click installer, which is freely available for download.

You need administrator privileges on your machine for the installation to proceed.
For further details, refer to the quick Postgres tutorials for Linux, Mac OS X and Windows.

Version 9.1 installs with the following default values:

  • Default port: 5432

  • Default admin user: postgres

Mule recommends installing one of Postgres’s GUI administration utilities, such as phpPdAdmin (Web-based) or pgAdmin III. These can be selected for installation during the Postgres installation process. In this document, we will use pgAdmin III.

Setting Up the Database Listener

Locate the file postgresql.conf, which is under the root directory of the Postgres installation, for example in /opt/PostgreSQL/9.1/data or /opt/PostgreSQL/9.1/main.

Open the postgresql.conf file for editing, then go to the "Connections and Authentication" section. Locate the line that begins with: listen_addresses =. If the line reads:

listen_addresses = 'localhost'

Then modify it to read as below.

listen_addresses = '*'

This will make PostgreSQL listen on all of the available interfaces.

Locate the file pg_hba.conf, which should be in the same data directory of the Postgres installation. In this file, find the line that begins with: host all. It will probably look similar to the following.

host    all             all             127.0.0.1/32            md5

Modify it as per below.

host    all             all             0.0.0.0/0       trust

This ensures that Postgres accepts all attempts to connect from any host, without asking for an encrypted password. After testing the database connection, you may wish to modify this file to restrict connections according to the security needs of your site.

(For a detailed description of the pg_hba.conf file, see the online documentation.)

After setting up the listener, the database must be restarted. To do this, open a terminal window (Unix terminal or DOS prompt) and use the pg_ctl command as described below.

  • To restart the PostgreSQL server, run: pg_ctl restart

  • To verify the state of the PostgreSQL server, run: pg_ctl status

Run pg_ctl on Unix

The pg_ctl command can usually be run by any user on the system, but its location is not contained in users' PATH environment variable, so attempting to run it will cause the shell to return a command not found error. To run the command, proceed in one of two ways:

  • Locate the pg_ctl binary on your system, and run it by specifying the full path to it. For example:

  • Add the full path of the binary to your PATH environment variable, for example with the command:

    Then just run pg_ctl.

When running pg_ctl status, you may get the following output:

pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

In that case, issue the command with the following options: pg_ctl status -D <Postgres data directory>, as shown below.

pg_ctl status -D /opt/PostgreSQL/9.1/data

The same is valid for the restart option of the pg_ctl command. For example, to restart the PostgreSQL server:

pg_ctl restart -D /opt/PostgreSQL/9.1/data

The next step is to create the database user. Follow the relevant setup instructions below, depending on whether you are setting up a database for Persisting Environment Data or Persisting Transaction Data.

Persisting Environment Data

To set up PostgreSQL to persist your MMC environment data, you need to complete four steps:

  1. Create the database user

  2. Create the database

  3. Verify the database

  4. Set up MMC

Creating the Database User

You can create a new database user, with the following parameters:

  • Role name: mmc_status

  • Password: mmc123

Using pgAdmin III

  1. Log in to the database server as role postgres:

    1. In pgAdmin III’s object browser (on the right-hand pane), right-click server PostgreSQL on localhost, then select Connect.

  2. On the Object Browser, right-click Login Roles, then select New Login Role.

  3. At the New Login Role dialog box, type mmc_status in the Role name field.

  4. Go to the Definition tab in the dialog box, and type the password mmc123 in both Password fields.

  5. Click OK to close the New Login Role dialog box.

Creating the New Database

You can create a database called mmc_persistency_status, owned by role mmc_status.

Using pgAdmin III:

  1. Using the Object Browser, navigate to Databases > New Database.

  2. In the dialog box, type mmc_persistency_status in the Name field.

  3. In the Owner field, select mmc_status.

  4. In the Definition tab, ensure that Encoding is set to UTF8.

  5. Click OK to close the New Database dialog box.

Verifying the New Database

Use PostgreSQL’s psql command-line utility to log in to database mmc_persistency_status as user mmc_status. To do this, open a terminal and run:

psql postgres -Ummc_status

When you run this command, psql should prompt for the user’s password. After typing it, you should get a prompt similar to the following:

mmc_persistency_status=#

This indicates that you have successfully connected to the mmc_persistency_status database as user mmc_status.

If you can’t login using the psql command

Depending on you PostgreSQL configuration, you may get the following error when trying to connect to the database:

This probably means that PostgreSQL is configured to accept connections for the specified user only if the operating system first authenticates that user. So, for example, to connect as user mmc_status, you would have to create a Unix account called mmc_status and run psql from that account.

If you do not desire this configuration, check for the following line in the PostgreSQL configuration file pg_hba.conf:

If the line exists, modify it as per below.

An example of the full login command and output:

mitra:/opt/PostgreSQL/9.1/bin$ ./psql mmc_persistency_status -Ummc_status
Password for user mmc_status:
psql.bin (9.1.3)
Type "help" for help.
mmc_persistency_status=#

To exit psql, type q, then press Enter.

Creating the Tables

On the first run, JCR automatically creates all the tables needed to store persistent MMC information. However, you have to manually create some tables that store Quartz job info; otherwise at some point the following error occurs:

ERROR: relation "qrtz_locks" does not exist
  Position: 15 [See nested exception: org.postgresql.util.PSQLException: ERROR: relation "qrtz_locks" does not exist
  Position: 15]]

To create and insert the tables:

  1. Navigate to the directory <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/classes/quartz.

  2. Execute the tables_postgres.sql script on the target database mmc_persistency_status. One way to do this is by running the following command:

psql -d mmc_persistency_status -Ummc_status -f tables_postgres.sql

If necessary, in the above command specify <full path>/tables_postgres.sql.

At this point, the Postgres database should be completely defined, as shown below.

postgres_db

Setting Up MMC to Use PostgreSQL for Persisting Environment Data

Database Driver

To check whether your MMC installation has the PostgreSQL driver installed, go to the directory MMC_HOME/WEB-INF/lib, and look for a file called <postgresql-<version>.jdbc<version>.jar, for example postgresql-9.1-901.jdbc3.jar.

If you do not have such a file, the driver is not installed and you will need to install it. If you do have this file, you may wish to check that it is the latest version, and update it if necessary. Both actions are explained below.

Installing or Updating the PostgreSQL JDBC Driver

  1. Go to the PostgresSQL driver download site and download the .jar file for the latest version. For example, postgresql-9.3-1100.jdbc41.jar.

  2. Ensure that MMC is not running.

  3. Copy the .jar file to the directory MMC_HOME/WEB-INF/lib.

  4. If the directory contains a previous version of the driver, delete it.

MMC Configuration

Configuring MMC to store data on a PostgreSQL databse involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use Postgres instead of its default database

  • Modifying the file mmc-postgres.properties to set the parameters for connecting to the Postgres database

Modifying web.xml

  1. In the directory MMC_HOME/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  1. Delete the string env-derby, then replace it with env-postgres, as shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-postgres</param-value>
</context-param>

  1. If you are also planning to Persisting Transaction Data to PostgreSQL, delete the string tracking-h2 and replace it with tracking-postgres.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data. For a quick instructions for all supported database servers, see Configuring MMC for External Databases - Quick Reference.
Modifying mmc-postgres.properties

  1. In the directory MMC_HOME/WEB-INF/classes/META-INF/databases, locate the file mmc-postgres.properties, then open it for editing.

  2. The table below lists the settings contained in the file. Modify the values as needed. In general, the only values that you should need to modify are env.username, env.password, env.host, env.port, and env.dbschema.

Parameter Description Default

env.driver

Driver to use for connecting to the database

org.postgresql.Driver

env.script

Script to use for creating the tables in the target database

postgres

env.username

Database user

mmc_status

env.password

Password for the database user

mmc123

env.host

Hostname or IP address where the database server is listening

localhost

env.port

Port where the database server is listening

5432

env.url

URL for connecting to the database

jdbc:postgresql://${env.host}:${env.port}/${env.dbschema}

env.dbschema

Database to connect to

mmc_persistency_status

+ . Save the file with your modifications, if any.

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store environment data on the external Postgres database that you specified.

Disaster Recovery of Environment Data

Out of the box, MMC stores persistent state data in the folder mmc-data, which is at MULE_HOME/mmc-data if using an application server. If the database files become corrupted, you’ll probably have to delete mmc-data and start from scratch, unless you have a backup copy of mmc-data. But having a backup copy of mmc-data does not cover a catastrophic failure with complete data loss on the MMC host itself, nor does it allow for an active-passive configuration for immediate recovery.

One possible solution is to backup the database to a single file, which can then be copied to another machine. If the need for immediate recovery arises, this file can be used to restore the database to its original state.

When you restore MMC to a previous state, be aware of the following:

  • You are restoring MMC state data. This is not related to the persistence of Business Events, which use a completely different mechanism to store data.

  • Registered servers at the time of the backup are restored, which means that one of the following situations may arise:

    • A server is paired to another Mule instance. In this case, “unpair” the server through MMC, then re-pair it. This can affect deployments and server groups.

    • A server does not exist anymore. Unpair the server.

    • Another server is using the same IP and port as the original server. Try to identify the original server’s current IP and port, then re-pair.

    • A server is correctly connected, but after the backup, deployed and/or undeployed apps are not shown or are shown incorrectly. Undeploy/Redeploy as needed to eliminate the unreconciled state.

Scenario

  • Database server: PostgreSQL 9.1

  • MMC is connected to Postgres

  • A database is already created. For this example the following parameters will be used:

    • Role: mmc_status (with same permissions as the "postgres" role)

    • Database name: mmc.test

      • Encoding: UTF8

      • Owner: mmc_status

  • Tool to access database: pgAdmin III

Backing up the Database

To backup the mmc.test database, complete the following steps:

  1. Login to pgAdmin III as admin (role postgres).

  2. On the object browser on the left-hand pane, go to Server Groups > PostgreSQL 9.1 > Databases > mmc.test.

  3. Right-click on mmc.test, then navigate to Backup > File Options.

  4. Select the following options:

    • Format: Tar

    • Encoding: UTF8

    • Rolename: mmc_status

    • Filename: <Suitable name and folder>

  5. Click Backup to create a tar archive of the database at the location you specified.

Restoring the Database

To restore the mmc.test database, complete the following steps:

  1. Go to the mmc-data folder (at <Mule install path>/.mule/mmc/mmc-data) and delete the following folders:

    • db (if it exists)

    • repository

    • tracking (this is necessary to avoid generating several stacktraces related to JCR)

    • you may need to also delete workspaces/<name of your workspace>/index

  2. Login into pgAdmin III as admin (role postgres).

  3. On the object browser, make sure that the database called mmc.test is defined.

  4. Make sure that all the tables that may be defined on the database are dropped.

  5. Right click on mmc.test, select Restore.

  6. On the File Options tab, select:

    • Filename: <Database backup file>

    • Format: Custom or tar

    • Rolename: mmc_status

  7. Click Restore.

Persisting Transaction Data

To set up PostgreSQL to persist your MMC transaction data, you need to complete three steps:

  1. Create the database user.

  2. Create the database.

  3. Verify the database.

  4. Set up MMC.

Creating the Database User

You can create a user with the following parameters:

  • Role name: tracker

  • Password: tracker

Using pgAdmin III

  1. Log in to the database server as user postgres:

    1. In pgAdmin III’s object browser (on the right-hand pane), right-click server PostgreSQL on localhost, then select Connect.

  2. On the Object Browser, right-click Login Roles, then select New Login Role.

  3. At the New Login Role dialog box, type tracker in the Role name field.

  4. Go to the Definition tab in the dialog box, and type the password tracker in both Password fields.

  5. Click OK to close the New Login Role dialog box.

Creating the New Database

You can create a database called mmc_persistency_tracking, owned by user tracker.

Using pgAdmin III

  1. Using the Object Browser, navigate to Databases > New Database.

  2. In the dialog box, type mmc_persistency_tracking in the Name field.

  3. In the Owner field, select tracker.

  4. In the Definition tab, ensure that Encoding is set to UTF-8.

  5. Click OK to close the New Database dialog box.

Verifying the New Database

Use PostgreSQL’s psql command-line utility to log in to database mmc_persistency_tracking as user tracker. To do this, open a terminal and run:

psql postgres -Utracker

When you run this command, psql should prompt for the user’s password. After typing it, you should get a prompt similar to the following:

mmc_persistency_tracking=#

This indicates that you have successfully connected to the mmc_persistency_tracking database as user tracker.

If you can’t login using the psql command

Depending on you PostgreSQL configuration, you may get the following error when trying to connect to the database:

This probably means that PostgreSQL is configured to accept connections for the specified user only if the operating system first authenticates that user. So, for example, to connect as user mmc_status, you would have to create a Unix account called mmc_status and run psql from that account.

If you do not desire this configuration, check for the following line in the PostgreSQL configuration file pg_hba.conf:

If the line exists, modify it as per below.

An example of the full login command and output:

mitra:/opt/PostgreSQL/9.1/bin$ ./psql mmc_persistency_tracking -Utracker
Password for user tracker:
psql.bin (9.1.3)
Type "help" for help.
mmc_persistency_tracking=#

To exit psql, type q, then press Enter.

Setting Up MMC to Use PostgreSQL for Persisting Transaction Data

See the section Database Driver in this document to install or verify your installation of the PostgreSQL database driver.

MMC Configuration

Configuring MMC to store Business Events data on a PostgreSQL databse involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use Postgres instead of its default database

  • Modifying the file tracking-persistence-postgres.properties to set the parameters for connecting to the Postgres database

Modifying web.xml

  1. In the directory MMC_HOME/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  1. Delete the string tracking-h2, then replace it with tracking-postgres, as shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-postgres,env-derby</param-value>
</context-param>

  1. If you are also planning to Persisting Environment Data to PostgreSQL, delete the string env-derby and replace it with env-postgres.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data. For a quick instructions for all supported database servers, see Configuring MMC for External Databases - Quick Reference.

Modifying tracking-persistence-postgres.properties

  1. In the directory MMC_HOME/WEB-INF/classes/META-INF/databases, locate the file tracking-persistence-postgres.properties, then open it for editing.

  2. Modify the included settings as needed, according to the table below. In general, the only values that you should need to modify are mmc.tracking.db.username, mmc.tracking.db.password, mmc.tracking.db.host, mmc.tracking.db.port and mmc.tracking.db.dbname.

Parameter Description Default

mmc.tracking.db.platform

Type of database server to connect to

postgres

mmc.tracking.db.driver

Driver to use for connecting to the database

org.postgresql.Driver

mmc.tracking.db.host

Hostname or IP address where the database server is listening

localhost

mmc.tracking.db.port

Port where the database server is listening

5432

mmc.tracking.db.url

URL for connecting to the database

jdbc:postgresql://${mmc.tracking.db.host}:${mmc.tracking.db.port}/${mmc.tracking.db.dbname}

mmc.tracking.db.username

Database user

tracker

mmc.tracking.db.password

Password for the database user

tracker

mmc.tracking.db.dbname

Database to connect to

mmc_persistency_tracking

mmc.max.events.exception.details.length

Number of characters from a Business Events exception that will be stored in the tracking database. The maximum allowed is 261120.

8000

+ . Save the file with your modifications, if any.

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store tracking data on the external Postgres database that you specified.

Troubleshooting Tips

If you have installed the database on a remote host and experience problems, ensure that network connectivity to the database is working. Open a terminal (Unix or DOS) and run: telnet <host> <port>

Output should be similar to the following.

mitra:~$ telnet dbserver 5432
Trying ::1...
Connected to dbserver.
Escape character is '^]'.

The above output indicates a successful connection to host dbserver on port 5432. A “connection refused” error indicates that nothing is listening on the specified host and port. Any other output often indicates a connectivity problem, such as a firewall blocking requests to the specified port.

See Also