Difference between revisions of "MigrateMysql2PostgresqlV4"

From Request Tracker Wiki
Jump to navigation Jump to search
m (6 revisions imported)
(nowiki seems to not work properly. let's try pre. Fix migration script example.)
Line 48: Line 48:


The patch looks like the following for v4.2.2:
The patch looks like the following for v4.2.2:
<pre>
  diff -u lib/RT/Migrate/Serializer.pm.orig lib/RT/Migrate/Serializer.pm
  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.orig  2014-05-31 08:56:52.108512591 +0200
Line 60: Line 61:
     # Attributes
     # Attributes
     $self->PushCollections(qw(Attributes));
     $self->PushCollections(qw(Attributes));
 
</pre>


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


The patch looks like the following for v4.2.2:
The patch looks like the following for v4.2.2:
<pre>
  diff -u lib/RT.pm.orig lib/RT.pm
  diff -u lib/RT.pm.orig lib/RT.pm
  --- lib/RT.pm.orig      2014-06-02 17:00:14.879786262 +0200
  --- lib/RT.pm.orig      2014-06-02 17:00:14.879786262 +0200
Line 85: Line 87:
         RT::GroupMember
         RT::GroupMember
         RT::CustomField
         RT::CustomField
 
</pre>


Import the Database:
Import the Database:
Line 91: Line 93:


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


Make it executable:
Make it executable:
Line 134: Line 139:
Then run it:
Then run it:
  sudo -u postgres /path/to/Update-Pg-Sequences.sh
  sudo -u postgres /path/to/Update-Pg-Sequences.sh
=== Retrieve your old parameters ===
=== Retrieve your old parameters ===
Now is time to complete your configuration file RT_SiteConfig.pm with your parameters from the source server.
Now is time to complete your configuration file RT_SiteConfig.pm with your parameters from the source server.
Line 148: Line 154:
   
   
Then add the proposed setup lines to your /opt/rt4/etc/RT_SiteConfig.pm file, for instance:
Then add the proposed setup lines to your /opt/rt4/etc/RT_SiteConfig.pm file, for instance:
<pre>
  Set( %FullTextSearch,
  Set( %FullTextSearch,
     Enable    => 1,
     Enable    => 1,
Line 154: Line 161:
     Table      => 'Attachments',
     Table      => 'Attachments',
  );
  );
</pre>
   
   
Now run a first indexing
Now run a first indexing

Revision as of 06:45, 6 January 2021

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

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

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.

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.