Skip to content
A collection of migration scripts to quickly migrate from RT2.x to RT3 (3.8.4)
Perl Shell
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.
Migrate
AUTHORS Added copyright, author, and more documentation Jul 9, 2009
COPYING
IdCache.pm
POST.sql
PRE.sql
README
cli.pl
debugstorable
fillstorable
import.sh
log
onlyone.pl Fix initialisation of the Migrate::Tickets class Jul 10, 2009

README

1.0 introduction
2.0 Running the migration
2.1 Commands
2.2 Statistics that I had whilst migrating
2.3 Performance for these numbers
3.0 verifying the migration
4.0 Support and Feedback



1.0 Introduction

These scripts together make it possible to migrate from Request Tracker 2.* to
version 3.8.2.

To be able to make these scripts I have used the following existing
code to reverse engineer how RT has changed over the years:

- RT2's code (version 2.0.15 to be exact)
- rt-2.0-to-dumpfile (a commandline client part of RT-Extension-RT2toRT3)
- dumpfile-to-rt-3.0 (same package)
- RT3's code (version 3.8.2)



2.0 Concepts in the migration

The migration process has been split up in 2 parts for a good reason.

Originally I wrote a translation engine that directly  connected with both
databases through perl's DBI class. After testing the migration of the 
Users, Groups and GroupMembers table, the time taken to migrate was already
past the 40 minutes. This was running on an old i686 box, so more hardware
could have helped, and I did not tune the receiving database engine.

Our current RT2 install runs version 2.0.15 on an old linux debian
distribution. A mysql server on the same box contains all the data.

Our new setup for RT3 runs version 3.8.2 of RequestTracker on a debian
lenny system, using a PostgreSQL backend for storage of the tickets.

Since importing into PostgreSQL is alot faster with COPY FROM statements
I decided to split up the migration: The first part is retrieving the
data from MySQL, modifying the data where needed, and creating CSV files
for every destination table in PostgreSQL.

These CSV files can then be imported in your existing PostgreSQL install
by simply feeding them to PostgreSQLs commandline interface (psql).


I'm presuming that your RT3.8.2 setup works.



2.1 Commands

Step 1: Load your mysql dump from 2.0.15's mysql server into a local
	mysqld.

Step 2: edit cli.pl to point to the correct mysql database with
        some credentials that can read the info from the tables.
	Its now using user 'root', without password, database 'rt2'.

Step 3: run cli.pl.

Step 4: run psql against your new postgres database with the SQL commands
	from the file PRE.sql

	$ psql -u postgres rt3 < PRE.sql

Step 5: import the CSV files into postgres

	$ cd csv
	$ for file in .....; do \
	    echo "importing $file"; \
	    psql -e -1 rt3 < $file; \
	    done

	I included an "import.sh", but that contains an assumption about
	the size of your attachments table. You can edit it to your likes.
	(DBI runs out of memory on 1.7m rows of attachments)

Step 6: Post import actions

Recreate the indexes in the postgres database, and fix all sequences
to a proper value:

	$ psql -u postgres rt3 < POST.sql


2.2 Statistics that I had whilst migrating

Our old mysql database has these rowcounts:

ACL:		5931
Attachments:	~ 1700000 (*)
GroupMembers:	368
Groups:		60
KeywordSelects:	68
Keywords:	284
Links:		140047
ObjectKeywords:	9678
Queues:		149
ScripActions:	26
ScripConditions: 11
Scrips:		385
Templates:	44
Tickets:	662907
Transactions:	3162010
Users:		678646
Watchers:	672587

(*) operation to get this number is slow which is one of the
   reasons to go for a different database engine)

This roughly translates into these numbers on the RT3 side:

ACL:		5929
Attachments:	1826388
Attributes:	98
CachedGroupMembers:	6038951 (*)
CustomFields:	38
CustomFieldValues:	272
GroupMembers:	2709082 (*)
Groups:		3329869 (*)
Links:		140044
ObjectCustomFields:	80
ObjectCUstomFieldValues:	9678
Principals:	4008515 (*)
Queues:		150
ScripActions:	31
ScripConditions:	12
Scrips:		496
Templates:	58
Tickets:	662907
Transactions:	3162010
Users:		678819 (*)

(*) From RT2 to 3 there was a big overhaul in the users and groups tables
    and in the Keywords setup, and in how Watchers were handled. Formerly
    a non privileged user would take up only a single row in the Users table;
    in RT3 that same user takes up 1 group, 2 principals, 3 groupmember and
    4 cachedgroupmember entries; excluding any entries in GroupMember for
    the user being a requestor for any tickets (N Tickets translates to N*4
    extra groups per Ticket and 1 groupmember (and 1 cached groupmember)
    per Watcher).

(On writing these tools, my first impression is that the ACL system
could actually be pointing to a Principal, dropping the need for
alot of groups).


2.3 Performance for these numbers

- Copying the data to the new machine, and load it in a local mysql

  Reading in a copy of that mysql database takes 98 minutes. There might
  be big improvements on this part.

- Executing cli.pl

  This is the main script that loops over the tables to export. It does
  this sequentially, and this took 48 minutes for all tables together. From
  that time, about 30 minutes is spent on the attachments table which costs
  the most IO.

  cli.pl's speed could be improved by using forks or threads on a multi cpu
  machine, but the most time is spent on reading data from mysql.

- Importing the resulting CSV's

  The import takes ~ 60 minutes on average. Again, having a faster box
  will speed up this considerably.

- Gegenerating the indexes

  Regenerating the indexes (I changed a few in the process) takes around
  33 minutes. Your mileage may vary.


3.0 Verifying the migration


Since we were using our RT2 in a certain way, I have made a few tradeoffs
for speed. We weren't using translations of Templates for example, so I didn't
dig deep to get that working properly.

A list of things that might go wrong for someone elses RT2 install:

* Default users and other default data that was created between 2.0.15
  and 3.82 will be migrated too: All non-default data is retrieved from the
  database with a simple WHERE clause. The rt-2.0-to-dumpfile script uses your
  RT2's libraries to retrieve any non-default data, so if your RT2 install
  is fresher you might want to dig into this

* Moving to anything newer than 3.8.2: That might screw up the sequences.
  For speed, the migration doesn't use the sequences in the destination
  table, but just has an internal counter per table. I have initialized
  these counters on numbers that are valid for 3.8.2.
  
* Encoding. Attachments contained the only data that actually had any pointers
  to the type of encoding used. Most other data was latin1, but might well be
  anything else. I'm afraid this migration will not solve that problem.

* I have done my best effort to translate customfields. I can't test this
  properly, because we're not actively using them.

* Links need attention; There is a site specific rewrite of the
  fsck.com-rt:: URIs.

* Queues: RT3 comes with 2 default queues, RT2 with 1. This means
  every queue gets a new id. That might screw up bookmarks to specific
  queues or searches. I can imagine this as unacceptable for some companies.

* ScripActions, Scrips, Templates: please look into your current
  Scrips setup. I'm copying additional scripactions, you might want to
  decide to not copy them at all.
  I had to remove all existing scrips from the template for RT3.

* Transactions: a few transactions in RT2 showed me an AddWatcher event
  with a NULL value for the Field column. My script is skipping such
  events. (minor)

* Attachments: This table was so big that I ran into memory limits
  with using perl's DBI driver. The data is now selected on its primary key
  between 2 positions, starting at 0, ending at 200,000 and then incrementing
  each by another 200000.
  Our data was spread out sufficiently that this worked out. If you're using
  a databaseengine that supports cursors, you could write your own bit
  of perl that does the trick.

There are lots of comments in each Migrate/Fubar.pm file.


4.0  Support and Feedback

I do not deliver support on this software. The Request Tracker project
moves towards goals that I may not follow.


Something went wrong with that request. Please try again.