= SQLite to PostgreSQL Script =

{{{
#!html
<div class="system-message" float="left" overflow="auto">
<b>Notice:</b> This plugin is deprecated. See the <a href=http://trac-hacks.org/wiki/TracMigratePlugin>TracMigratePlugin</a>.
</div>
}}}

== Description ==

Script to migrate a SQLite trac database to PostgreSQL

== Bugs/Feature Requests == 

Existing bugs and feature requests for SqliteToPgScript are 
[report:9?COMPONENT=SqliteToPgScript here].

If you have any issues, create a 
[http://trac-hacks.org/newticket?component=SqliteToPgScript&owner=pacopablo new ticket].

== Download ==

Download the zipped source from [download:sqlitetopgscript here].

== Source ==

You can check out SqliteToPgScript from [http://trac-hacks.org/svn/sqlitetopgscript here] using Subversion, or [source:sqlitetopgscript 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 ==

[[ChangeLog(sqlitetopgscript, 3)]]

== 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, [wiki: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:''' [wiki:pacopablo] [[BR]]
'''Contributors:''' mellis