Changes between Version 2 and Version 3 of SqliteToPgScript


Ignore:
Timestamp:
Jun 28, 2007, 10:21:29 PM (7 years ago)
Author:
mellis
Comment:

Tips on how to complete your sqlite -> postgres migration

Legend:

Unmodified
Added
Removed
Modified
  • SqliteToPgScript

    v2 v3  
     1&
    12= SQLite to PostgreSQL Script =
    23
     
    3940                        Comma separated list of users to exclude from
    4041                        permission migration
    41   -w <list>, --wikipages=<list>
     42  -w <list;, --wikipages=<list>
    4243                        Comma separated list of wiki page names to ommit from
    4344                        the migration
     
    5657[[ChangeLog(sqlitetopgscript, 3)]]
    5758
     59== Additional ==
     60
     61We moved a number of sqlite dbs under trac 0.9 to postgres and came up against the following issues:
     62
     63 * 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:
     64{{{
     65CREATE TABLE tags (tagspace text(2147483647), name text(2147483647), tag text(2147483647) );
     66}}}
     67 * 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.
     68 * Having dealt with the tag plugin problems, the script ran nicely but we then hit two problems in use, both postgres related:
     69   * 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:
     70{{{
     71update enum set value = regexp_replace(value,'\\..*$','','g');
     72}}}
     73   * 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:
     74{{{
     75update report set query = regexp_replace(query,'"','\'','g');
     76}}}
     77
    5878== Author/Contributors ==
    5979
    6080'''Author:''' [wiki:pacopablo] [[BR]]
    61 '''Contributors:'''
     81'''Contributors: mellis'''