That story happened yesterday and i feel the urge to tell it, since I am likely to forget the bulk of it in the next few months. It all started as a slow day, I decided to migrate a VM to a new faster XEN server. My weapon of choice is Debian. That VM was running a support system called RT. Went on installing it, rebooting and switching IPs in order to make the new VM live and start rsync’ing content to it. All went well until i copied over the PostgreSQL data. This is when i noticed there was no database…

Funnily as it happens, I had mis-configured my current installation of RT to use sqlite3 instead of postgres. A colleague of mine (hey Sean 🙂 then gave me a hand in debugging and migrating the data. A few hours and dozen of web pages later, we migrated the data from sqlite3 onto a flat file then started on putting the data into postgres.

First thing to know is that postgres expects the data to have specific format and some fields were 0 instead of NULL. A few sed later, this worked and we migrated all the data back to the new system. It still failed, with the error Ticket creation failed: internal error.

After more digging, i discovered the index tables (under psql, do d instead of dt). We then noticed that all these index tables kept the last number of transactions, tickets, people, etc… After having queried the databases, we got the last record created for each table and edited the tables like this : select setval(‘queues_id_seq’,7);.

Since then, our ticket system is back and happily running. I guess one lesson i’ve learnt is no matter how trivial you think it is going to be, it won’t!