wiki:SqliteToPgScript

Version 10 (modified by anonymous, 2 years ago) (diff)

--

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

[3826] by pacopablo on 2008-06-12 07:06:57
  • Accepted patch to create a main() function that is easily callable from other python modules. Thanks to alberto Closes #3148
  • Accepted patch to check for presence of arguments before checking value. Closes #3138
[3707] by pacopablo on 2008-05-22 17:54:46
Changed author's email address as asylumware has, unfortunately, gone the way of the dodo
[2377] by pacopablo on 2007-07-04 06:28:21

SqliteToPgScript:

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

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

NOTE: seemed to work with 0.12 ... for us.. arnstein87 AT gmail, ttressieres.

do not forget to enable postgresql connector in trac.ini

[components]
trac.db.postgres_backend.postgresqlconnector = enabled

NOTE: Some plugins use sequences in PostgreSQL. The script can move the data, however, it will not update the sequence. This will need to be done manually.

NOTE: It might go without saying, but it goes better with : you'll want to restart apache if you run trac in it after the migration (and the activation of the postgres db in trac.ini)

Author/Contributors

Author: pacopablo
Contributors: mellis