Introduction

From version 1.2.13, pulse™ supports using a PostgreSQL rather than the embedded HSQL database. As of version 1.2.15, support for MySQL has also been added. This support will be made more prominent in pulse™ 2.0, but it may be used now in 1.2. This cookbook entry describes how to migrate your existing HSQL data over to MySQL or PostgreSQL.

Warning Once you have migrated to an external database, pulse™ will no longer perform automatic backups of the database on upgrade. Instead, you should use the export command to dump the state of your database prior to all upgrades.

Preparation

Upgrade

You must upgrade to pulse™ 1.2.15 or later before starting the migration. The 1.2.15 upgrade includes schema changes required to support these external databases.

Note You cannot perform an export/import and upgrade at the same time. Exported data can only be imported into exactly the same pulse™ version.

Backup

Before migrating, you should create a backup of your current pulse™ database:

1. Shutdown pulse™
2. Take a copy or archive of the $PULSE_DATA/database directory.

Prepare

The migration process involves exporting your current pulse™ data to an XML file. You should ensure that you have sufficient disk space to the export file. Check the size of your current $PULSE_DATA/database directory, and ensure there is enough free disk space to hold two times that amount.

The External Database

MySQL

Installation

You will need to install an instance of MySQL if you do not already have one available. We primarily test against MySQL 5.0, and therefore recommend this version. Earlier versions may not support all features required by pulse™, and later versions are not yet stable. Details of installation are outside the scope of this document.

Pulse Database

You should create a new database (also referred to as a "schema" in MySQL tools) for pulse™ to use. In this document we will assume that you call the database "pulse". It is also best to create a dedicated "pulse" user in your database. You must ensure this user has full write access to the "pulse" database, including the ability to create tables and apply constraints.

JDBC Driver

To allow pulse™ to access MySQL, you need to install the MySQL driver jar (also known as Connector/J). This file can be obtained for MySQL 5.0 from http://dev.mysql.com/downloads/connector/j/5.0.html. To install the jar, copy it into your pulse™ lib directory. For example:

# cp /usr/share/java/mysql-connector-java-5.0.4-bin.jar $PULSE_HOME/versions/0102015000/lib

Note that the build number (0102015000) changes with each pulse™ release. Also note that you will need to repeat this step each time you upgrade pulse™.

PostgreSQL

Installation

Naturally, you will need an instance of PostgreSQL running before you can migrate. We primarily test and therefore recommend version 8.1. Details for installing PostgreSQL are outside the scope of this document.

Pulse Database

Once PostgreSQL is installed, you will need to create an empty database for pulse™ to use. We also recommend creating a dedicated pulse user for connecting to the database. One way to achieve this is to use the createuser and createdb command line tools. For example, on a Unix-like system which already has a 'pulse' login account:

# sudo -u postgres createuser -U postgres -d -A -P pulse
# sudo -u pulse createdb -U pulse -E UNICODE pulse

JDBC Driver

To allow pulse™ to access PostgreSQL, you need to install the PostgreSQL JDBC 3 driver jar. This file can be obtained for your PostgreSQL version from http://jdbc.postgresql.org/download.html. To install the jar, copy it into your pulse™ lib directory. For example:

# cp /usr/share/java/postgresql-jdbc3-8.1.jar $PULSE_HOME/versions/0102015000/lib

Note that the build number (0102015000) changes with each pulse™ release. Also note that you will need to repeat this step each time you upgrade pulse™.

Migration

The migration involves exporting your current database, updating your database configuration, and importing into the new database.

Export

First, ensure pulse™ is cleanly shutdown using the service tools or shutdown command. Then, use the export command to export your current database to a file:

# pulse export pulse.xml

The last argument is the path of the file to export to. The export may take a significant amount of time, so please be patient.

Reconfigure Your Datasource

Next you need to configure pulse™ to talk to the PostgreSQL database. To do so, edit the file $PULSE_DATA/config/database.properties (which will be new in 1.2.13). If this file does not exist, you may copy it from the template $PULSE_HOME/versions/0102015000/system/config/database.properties.template. Just remove the .template extension and the DO NOT EDIT banner from the copied file. The file itself has commented lines for typical MySQL and PostgreSQL setups. The properties you need to configure are summarised in the table below:

Now when pulse™ next runs it will connect to the external database.

Import Your Data

Now you can import your data into the external database using the import command, passing the path of the file you exported to:

# pulse import pulse.xml

The import can take significant time, so please be patient while it runs.

Testing

That's it! Now you should restart pulse™ and test that the data was successfully imported. If you have any problems or questions, please contact support@zutubi.com.