http://wiki-archive.bestpractical.com/view/MySQLToPg
One difference is that I was only using one machine. Also, I was already running 4.0.1, so upgrading the database weren't needed. This was done with Postgres 9.0 on a machine running RHEL5.
1) Install PostgreSQL 9.0 from the "official" yum repo found here:
http://yum.pgrpms.org/howtoyum.php
2) "Reinstalled" RT to use postgres. I simply used --prefix to put it
into a different directory. Specifically, I installed to
/opt/rt/rt4-pg. I also moved the current (MySQL-based) installation to
/opt/rt/rt-mysql and created a link (ln -s rt4-mysql rt4) to facilitate
later easy switching back to the mysql version if needed.
3) make initialize-database 4) dropdb -U postgres rt4 5) createdb -U postgres rt4 6) psql -U postgres rt4 < /opt/rt/rt4-pg/etc/schema.Pg 7) Turn off apache and sendmail to facilitate the migration. 8) ./RTmysqltoPg (an updated/edited version of the script Edward presented)This took maybe 10 or 15 minutes, since we don't have a large RT instance here - only ~14k tickets.
9) Move the rt4 link in /opt/rt to point to /opt/rt/rt4-pg.10) Copy over RT_SiteConfig.pm from /opt/rt/rt4-mysql. Add a line for the different dba user and change the line for the dba password.
11) Start apache and sendmail again, check that it works. 12) Profit!Once it looked like it was working correctly, I proceeded to enable full text indexing and begin the initial index as per the instructions in full_text_indexing.pod.
The script from step 8 is attached to this email. Here're the changes I made to the script:
1) Changed up the "my @tables" line (25) to take out the RTFM tables and make sure all necessary tables in my particular RT instance (except for sessions) were accounted for.
2) I had to edit the "push @values" line (124) to properly handle UTF-8 encoding - PG's much more strict than mysql is, apparently.
3) Added lines 52-63 to add the ability to update the sequences. Please pardon me if it's bad perl - I'm not a coder, just a sysadmin who can shell script on a good day. ;)
Hope this is helpful to some. Cheers, Dario -- ************************************************************ Dario Landazuri [email protected] Systems Administrator (512) 471-3334 McDonald Observatory ************************************************************
#!/usr/bin/perl -w
use strict;
use DBI;
use MIME::Base64 qw(encode_base64);
use Encode;
# Connect to mysql database
my $old_handle = DBI->connect("dbi:mysql:dbname=rt4",'rt_user','cdia3712');
# Connect to postgres database (on remote host)
my $new_handle = DBI->connect("dbi:Pg:dbname=rt4",'postgres','aidc2173');
# For each table in our list of tables
#
# Original list:
#
#my @tables = qw/ Attributes Users ACL Attachments CachedGroupMembers
CustomFieldValues CustomFields GroupMembers Groups Links Principals Queues
ScripActions ScripConditions Scrips Templates TicketCustomFieldValues Tickets
Transactions /;
#
# Updated list for "plain" RT 4.0.1 as of 28 June 2011
# Dario Landazuri, [email protected]
#
my @tables = qw/ ACL Articles Attachments Attributes CachedGroupMembers Classes
CustomFieldValues CustomFields GroupMembers Groups Links ObjectClasses
ObjectCustomFieldValues ObjectCustomFields ObjectTopics Principals Queues
ScripActions ScripConditions Scrips Templates Tickets Topics Transactions Users
/;
foreach my $table (@tables) {
print "Migrating $table...\n";
# Find all the rows in the table
my $mymaxidq = $old_handle->prepare("select max(id) as id from $table")
|| die $@;
$mymaxidq->execute || die $@;
my $mymaxidr = $mymaxidq->fetchrow_hashref;
my $mymaxid = $mymaxidr->{"id"};
print "maxid=$mymaxid\n";
my $reached_end = 0;
my $table_step = 2000;
my $current = 0;
while ($reached_end == 0)
{
my $sth = $old_handle->prepare("SELECT * FROM $table where
id>$current order by id limit $table_step") || die $@;
$sth->execute || die $@;
print "Asked $table for $table_step starting from $current, got
".$sth->rows."\n";
if ($sth->rows == 0 && $current >= $mymaxid)
{
print "Exiting $table at $current, table size was $mymaxid\n";
my $seq_name;
if (($table eq "ObjectCustomFields") or ($table eq
"ObjectCustomFieldValues"))
{
$seq_name = lc($table) . "_id_s";
}
else
{
$seq_name = lc($table) . "_id_seq";
}
my $new_seq_start = $mymaxid + 1;
my $sth = $new_handle->prepare("select setval('$seq_name',
$new_seq_start)");
$sth->execute || die $@;
$reached_end = 1;
} else {
# Copy all rows from postgres to mysql
$current = ©_table_rows($table, $sth);
}
}
}
sub copy_table_rows {
my $table = shift;
my $sth = shift;
my $lastrow = 0;
while (my $row = $sth->fetchrow_hashref) {
my @keys;
my @values;
my @placeholders;
$lastrow = $row->{"id"};
# print "Row $lastrow\n";
if ($table eq "Attachments" and (defined($row->{"ContentType"})
and $row->{"ContentType"} =~ /application|image/ )) {
$row->{"ContentEncoding"} = "base64";
$row->{"Content"} = encode_base64($row->{"Content"});
}
if ($table eq "ObjectCustomFieldValues")
{
if (defined($row->{"Content"}) and length $row->{"Content"}
>= 255) {
# Copy the contents to LargeContent and then truncate
Content
$row->{"LargeContent"} = $row->{"Content"};
$row->{"Content"} = substr $row->{"Content"}, 0, 255;
}
if (!$row->{"SortOrder"}) {
$row->{"SortOrder"} = 0;
}
if (!$row->{"Creator"}) {
$row->{"Creator"} = 0;
}
if (!$row->{"LastUpdatedBy"}) {
$row->{"LastUpdatedBy"} = 0;
}
if (!$row->{"Disabled"}) {
$row->{"Disabled"} = 0;
}
}
foreach my $key (keys %$row) {
if ($table eq 'Users' && $key =~ /^Name$/i) {
$row->{$key} =
check_for_duplicate_name($row->{$key});
}
if ($table eq 'Users' && $key =~ /EmailAddress/i) {
$row->{$key} =
check_for_duplicate_email($row->{$key});
}
push @keys, $key;
# Line below edited per advice from Frank Wiles
# ([email protected]) to cope with UTF-8 encoding
# issues - see http://www.perlmonks.org/?node_id=591180
push @values, encode("UTF-8", $row->{$key});
push @placeholders, '?';
}
my $insert = "INSERT into $table (".join(',',@keys).") VALUES
(".join(',',@placeholders).")\n";
my $sth= $new_handle->prepare($insert) || die $@;
$sth->execute(@values) || die $@ . "\n
".join(",",@values);
}
$lastrow;
}
sub check_for_duplicate_name {
my $address = shift;
my $lookup = $new_handle->quote($address);
my $result = $new_handle->selectrow_arrayref("SELECT id FROM Users
WHERE Name = $lookup");
if ($result->[0]) {
my $new_addr = "$address (Duplicate ".time().")";
print "Found duplicate name '$address' :". $result->[0].". replacing
with alternate address - ".$new_addr."\n";;
check_for_duplicate_email($new_addr);
}
else {
return($address);
}
}
sub check_for_duplicate_email {
my $address = shift;
my $lookup = $new_handle->quote($address);
my $result = $new_handle->selectrow_arrayref("SELECT id FROM Users
WHERE EmailAddress = $lookup");
if ($result->[0]) {
my $new_addr = "$address, nobody+".time();
print "Found duplicate address '$address':". $result->[0].". replacing
with alternate address - ".$new_addr."\n";;
check_for_duplicate_email($new_addr);
}
else {
return($address);
}
}
$old_handle->disconnect();
$new_handle->disconnect();
smime.p7s
Description: S/MIME Cryptographic Signature
-------- 2011 Training: http://bestpractical.com/services/training.html
