Migrating Request Trackers backend

I’ve been stupid enough to try to use Request Tracker with its default (sqlite) backend. Bad idea. Please read that and don’t think I am bashing sqlite, I am not.

As a note to self and to the next guy falling into that trap, here’s how it can be done:

Preparation

Install postgresql and make sure you can access it through a local user using passwords. This might mean you have to tweak postgresqls settings, notably pg_hba.conf.

If you’re using Request Tracker version 3.8.4, you can use the schema behind this link to prepare your database. Otherwise I would suggest using the tool that is provided in the installation (rt-setup-database) to do that. Your command line will have to look something like this:

  $ rt-setup-database --action schema

RT will use its database settings from /etc/request-trackerXXX/RT_SiteConfig.pm to connect to your database, so make sure that points to the new location…

Migration

Now you have two databases that have exactly the same schema, but one contains the data. Turn off your mail gateway and your apache (site is closed for now) and install the prerequisites for this script. This should mostly be the ‘pg’ gem and the ‘sqlite3’ gem. Using aptitude, this would look like this:

  $ aptitude install libpgsql-ruby libsqlite3-ruby

Running the script should be easy: Give it the location of RTs sqlite database as parameter. Adjust the pgsql connection settings inside the script to match your installation.

The script will insert all rows from sqlite into postgresql that are missing there. You can run the script a few times; it will reach a steady state the second time round that looks like this:

  ACL: Source: 26, Target: 26
  ... lots of tables ...

Those are all tables and their corresponding max(id)s.

Correct the sequences

Since all INSERTs are done giving the id of the rows the postgresql sequences will all be off and you wont be able to insert new data. This is easy to correct: Run the following lines against your database:

  select setval('acl_id_seq', (select max(id) from acl));
  select setval('attachments_id_seq', (select max(id) from attachments));
  select setval('attributes_id_seq', (select max(id) from attributes));
  select setval('cachedgroupmembers_id_seq', (select max(id) from cachedgroupmembers));
  select setval('customfields_id_seq', (select max(id) from customfields));
  select setval('customfieldvalues_id_seq', (select max(id) from customfieldvalues));
  select setval('groupmembers_id_seq', (select max(id) from groupmembers));
  select setval('groups_id_seq', (select max(id) from groups));
  select setval('links_id_seq', (select max(id) from links));
  select setval('objectcustomfields_id_s', (select max(id) from objectcustomfields));
  select setval('objectcustomfieldvalues_id_s', (select max(id) from objectcustomfieldvalues));
  select setval('principals_id_seq', (select max(id) from principals));
  select setval('queues_id_seq', (select max(id) from queues));
  select setval('scripactions_id_seq', (select max(id) from scripactions));
  select setval('scripconditions_id_seq', (select max(id) from scripconditions));
  select setval('scrips_id_seq', (select max(id) from scrips));
  select setval('templates_id_seq', (select max(id) from templates));
  select setval('tickets_id_seq', (select max(id) from tickets));
  select setval('transactions_id_seq', (select max(id) from transactions));
  select setval('users_id_seq', (select max(id) from users));

These statements are a copy of what himdel has on his blog.

Done

This should conclude your migration. Start apache and the mail gateway again and browse through your tickets – they are all there. Of course, this is at your own peril. But if all goes well, you will have migrated your Request Tracker instance to PostgreSQL in almost no time.