Re: [rt-users] migrating from MySQL to Postgres? - SUCCESS!
Johan, Thanks for your work and the detailed instructions. I tried migrating earlier, but I had trouble with non-ASCII characters (all rows with non-ASCII characters were skipped), so I put this on ice for the time being. Will this work now, after your change #2 mentioned below? I can't say with certainty, but I think it would. Every row is (re)encoded into UTF-8 before being pushed into the new database, if I understand the advice I was given correctly. Cheers, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature 2011 Training: http://bestpractical.com/services/training.html
Re: [rt-users] migrating from MySQL to Postgres?
Ken, I think you need to make certain you set the start points of the serial columns/sequences for the PostgreSQL version to after the last value for each PK. Then you will get a new/unique value for each insert thereafter. D'oh, yeah. Missed the bits at the top of the old wiki page talking about that. Thanks. -Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature 2011 Training: http://bestpractical.com/services/training.html
Re: [rt-users] migrating from MySQL to Postgres? - SUCCESS!
Alright, I'm up and running on Postgres now instead. Here's a rough
writeup of what I did - most of it was to adapt/follow the instructions
put forth by Edward Groenendaal at the bottom of the old wiki page
Ruslan posted the link to:
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 Landazurida...@astro.as.utexas.edu
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, da...@astro.as.utexas.edu
#
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
Re: [rt-users] migrating from MySQL to Postgres?
Greetings, Some progress has been made on this front. I have made some slight edits to Edward Groenendaal's script found here: http://wiki-archive.bestpractical.com/view/MySQLToPg that Ruslan pointed out. I am now able to migrate data from my mysql database to a postgres one. I'll post that script tomorrow. RT comes up ok with the postgres database, but I am unable to create new tickets - the following error comes up in /var/log/httpd/error_log: [Tue Jun 28 17:31:56 2011] [error] [client 128.83.129.176] FastCGI: server /opt/rt/rt4/sbin/rt-server.fcgi stderr: [Tue Jun 28 22:31:56 2011] [crit]: Couldn't create a ticket: Internal Error: Couldn't execute the query 'INSERT INTO Tickets (Subject, Status, Queue, Creator, Owner, LastUpdatedBy, Started, Type, Resolved, Starts, Created, Priority, LastUpdated, Due) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'ERROR: duplicate key value violates unique constraint tickets_pkey, referer: https://rt instance/Ticket/Create.html?Queue=3 Any ideas? I have moved back to the mysql-backed instance for the time being. Thanks, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature 2011 Training: http://bestpractical.com/services/training.html
Re: [rt-users] migrating from MySQL to Postgres?
James, Dario, I apologize for being slightly off-topic, but your original request stated you wanted to migrate from mysql to postgres to better utilize full text search. I'm sure this has been discussed in other threads, but is their an inherent limitation in mysql that prevents this (ie, you need a lot more memory, faster disk access, etc, using mysql vs. postgres) function from working efficiently (or at all)? No apologies necessary. I made this decision based on Best Practical's assertions in full_text_indexing.pod (in the install tarball) that full text indexing is not supported natively in MySQL, that I'd have to install an external package to get it, and that I'd likely have to recompile MySQL from source to properly integrate MySQL with the external indexing engine. Whereas PostgreSQL supports full text indexing natively, and I can get (maintained) packages for it straight from postgres.org. (Side note: I've changed from digest subscription to full for the time being - no need to cc me on messages anymore) Cheers, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Analyst (512) 471-3334 McDonald Observatory 2011 Training: http://bestpractical.com/services/training.html
Re: [rt-users] migrating from MySQL to Postgres?
Ruslan, Here is the page from the archive: http://wiki-archive.bestpractical.com/view/MySQLToPg Thanks. Trying to work with the last iteration of the script on there. Having some problems with encoding of attachments. Also tried doing a data-only migration using EDB's wizard after manually importing the correct schema from rt_home/etc/schema.Pg, but ran into a problem doesn't seem to respect the schema file's specifying some capital letters and creates everything in lowercase. i.e: rt4=# \d List of relations Schema | Name | Type | Owner +--+--+-- public | acl | table| postgres public | acl_id_seq | sequence | postgres public | articles | table| postgres public | articles_id_seq | sequence | postgres public | attachments | table| postgres public | attachments_id_seq | sequence | postgres public | attributes | table| postgres This screws up the wizard. Thanks, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature 2011 Training: http://bestpractical.com/services/training.html
[rt-users] migrating from MySQL to Postgres?
Greetings, I'm beginning to investigate migrating our RT backing database from MySQL to Postgres to more easily take advantage of full text search, which a couple of my coworkers really want. There apparently used to be a page on the wiki for doing this which has since been deleted for being out-of-date. My google-fu looks to be a little weak this morning and hasn't turned up much - I have not tried to search the list archives directly yet. I was wondering if anyone has done this and has notes or advice to offer. I'm looking at installing pg 8.4 packaged by Red Hat (the system is running RHEL5) and looking at using one of the tools (probably the one from PostgreSQL themselves) here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL Advice? Cheers, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature 2011 Training: http://bestpractical.com/services/training.html
Re: [rt-users] migrating from MySQL to Postgres?
Argh - my apologies, I forgot to say that I'm running RT 4.0.1. -Dario I'm beginning to investigate migrating our RT backing database from MySQL to Postgres to more easily take advantage of full text search, which a couple of my coworkers really want. There apparently used to be a page on the wiki for doing this which has since been deleted for being out-of-date. My google-fu looks to be a little weak this morning and hasn't turned up much - I have not tried to search the list archives directly yet. I was wondering if anyone has done this and has notes or advice to offer. I'm looking at installing pg 8.4 packaged by Red Hat (the system is running RHEL5) and looking at using one of the tools (probably the one from PostgreSQL themselves) here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL Advice? Cheers, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature 2011 Training: http://bestpractical.com/services/training.html
Re: [rt-users] migrating from MySQL to Postgres?
RT stores Attachment data differently on MySQL and Pg. MySQL has binary-safe blobs and Pg doesn't, so we use base64 and quoted- printable encodings (depending on content) on Pg. You'll likely need to do this conversion yourself and update the relevant metadata columns. This is the most prominent pain point, but there may be others like it too. If you do this successfully, it'd be awesome if you wrote down your steps, problem areas, etc and put it up on the wiki. I suspect many folks are going to start migrating to Pg due to the easier FTS setup. Just fyi, I've been playing around with the Migration Wizard EnterpriseDB has written. I'm not done yet, there's still two small problems, but here's what I've found so far: 1) The Wizard fails to migrate tables ScripActions and ScripConditions with the following error: Error Creating Table ScripConditions:ERROR: type varbinary does not exist 2) I had one ticket (not sure how) with a Due of -00-00 00:00:00, which made the Wizard fail to migrate the Tickets table. When I manually put a date in the Due field, it migrated fine. 3) I hit a java heap out of memory error trying to migrate the Transactions table. I manually set the max heap size to 512M in the script that called the Migration Wizard and it went through. I'm not 100% sure how to fix the problem in point 1 - any advice would be helpful. Additionally, since I'm on the digest instead of full rt-users list, please cc me if you reply to this. Thanks, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature 2011 Training: http://bestpractical.com/services/training.html
[rt-users] HTTP/HTTPS bug in 4.0?
Greetings, We are running an RT instance under https only. I just noticed a small issue - when you're looking at a ticket, the links for a requestor's other tickets are non-https (http://...). On our system, that leads to a 404. Other links in the ticket (creating links, last ticket transaction, etc) all properly go to https://... Cheers, Dario -- Dario Landazurida...@astro.as.utexas.edu Systems Administrator (512) 471-3334 McDonald Observatory smime.p7s Description: S/MIME Cryptographic Signature
