Changes between Version 2 and Version 3 of SqliteToPgScript

Jun 28, 2007, 10:21:29 PM (8 years ago)

Tips on how to complete your sqlite -> postgres migration


  • SqliteToPgScript

    v2 v3  
    12= SQLite to PostgreSQL Script =
    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)]]
     59== Additional ==
     61We moved a number of sqlite dbs under trac 0.9 to postgres and came up against the following issues:
     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:
     65CREATE TABLE tags (tagspace text(2147483647), name text(2147483647), tag text(2147483647) );
     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:
     71update enum set value = regexp_replace(value,'\\..*$','','g');
     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:
     75update report set query = regexp_replace(query,'"','\'','g');
    5878== Author/Contributors ==
    6080'''Author:''' [wiki:pacopablo] [[BR]]
    61 '''Contributors:'''
     81'''Contributors: mellis'''