Planned Spontaneity

One of the greatest things about blogs and search engines is that for 90% of the things you want to do, someone else has already done it and blogged about it. Today’s post is on trac, postgres, and sqlite.

Backstory: As a part of the research I do in the Aerospace Systems Laboratory, I run a mid-sized Subversion repository and trac project so we can keep all the 100-something people involved on the same page. When I setup this giant system, I decided to use postgresql as the trac database backend (as postgresql is my database of choice).

The Problem: trac + postgresql = chaos
The database frequently double inserts rows, has odd issues with keeping the revisions in sync with Subversion, and general stability issues (due to the python library they are using).

Solution: trac + sqlite3 = well supported
sqlite seems to be the preferred database by the trac developers. Sadly, there is no way (documented or otherwise) of backing up / restoring a non-sqlite trac database. Read on for the steps to hand migrate.

Getting data out of postgres

• pg_dump -a -d -D -i –no-owner -x -h db.example.com -U trac > trac-dump.sql
• -a : dump data only
• -d : use INSERT INTO instead of COPY
• -D : explicitly specify the column names when inserting values
• -i : ignore postgresql version
• –no-owner / -x : disable the ownership / acl information that is postgres specific
• -h / -U : connection info

Preparing data for dump into sqlite

• Open trac-dump.sql in your editor of choice
• Find replace (’, E’) with (’, ‘) excluding the parentheses
• I did this by hand as there are some user texts that included this string

Preparing database structure

You have two options, you can use the SQL structure I have here or you can generate it yourself. Note, the structure I generated is for trac 0.10.2.

• Generating
• trac-admin /tmp/trac initenv
• Follow the install setup
• sqlite3 /tmp/trac/db/trac.db
• .dump /tmp/trac-skel.sql
• .exit
• Edit /tmp/trac-skel.sql
• Remove all INSERT statements except

  INSERT INTO "system" VALUES('database_version', '19');

• Copy in all the lines from trac-dump.sql in between the CREATE TABLE statements and the CREATE INDEX statements

Create the new database

• In your trac directory (eg. /var/trac/example/), run the following commands
• sqlite3 ./db/trac.db
• .read /tmp/trac-skel.sql
• .exit

Clean up!

Remember to delete your files in /tmp/ and your database dumps.

That’s all she wrote. This worked flawlessly for me, but I make no guarantee this will work for you. That said, if this is helpful, I’d love to hear about it.