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

[Dario Landazuri]

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
************************************************************
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: RTmysqltoPg
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110629/ba8d3f08/attachment.ksh>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5065 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110629/ba8d3f08/attachment.bin>