Migrating from MySQL to Postgres?

RT Users
1

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 Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

smime.p7s (6.4 KB)

2

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:
Converting from other Databases to PostgreSQL - PostgreSQL wiki

Advice?

Cheers,
Dario

Dario Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

smime.p7s (6.4 KB)

3

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.

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.

Thomas

4

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

  1. I had one ticket (not sure how) with a Due of 0000-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.

  2. 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 Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

smime.p7s (6.4 KB)

5

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

If you look at etc/schema.Pg, the two columns that are varbinary in
etc/schema.mysql are varchar(255).

You probably want to check the Pg schema the migration wizard created
against the one RT expects and fix, or at least take note of, any
differences.

Thomas

6

Hi,

Here is the page from the archive:
http://wiki-archive.bestpractical.com/view/MySQLToPg

Strange that we skipped it during wikia migration.On Fri, Jun 24, 2011 at 7:12 PM, Dario Landazuri dario@astro.as.utexas.edu wrote:

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:
Converting from other Databases to PostgreSQL - PostgreSQL wiki

Advice?

Cheers,
Dario

–

Dario Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

2011 Training: http://bestpractical.com/services/training.html

Best regards, Ruslan.

7

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 Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

smime.p7s (6.4 KB)

8

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:///Ticket/Create.html?Queue=3

Any ideas? I have moved back to the mysql-backed instance for the time
being.

Thanks,
Dario

Dario Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

smime.p7s (4.95 KB)

9

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)?

Thanks.On Tue, Jun 28, 2011 at 5:40 PM, Dario Landazuri dario@astro.as.utexas.eduwrote:

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.

James Moseley

10

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 Landazuri dario@astro.as.utexas.edu
Systems Analyst (512) 471-3334
McDonald Observatory

11

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:///Ticket/Create.html?Queue=3

Any ideas? I have moved back to the mysql-backed instance for the
time being.

Thanks,
Dario

–

Dario Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

Hi Dario,

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.

Cheers,
Ken

12

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 Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

smime.p7s (4.95 KB)

13

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

  1. “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.

  2. make initialize-database

  3. dropdb -U postgres rt4

  4. createdb -U postgres rt4

  5. psql -U postgres rt4 < /opt/rt/rt4-pg/etc/schema.Pg

  6. Turn off apache and sendmail to facilitate the migration.

  7. ./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.

  1. Move the rt4 link in /opt/rt to point to /opt/rt/rt4-pg.

  2. 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.

  3. Start apache and sendmail again, check that it works.

  4. 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. :wink:

Hope this is helpful to some.

Cheers,
Dario

Dario Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

RTmysqltoPg (4.98 KB)

smime.p7s (4.95 KB)

14

Thanks for the info, Dario.On Tue, Jun 28, 2011 at 6:38 PM, Dario Landazuri dario@astro.as.utexas.eduwrote:

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)?

James Moseley

15

Hi.

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?

/Johan

16

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 Landazuri dario@astro.as.utexas.edu
Systems Administrator (512) 471-3334
McDonald Observatory

smime.p7s (4.95 KB)