[rt-users] Tool to migrate from RT3 on postgres to RT3 on mysql and back...

[Alex]

There were questions on the list about doing it the other way around: 
from MySQL to Postgres.

I have managed to use this tool to migrate RT 3.0.10 the other way 
around -- from mysql to postgres.

The procedure was:

   1. *rt-setup-database --action schema ...
      *
   2. Download
      http://fsck.com/pub/rt/contrib/3.0/Conversion/rt3-on-pg-to-mysql
   3. Modify the script to reflect database locations, e.g.:
          * my $new_handle =
            DBI->connect("dbi:Pg:dbname=rtdb",'postgres','')
                    or die $DBI::errstr;
          * my $old_handle =
            DBI->connect("dbi:mysql:database=rt3;host=<my-old-host>;port=3306",'<my_user>
            ','<my password>')  or die $DBI::errstr;
   4. Switch to PostgreSQL DBA account: *su postgres*
   5. Run the script and make sure it worked OK. At this point you can
      login and see tickets, but not create tickets
   6. Reset the sequences in the database:
          * *su postgres
            psql rtdb*
          * For every SEQUENCE in /etc/request-tracker3/schema.Pg (I am
            using Debian; the location on your system may be in
            /opt/rt3. or somewhere else) do:
                o SELECT MAX(id) + 1 FROM <table>;
                o note the result
                o ALTER SEQUENCE <table>_is_seq RESTART WITH <the result
                  of the previous statement> ;

NOTES

   1. Usual disclaimer: The above worked for me once; it is not
      guaranteed to work for you and you are strongly advised not to do
      it on the production database without prior testing.
   2. IMHO ALTER SEQUENCE <table>_is_seq RESTART WITH (SELECT MAX(id) +
      1 FROM <table>)  should have worked but it didn't and I had to
      split it in two. Postgres/SQL gurus might be able to explain why.
      Perl gurus may want to modify Jesse's script to do all this
      automagically.
   3. In my totally unscientific Apples-to-Oranges comparison moving
      database to Postgres actually made things faster, contrary to what
      many people experience.


Jesse Vincent wrote on 04/11/2004 4:12 PM:

>Is now available at http://fsck.com/pub/rt/contrib/3.0/Conversion/
>
>It's a small tool I built for a client who wanted to move from Pg to
>Mysql with their existing RT3 database.
>
>You should start with a full RT database on postgres and an RT3 database
>on mysql that contains only the schema and not the database content.
>
>	-j
>
>
>
>  
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20040611/c2b0f6fa/attachment.htm>