MigrateMysql2PostgresqlV4

What is this page ?

As I'm currently migrating from RT v4.2.2 on Mysql to v4.2.2 on Postgresql and didn't find an easy tutorial about this, I've decided to write this quick page describingthe steps I've used.

I tested these instructions while moving from a Debian6-based RT server on Mysql to a Debian7-based RT server on Postgresql: this means I do have an old server (the "source server" and a new server the "destination server"). Of course you'll have to adapt these steps to your environment.

Prepare the new server

Prepare your Postgresql server

Edit /etc/postgresql/9.1/main/pg_hba.conf, and add the following lines before the not-commented ones

host    template1       rt_user                 127.0.0.1 255.255.255.255       password
local   template1       rt_user                                                 password 
host    rt4             rt_user                 127.0.0.1 255.255.255.255       password
local   rt4             rt_user                                                 password

Restart Postgres:

service postgresql restart

As user postgres, create the new role:

su - postgres
$ psql
postgres=# CREATE USER rt_user WITH PASSWORD 'DBPASS' CREATEDB NOCREATEUSER;
postgres=# \q

Install RT (from source in my case)

Install basic dependencies (compiling environment, perl libraries, apache, porstgresql server, ...).

Configure you RT installation (in the source tree) and enable Postgresql, depending on your options, you can choose for instance:

/path/to/extractedsources/configure --enable-graphviz --enable-gd --with-db-type=Pg --with-db-rt-pass="DBPASS" --with-web-user=www-data --with-web-group=www-data

Note that the default DB user is "rt_user" and the default dbname is "rt4". Of course you'll have to change DBPASS by your password.

Install missing dependencies:

make fixdeps

Check with

make testdeps

Install:

make install

CAUTION: do NOT initialize the database as the script may ask (do NOT run "make initdb")

If you've setup the database with initdb, you won't be able to import your cloned database. In this case, and in this case only, you can run:

/opt/rt4/sbin/rt-setup-database --action drop --dba rt_user --dba-password DBPASS

Install whatever extra RT extension you may need.

Export your source RT database

If your RT version is older than v4.2.5, You'll have first to apply a patch so that your export contains the Scrips/Queues bindings (see http://issues.bestpractical.com/Ticket/Display.html?id=29949).

The patch looks like the following for v4.2.2:

diff -u lib/RT/Migrate/Serializer.pm.orig lib/RT/Migrate/Serializer.pm
--- lib/RT/Migrate/Serializer.pm.orig   2014-05-31 08:56:52.108512591 +0200
+++ lib/RT/Migrate/Serializer.pm        2014-05-31 08:59:02.994003819 +0200
@@ -172,7 +172,7 @@
    $self->PushCollections(qw(ACL));

    # Scrips
-    $self->PushCollections(qw(Scrips ScripActions ScripConditions Templates));
+    $self->PushCollections(qw(Scrips ObjectScrips ScripActions ScripConditions Templates));

    # Attributes
    $self->PushCollections(qw(Attributes));

Then, an your source server, export your database using rt-serializer:

sbin/rt-serializer --directory /path/to/export/rt-serializer-data --clone 

Then tar the resulting directory, copy it to the new server and untar the archive.

Import your RT database

Prepare your destination database:

sbin/rt-setup-database --action create,schema,acl --dba rt_user --dba-password  DBPASS

If your RT version is older than v4.2.5, You'll have first to apply a patch so that rt-importer can import the Scrips/Queues bindings (see http://issues.bestpractical.com/Ticket/Display.html?id=29949).

The patch looks like the following for v4.2.2:

diff -u lib/RT.pm.orig lib/RT.pm
--- lib/RT.pm.orig      2014-06-02 17:00:14.879786262 +0200
+++ lib/RT.pm   2014-06-02 17:00:40.560077022 +0200
@@ -524,6 +524,7 @@
        RT::ScripAction
        RT::ScripCondition
        RT::Scrip
+       RT::ObjectScrip
        RT::Group
        RT::GroupMember
        RT::CustomField

Import the Database:

sbin/rt-importer /path/to/exported/rt-serializer-data

We now need to fix the Postgresql sequence numbers as they are not updated by rt-importer, see http://issues.bestpractical.com/Ticket/Display.html?id=29158. In order to automatically fix your sequences, you can create the following script as "Update-Pg-Sequences.sh":

#!/bin/bash
# Request-Tracker mygration to Postgresql Companion script
#-------------------------------------------------------
# When migrating to postgresql, rt-importer doesn't update the
# last_value of sequences.
#
   # This script is a quick and dirty fix that updates the Sequences
# last value from the MAX_id used in each table
#
      # This script must be run as user postgres:
# sudo -u postgres /path/to/Update-Pg-Sequences.sh
#set -x
      DBNAME="rt4"
      cd /tmp
   # First list all sequences from DB
LIST="$(psql -t -d $DBNAME -c 'select sequence_name from information_schema.sequences;')"
declare -A before_seq
declare -A after_seq
# For each sequence, read the last value, then update to the MAX id, and
# read again the last value
for i in $LIST
do
# Let's parse the table name and columnname that corresponds to the sequence
 tablename=$(echo $i | cut -d"_" -f1)
 columnname=$(echo $i | cut -d"_" -f2)
# Read the initial sequence last_value
 sql_lastval="select last_value from $i"
 before_seq["$i"]=$(psql -t -d $DBNAME -c "$sql_lastval" | tr -d ' ')
# Setting the sequence to the MAX of id(s)
 sql="select setval('$i',max($columnname)) from $tablename;"
 cmd=$(psql -t -d $DBNAME -c "$sql")
# Let's read again the sequence last_value
# Yes I know this last step is not required as we already have the value in cmd
# but I like to double check the results ;-)
 after_seq["$i"]=$(psql -t -d $DBNAME -c "$sql_lastval" | tr -d ' ')
 echo "Sequence '$i', before=${before_seq[$i]}, after=${after_seq[$i]}"
done

Make it executable:

chown 777 /path/to/Update-Pg-Sequences.sh

Then run it:

sudo -u postgres /path/to/Update-Pg-Sequences.sh

Retrieve your old parameters

Now is time to complete your configuration file RT_SiteConfig.pm with your parameters from the source server.

Then setup your Webserver as required, and start it.

You can setup your mailgate as well as your crontab entries.

Don't forget any customization you may have done such as local html pages.

FullText indexing

You may now want to setup FullText indexes:

sbin/rt-setup-fulltext-index --dba rt_user --dba-password DBPASS

Then add the proposed setup lines to your /opt/rt4/etc/RT_SiteConfig.pm file, for instance:

Set( %FullTextSearch,
   Enable     => 1,
   Indexed    => 1,
   Column     => 'ContentIndex',
   Table      => 'Attachments',
);

Now run a first indexing

./sbin/rt-fulltext-indexer -–all 

Then make it a cron job:

ln -s /opt/rt4/sbin/rt-fulltext-indexer /etc/cron.hourly

Your RT server is now migrated to Postgresql.