MigrateMysql2PostgresqlV4

From Request Tracker Wiki
Revision as of 06:32, 6 January 2021 by Robl (talk | contribs) (add notes external storage / debian perl libs)
Jump to navigation Jump to search

What is this page ?

As I'm currently migrating from RT v4.2.2 on Mysql to v4.2.2 on Postgresql and didn't find an easy tutorial about this, I've decided to write this quick page describingthe steps I've used.

I tested these instructions while moving from a Debian6-based RT server on Mysql to a Debian7-based RT server on Postgresql: this means I do have an old server (the "source server" and a new server the "destination server"). Of course you'll have to adapt these steps to your environment.

Prepare the new server

Prepare your Postgresql server

Edit /etc/postgresql/9.1/main/pg_hba.conf, and add the following lines before the not-commented ones 

host    template1       rt_user                 127.0.0.1 255.255.255.255       password
local   template1       rt_user                                                 password 
host    rt4             rt_user                 127.0.0.1 255.255.255.255       password
local   rt4             rt_user                                                 password

Restart Postgres: 

service postgresql restart

As user postgres, create the new role: 

su - postgres
$ psql
postgres=# CREATE USER rt_user WITH PASSWORD 'DBPASS' CREATEDB NOCREATEUSER;
postgres=# \q

For newer versions of postgres, may need something like: 

su - postgres
$ psql
postgres=# create ROLE rt_user SUPERUSER LOGIN PASSWORD 'DBPASS';
postgres=# createdb rt4
postgres-# grant all privileges on database rt4 to rt_user
postgres-# \q


Install RT (from source in my case)

Install basic dependencies (compiling environment, perl libraries, apache, porstgresql server, ...).

Configure you RT installation (in the source tree) and enable Postgresql, depending on your options, you can choose for instance: 

/path/to/extractedsources/configure --enable-graphviz --enable-gd --with-db-type=Pg --with-db-rt-pass="DBPASS" --with-web-user=www-data --with-web-group=www-data

Note that the default DB user is "rt_user" and the default dbname is "rt4". Of course you'll have to change DBPASS by your password.

Install missing dependencies: 

make fixdeps

Check with 

make testdeps

Install: 

make install

CAUTION: do NOT initialize the database as the script may ask (do NOT run "make initdb")

If you've setup the database with initdb, you won't be able to import your cloned database. In this case, and in this case only, you can run: 

/opt/rt4/sbin/rt-setup-database --action drop --dba rt_user --dba-password DBPASS

Install whatever extra RT extension you may need.

Export your source RT database

If your RT version is older than v4.2.5, You'll have first to apply a patch so that your export contains the Scrips/Queues bindings (see http://issues.bestpractical.com/Ticket/Display.html?id=29949).

The patch looks like the following for v4.2.2: 

 diff -u lib/RT/Migrate/Serializer.pm.orig lib/RT/Migrate/Serializer.pm
 --- lib/RT/Migrate/Serializer.pm.orig   2014-05-31 08:56:52.108512591 +0200
 +++ lib/RT/Migrate/Serializer.pm        2014-05-31 08:59:02.994003819 +0200
 @@ -172,7 +172,7 @@
     $self->PushCollections(qw(ACL));
 
     # Scrips
 -    $self->PushCollections(qw(Scrips ScripActions ScripConditions Templates));
 +    $self->PushCollections(qw(Scrips ObjectScrips ScripActions ScripConditions Templates));
 
     # Attributes
     $self->PushCollections(qw(Attributes));

Users should not update tickets in RT during the migration.

It is recommended to temporarily shut down the MTA to prevent new messages arriving during the migration, for example, if your RT server is running exim4 MTA (Debian default): 

service exim4 stop

This will cause incoming messages to be queued on remote servers.

Then, an your source server, export your database using rt-serializer: 

sbin/rt-serializer --directory /path/to/export/rt-serializer-data --clone

Then tar the resulting directory, copy it to the new server and untar the archive.

Import your RT database

Prepare your destination database: 

sbin/rt-setup-database --action create,schema,acl --dba rt_user --dba-password  DBPASS

If your RT version is older than v4.2.5, You'll have first to apply a patch so that rt-importer can import the Scrips/Queues bindings (see http://issues.bestpractical.com/Ticket/Display.html?id=29949).

The patch looks like the following for v4.2.2: 

 diff -u lib/RT.pm.orig lib/RT.pm
 --- lib/RT.pm.orig      2014-06-02 17:00:14.879786262 +0200
 +++ lib/RT.pm   2014-06-02 17:00:40.560077022 +0200
 @@ -524,6 +524,7 @@
         RT::ScripAction
         RT::ScripCondition
         RT::Scrip
 +       RT::ObjectScrip
         RT::Group
         RT::GroupMember
         RT::CustomField

Import the Database: 

sbin/rt-importer /path/to/exported/rt-serializer-data

We now need to fix the Postgresql sequence numbers as they are not updated by rt-importer, see http://issues.bestpractical.com/Ticket/Display.html?id=29158. In order to automatically fix your sequences, you can create the following script as "Update-Pg-Sequences.sh": 

#!/bin/bash
# Request-Tracker mygration to Postgresql Companion script
#-------------------------------------------------------
# When migrating to postgresql, rt-importer doesn't update the
# last_value of sequences.
#
# This script is a quick and dirty fix that updates the Sequences
# last value from the MAX_id used in each table
#
# This script must be run as user postgres:
# sudo -u postgres /path/to/Update-Pg-Sequences.sh
#set -x
  DBNAME="rt4"
  cd /tmp
  # First list all sequences from DB
LIST="$(psql -t -d $DBNAME -c 'select sequence_name from information_schema.sequences;')"
declare -A before_seq
declare -A after_seq
# For each sequence, read the last value, then update to the MAX id, and
# read again the last value
for i in $LIST
do
# Let's parse the table name and columnname that corresponds to the sequence
tablename=$(echo $i | cut -d"_" -f1)
columnname=$(echo $i | cut -d"_" -f2)
# Read the initial sequence last_value
 sql_lastval="select last_value from $i"
 before_seq["$i"]=$(psql -t -d $DBNAME -c "$sql_lastval" | tr -d ' ')
# Setting the sequence to the MAX of id(s)
 sql="select setval('$i',max($columnname)) from $tablename;"
 cmd=$(psql -t -d $DBNAME -c "$sql")
# Let's read again the sequence last_value
# Yes I know this last step is not required as we already have the value in cmd
# but I like to double check the results ;-)
after_seq["$i"]=$(psql -t -d $DBNAME -c "$sql_lastval" | tr -d ' ')
 echo "Sequence '$i', before=${before_seq[$i]}, after=${after_seq[$i]}"
done

Make it executable: 

chown 777 /path/to/Update-Pg-Sequences.sh

Then run it: 

sudo -u postgres /path/to/Update-Pg-Sequences.sh

Retrieve your old parameters

Now is time to complete your configuration file RT_SiteConfig.pm with your parameters from the source server.

Then setup your Webserver as required, and start it.

You can setup your mailgate as well as your crontab entries.

Don't forget any customization you may have done such as local html pages.

External Storage

If you use ExternalStorage /sbin/rt-externalize-attachments to store attachments to disk instead of the database, you need to copy these files to the new server. They may be stored in, for example /opt/rt5/var/attachments (or whatever path configured in RT_SiteConfig.pm ExternalStorage) 

 # tar zcvf rt_attachments.tar.gz /opt/rt5/var/attachments
 # scp rt_attachments.tar.gz root@your-new-server:/opt/rt5/var/attachments
 # ...

Perl Modules / Debian Buster

(2020 update): When migrating to postgresql 13, the stock perl DBD::Pg module shipped in Debian buster package didn't work properly. (For example deleting due dates to an empty value to unset it, crashed RT with an error.)

This is only an issue if you are using a newer version of postgresql instead of the shipped Debian package version. (postgresql-11)

To resolve this, I had to remove the libdbd-pg-perl package and just install the newest DBD::Pg from CPAN. (needs to be at least 3.14.x) 

 # apt-get remove libdbd-pg-perl
 # apt-get install cpanminus build-essential libpq-dev
 # cpanm DBD::Pg

(DBD::Pg 3.14.2 is currently in Debian testing, so hopefully this issue will be resolved in the next Debian release.)

FullText indexing

You may now want to setup FullText indexes: 

sbin/rt-setup-fulltext-index --dba rt_user --dba-password DBPASS

Then add the proposed setup lines to your /opt/rt4/etc/RT_SiteConfig.pm file, for instance: 

 Set( %FullTextSearch,
    Enable     => 1,
    Indexed    => 1,
    Column     => 'ContentIndex',
    Table      => 'Attachments',
 );

Now run a first indexing 

./sbin/rt-fulltext-indexer -–all

Then make it a cron job: 

ln -s /opt/rt4/sbin/rt-fulltext-indexer /etc/cron.hourly

Your RT server is now migrated to Postgresql.

Retrieved from "https://rt-wiki.bestpractical.com/index.php?title=MigrateMysql2PostgresqlV4&oldid=26991"