[rt-users] migrating from MySQL to Postgres? - SUCCESS!

[Johan Sjöberg]

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

> -----Original Message-----
> From: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-
> bounces at lists.bestpractical.com] On Behalf Of Dario Landazuri
> Sent: den 29 juni 2011 22:58
> To: rt-users at lists.bestpractical.com
> Subject: 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 Landazuri                    dario at astro.as.utexas.edu
> Systems Administrator                         (512) 471-3334
> McDonald Observatory
> **********************************************************
> **