Re: [rt-users] Migrating from RT MySQL to PostgreSQL
On Sun, Dec 06, 2009 at 05:43:05PM +1000, David Hobley wrote: We have been trying to do this using this page, but we have never managed to get the binary attachments across successfully. If you have any luck with this, we are definitely interested in hearing about it. attached the version I used to migrate our DB without any loss ... test it ;) ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Migrating from RT MySQL to PostgreSQL
On Sun, Dec 06, 2009 at 05:21:52PM +0100, Emmanuel Lacour wrote:
On Sun, Dec 06, 2009 at 05:43:05PM +1000, David Hobley wrote:
We have been trying to do this using this page, but we have never
managed to get the binary attachments across successfully. If you have
any luck with this, we are definitely interested in hearing about it.
attached the version I used to migrate our DB without any loss ... test
it ;)
attached to _this_ message ...
#!/usr/bin/perl -w
# Warning, quickly modified version to suit my needs
# Emmanuel Lacour elac...@easter-eggs.com
# #
# mysql2pg.pl - Helps migration of RT database from MySQL to PostgreSQL #
# Copyright (C) 2007 - Gilmar Santos Jr #
# #
# This program is free software; you can redistribute it and/or modify#
# it under the terms of the GNU General Public License as published by#
# the Free Software Foundation; either version 2 of the License. #
# #
# This program is distributed in the hope that it will be useful, #
# but WITHOUT ANY WARRANTY; without even the implied warranty of #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
# GNU General Public License for more details.#
# #
# You should have received a copy of the GNU General Public License #
# along with this program; if not, write to the Free Software #
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, #
# MA 02110-1301, USA #
# #
# Gilmar Santos Jr - gilmarsanto...@safernet.org.br #
# #
use strict;
use DBI;
use Unicode::MapUTF8 qw(to_utf8);
use MIME::QuotedPrint;
use Encode;
use utf8;
Encode::Guess-set_suspects( qw(iso8859-1) );
# Adjust usernames and passwords!!! #
my $mysql = DBI-connect(DBI:mysql:database=rt, rt, )
or die $DBI::errstr;
$mysql-do('SET NAMES utf8;');
my $pg= DBI-connect(DBI:Pg:dbname=rt;host=localhost,
rt, , {AutoCommit = 0})
or die $DBI::errstr;
my @tables = (
'FM_Classes',
'FM_Articles',
'FM_Topics',
'FM_ObjectTopics',
'Attachments',
'CustomFieldValues',
'ObjectCustomFieldValues',
'ObjectCustomFields',
'CustomFields',
'CachedGroupMembers',
'GroupMembers',
'Attributes',
'ACL',
'Links',
'Principals',
'Tickets',
'Transactions',
'Queues',
'ScripConditions',
'Groups',
'Scrips',
'Users',
'ScripActions',
'Templates',
);
my $bad_data;
$| = 1;
foreach my $table (@tables) {
print \rConverting $table... ;
my $nr_regs = $mysql-selectall_arrayref(
qq|SELECT count(*) FROM $table;|)-[0][0];
print ($nr_regs lines)\n;
my $data= $mysql-selectall_arrayref(
qq|SELECT * FROM $table LIMIT 1 OFFSET 0|,
{ Slice = {} } ) or die $mysql-errstr;
$pg-do(qq|DELETE FROM \L$table\E|) or die $pg-errstr;
my @keys = keys %{$data-[0]};
my $pgh = $pg-prepare(
INSERT INTO \L$table\E( .
(join(, , @keys)) .
) VALUES (.
(join(, , map { ? } @keys)) .
)
) or die $pg-errstr;
foreach my $reg (0 .. ($nr_regs - 1 ? : $nr_regs - 1) ) {
if ( $table eq Attachments ) {
if ( ( $data-[$reg]{Content} ) ( $data-[$reg]{Content} =~ /\x00/ ) ) {
$data-[$reg]{ContentEncoding} = base64;
Encode::_utf8_off($data-[$reg]{Content});
$data-[$reg]{Content} = MIME::Base64::encode_base64($data-[$reg]{Content});
} elsif ( ( $data-[$reg]{ContentType} ) ( $data-[$reg]{ContentType} !~ /text\/plain/gi ) ( $data-[$reg]{Content} ) ( !Encode::is_utf8( $data-[$reg]{Content}, 1 ) ) ) {
$data-[$reg]{ContentEncoding} = quoted-printable;
Encode::_utf8_off($data-[$reg]{Content});
$data-[$reg]{'Content'} = MIME::QuotedPrint::encode($data-[$reg]{'Content'});
} elsif ( ( $data-[$reg]{ContentType} ) ( $data-[$reg]{ContentType} eq 'text/plain' ) ( $data-[$reg]{Content} ) ) {
$bad_data = $data-[$reg]{'Content'};
if ( ! eval { decode( UTF-8, $bad_data, Encode::FB_CROAK); } ) {
warn Bad UTF-8: .$data-[$reg]{'id'}.\n;
Re: [rt-users] Migrating from RT MySQL to PostgreSQL
- Original Message - From: Emmanuel Lacour elac...@easter-eggs.com To: rt-users@lists.bestpractical.com Sent: Friday, 27 November, 2009 3:22:53 AM Subject: Re: [rt-users] Migrating from RT MySQL to PostgreSQL On Thu, Nov 26, 2009 at 05:07:20PM +0100, Nehmer Torben wrote: Hello together, has anybody ever migrated RT from MySQL to PostgreSQL? Are there any scripts available to accomplish this? you can start here: http://wiki.bestpractical.com/view/MySQLToPg :) I did it with a slightly modified script to handle some encoding problems we had with our MySQL DB. - Original Message End - We have been trying to do this using this page, but we have never managed to get the binary attachments across successfully. If you have any luck with this, we are definitely interested in hearing about it. Cheers, David ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Migrating from RT MySQL to PostgreSQL
Hello together, has anybody ever migrated RT from MySQL to PostgreSQL? Are there any scripts available to accomplish this? Greetings, Torben Nehmer --- Torben Nehmer Diplom Informatiker (FH) Business System Developer CANCOM Deutschland GmbH Messerschmittstr. 20 89343 Scheppach Germany Tel.: +49 8225 - 996-1118 Fax: +49 8225 - 996-41118 torben.neh...@cancom.demailto:torben.neh...@cancom.de www.cancom.dehttp://www.cancom.de CANCOM Deutschland GmbH Sitz der Gesellschaft: Jettingen-Scheppach HRB 10653 Memmingen Geschäftsführer: Paul Holdschik, Christian Linder Diese E-Mail und alle mitgesendeten Dateien sind vertraulich und ausschließlich für den Gebrauch durch den Empfänger bestimmt! This e-mail and any files transmitted with it are confidential intended solely for the use of the addressee! ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Migrating from RT MySQL to PostgreSQL
On Thu, Nov 26, 2009 at 05:07:20PM +0100, Nehmer Torben wrote: Hello together, has anybody ever migrated RT from MySQL to PostgreSQL? Are there any scripts available to accomplish this? you can start here: http://wiki.bestpractical.com/view/MySQLToPg :) I did it with a slighty modified script to handle some encoding problems we had with our MySQL DB. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Migrating from RT MySQL to PostgreSQL
http://wiki.bestpractical.com/view/MySQLToPg 2009/11/26 Nehmer Torben torben.neh...@cancom.de: Hello together, has anybody ever migrated RT from MySQL to PostgreSQL? Are there any scripts available to accomplish this? Greetings, Torben Nehmer --- Torben Nehmer Diplom Informatiker (FH) Business System Developer CANCOM Deutschland GmbH Messerschmittstr. 20 89343 Scheppach Germany Tel.: +49 8225 - 996-1118 Fax: +49 8225 - 996-41118 torben.neh...@cancom.de www.cancom.de CANCOM Deutschland GmbH Sitz der Gesellschaft: Jettingen-Scheppach HRB 10653 Memmingen Geschäftsführer: Paul Holdschik, Christian Linder Diese E-Mail und alle mitgesendeten Dateien sind vertraulich und ausschließlich für den Gebrauch durch den Empfänger bestimmt! This e-mail and any files transmitted with it are confidential intended solely for the use of the addressee! ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com -- Best regards, Ruslan. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
