Migrating Trac from Sqlite to PostgreSQL

At Edoceo we use PostgreSQL almost exclusively, when we installed an old beta of Trac the only supported database was Sqlite. A little thing like that would not get in our way so we continued to use Trac successfully for a number of months. Just recently we decided to update our Trac install to use PostgreSQL. Here’s how it worked. Backup your data before starting! (Good advice which we don’t follow)

Trac-Hacks had a handy script to aid in our migration called SqliteToPg.

First fetch that script into your Trac directory (for simplicity) and make it executable.

cd /var/trac/
curl 'http://trac-hacks.org/browser/sqlitetopgscript/0.10/sqlite2pg?format=raw' > sqlite2pg.py
chmod 0755 sqlite2pg.py

Then create the PostgreSQL database (as you see fit, here’s a very simple method)

psql -U postgres
postgres=# create user trac_edoceo;
postgres=# create database trac_edoceo with owner trac_edoceo encoding 'UTF-8';

Then execute the script to import the data!

./sqlite2pg.py --tracenv=./ --pg_uri='postgres://trac_edoceo@localhost/trac_edoceo'

Now update the Trac system to use this new database! Open the trac.ini file and edit the line that reads ‘database = …’ to use the same URI as above. For us it looks like:

sed -i 's/sqlite:db/trac.db/postgres://trac_edoceo@localhost/trac_edoceo/' conf/trac.ini

Then we restarted Apache just to make sure all the configuration changes would take affect.