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

[Ruslan U. Zakirov]

Thanks, Alex.
Published: http://wiki.bestpractical.com/index.cgi?MySQLToPg

			Best regards. Ruslan.
Alex wrote:
> 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
>>
>>
>>
>>  
>>
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> RT Developer and Administrator training is coming to LA, DC and Frankfurt this spring and summer.
> http://bestpractical.com/services/training.html
> 
> Sign up early, as class space is limited.