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 INSERT
s 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.