SQLite to PostgreSQL Script

Description

Script to migrate a SQLite trac database to PostgreSQL

Bugs/Feature Requests

Existing bugs and feature requests for SqliteToPgScript are here.

If you have any issues, create a new ticket.

Download

Download the zipped source from here.

Source

You can check out SqliteToPgScript from here using Subversion, or browse the source with Trac.

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'

Recent Changes

[2377] by pacopablo on 07/03/07 23:28:21

SqliteToPgScript:

• Fixes #1709 (really)
• Yeah, good of me to actually test my changes. Fixed the typo caused by fixing the typo :)

[2376] by pacopablo on 07/03/07 23:26:55

SqliteToPgScript:

• Fixes #1709
• Thanks for catching my typos.

[1608] by pacopablo on 11/28/06 16:08:57

SqliteToPgScript:

• Refs #844 hopefull fix for missing "system" table. Turns out that the ISOLATION_LEVEL_AUTOCOMMIT line was the culprit. I think that I originally had that in there because when I first started the script I was taking a different approach for creating the environments. It's not needed now, and fixes the issue for me. Need testing before I can close the bug.

[1304] by pacopablo on 09/27/06 00:51:04

SqliteToPgScript:

• Fixed bug where the ticket_id_seq and report_id_seq weren't being set properly.

Additional

We moved a number of sqlite dbs under trac 0.9 to postgres and came up against the following issues:

• If you install the tag plug-in on your new installation, the migration script tries to look for that table in the sqlite database and fails. You can create the tag table in your sqlite db as follows:

  CREATE TABLE tags (tagspace text(2147483647), name text(2147483647), tag text(2147483647) );
  

• Another problem with the tag plug-in was that after your migration there are no tags in the database, so selecting the 'tag' button on the toolbar gave an error. This is easy to fix. Open any page and add a tag. Then everything works just fine.
• Having dealt with the tag plugin problems, the script ran nicely but we then hit two problems in use, both postgres related:
  • The value column in table enum had .0 on the end of every integer. This gave a postgres error complaining about an invalid integer. We cleared that out in postgres as follows:

    update enum set value = regexp_replace(value,'\\..*$','','g');
    

  • The query column in table report had double quotes in them, which work fine in sqlite but killed postgres. We sorted that out as follows:

    update report set query = regexp_replace(query,'"','\'','g');
    

NOTE: this script works with 0.11 ...at least it did for me. I'd still recommend being paranoid in your migration (e.g., making full backups, etc.) but mine cranked through like a champ. -- gt4329b

Author/Contributors

Author: pacopablo
Contributors: mellis