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 06/12/08 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 05/22/08 17:54:46
Changed author's email address as asylumware has, unfortunately, gone the way of the dodo
[2377] by pacopablo on 07/04/07 06:28:21
- 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/04/07 06:26:55
- Fixes #1709
- Thanks for catching my typos.
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');
- 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:
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
