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-boun...@lists.bestpractical.com [mailto:rt-users- > boun...@lists.bestpractical.com] On Behalf Of Dario Landazuri > Sent: den 29 juni 2011 22:58 > To: rt-users@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 da...@astro.as.utexas.edu > Systems Administrator (512) 471-3334 > McDonald Observatory > ********************************************************** > ** -------- 2011 Training: http://bestpractical.com/services/training.html