Migrating from Sqlite to Postgresql

This is a brief guide on how to migrate an  Sqlite trac install to a  PostgreSQL install. Of course you will need set up Postgresql on your machine. You should refer to the  Postgresql documentation as a starting point for this.

There is already a  brief mention of migrating from Sqlite to Postgresql on the Trac website. However this guide differs slightly extended approach from that one, based on real experiences of what seems to work best with Agile-Trac. This guide assumes you have postgresql setup on your machine with a postgresql user created to administer the trac database. For example you might have this user as trac_db_admin with a password of admin.

Also note that we provide a modified migration script that correctly migrates the Agile-Trac tables. This is a slightly changed version of  the SqliteToPgScript on trac-hacks.org. The modified version can be found here,  sqlite2pg. In theory it should be possible to follow the example given on the  trac-hacks site and that may work for you, namely:

Original sqlite2pg Example

usage: sqlite2pg [options] [site]

sqlite2pg is used to migrate data from SQLite to PostgreSQL
options:
  --version             show program's version number and exit
  -h, --help            show this help message and exit
  -t <path>, --tracbase=<path>
                        Parent path for trac sites
  -e <path>, --tracenv=<path>
                        Path to trac environment
  -m <list>, --migrate=<list>
                        Comma separated list of tables to migrate
  --exclude_perms=<list>
                        Comma separated list of users to exclude from
                        permission migration
  -w <list;, --wikipages=<list>
                        Comma separated list of wiki page names to ommit from
                        the migration
  -p <uri>, --pg_uri=<uri>
                        DB URI for PostgreSQL database
  -s <uri>, --sqlite_uri=<uri>
                        DB URI for SQLite database
  --noclean             Do not clear PostgreSQL tables before transfer

./sqlite2pg -e /var/trac -p 'postgres://user:pass@localhost/db?schema=myschema'

Longer but more reliable approach

However the approach shown aboe tries to create the database and migrate all data in a single step. In practice this does not always work - at least for the migrations we performed we failed to finsh the migration completely. These might have been caused by other installed plugins, but it was not investigated further. Instead we adopted a possibly cleaner approach to migrating databases that allowed us to verify everything worked before touching the active trac site. This approach has is broken down into small steps to make it easy to follow. Only at the end, when the verification has been successful will we actually need to touch the original trac install we are migrating, and that is a reversible change so the approach carries little risk.

step 1: install and setup Postgresql

Install and setup Postgresql so that it is suitable for use with trac. In addition to the  Postgresql manual you'll also want to refer to these sources of information:

•  TracInstall
•  Postgresql as Database Backend for trac
•  Postgresql Recipe for trac

You'll likely have the following information to hand after completing this exercise.

• Postgresql user for administering the database, for example trac_db_admin
• A password for the Postgresql user, for example admin_password

step 2: Create a dummy trac environment that connects to a Postgresql schema

Create a dummy trac environment which will use the postgresql schema that you wish to migrate to. In other words, run trac-admin and do initenv as usual for some dummy environment. Make sure that you provide it with the correct database connection string for the psotgresql schema that you want to use for your migrated data.

For example first call,

trac-admin /vault/migrate/trac-dummy initenv

then when asked for your database connection string you might enter something like this;

postgres://trac_db_admin:admin_password@localhost:5432/trac_db?schema=trac1

where trac-db-admin is the username, admin_password the password, trac-db is the database and trac1 the schema. Typically the schema name will match the name of your trac instance. In this case we are migrating a trac instance called trac1 so we will use that as our schema name.

step 3: Enable agile-trac for the dummy environment

Now that you have an empty environment you should edit the conf/trac.ini file of the new environment so that the agile-trac plugin is enabled in the new dummy environment. You should add the following,

[components]
agiletrac.api.agiletracsystem = enabled
agiletrac.env.agiletracsetup = enabled
agiletrac.workflow.agileticketworkflow = enabled

step 4: Upgrade your dummy environmemt to update the database

Now upgrade your environment. --no-backup is required for a Postgresql database.

trac-admin /vault/migrate/trac upgrade --no-backup

This will cause agile-trac to update the schema in your new database.

step 5 optional: Enable the remaining plugins

Now copy the rest of your [components] entries from your original conf/trac.ini over to the dummy environment and upgrade again so that any database upgrades are carried out.

trac-admin /vault/migrate/trac upgrade --no-backup

step 6: Migrate your Sqlite data to the new Postgresql database

At this point we have a dummy environment that points to a currently empty postgresql schema where we are going to migrate our data to. nNow it is time to run the migration script. For example we might write,

./sqlite2pg -e /vault/trac/trac1 -p 'postgres://trac_db_admin:admin_password@localhost:5432/trac_db?schema=trac1'

The script will write out a line for each table and sequence it migrates.

step 7 optional: Verify the migration was successful

When the migration is done (it shouldn't take too long) you can now fire up  `tracd` with your dummy environment to make sure all appears as it should. You can also browse the schema using the excellent  pgAdmin III application.

step 8: Update the database connection string on your existing environment

Assuming you are happy the migration has gone according to plan you are now in a position to point your real trac environnment at the new database. Simply go to your conf/trac.ini file on the real trac environment (trac1) and update the database connection string to match the one in the dummy environment, for example,

[trac]
#database = sqlite:db/trac.db
database = postgres://trac_db_admin:admin_password@localhost:5432/trac_db?schema=trac1

step 9: Restart your Trac Server

Restart your server, be it tracd or  Apache.

step 10: Done

Enjoy the stability and performance of Postgresql!