Difference between revisions of "MigrateMysql2PostgresqlV4"

From Request Tracker Wiki
Jump to navigation Jump to search
(Focus on RT and Postgresql aspects, and removed other steps. Added the patch for ObjectScrips export/import from ticket 29949)
 
m
 
(6 intermediate revisions by 4 users not shown)
Line 20: Line 20:
  postgres=# CREATE USER rt_user WITH PASSWORD 'DBPASS' CREATEDB NOCREATEUSER;
  postgres=# CREATE USER rt_user WITH PASSWORD 'DBPASS' CREATEDB NOCREATEUSER;
  postgres=# \q
  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 RT (from source in my case) ===
Install basic dependencies (compiling environment, perl libraries, apache, porstgresql server, ...).
Install basic dependencies (compiling environment, perl libraries, apache, porstgresql server, ...).
Line 45: Line 55:


=== Export your source RT database ===
=== 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&results=64e68aace8abdfa0fcd99f3490b4d094).
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:
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 71:
     # Attributes
     # Attributes
     $self->PushCollections(qw(Attributes));
     $self->PushCollections(qw(Attributes));
</pre>


Users should not update tickets in RT during the migration.


Then, an your source server, export your database using rt-serializer:
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):
 
<pre>service exim4 stop</pre>
 
This will cause incoming messages to be queued on remote servers.
 
Then, on your source server, export your database using rt-serializer:
  sbin/rt-serializer --directory /path/to/export/rt-serializer-data --clone  
  sbin/rt-serializer --directory /path/to/export/rt-serializer-data --clone  


Line 71: Line 91:
  sbin/rt-setup-database --action create,schema,acl --dba rt_user --dba-password  DBPASS
  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&results=64e68aace8abdfa0fcd99f3490b4d094).
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:
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 106:
         RT::GroupMember
         RT::GroupMember
         RT::CustomField
         RT::CustomField
 
</pre>


Import the Database:
Import the Database:
  sbin/rt-importer /path/to/exported/rt-serializer-data
  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. 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 158:
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 142: Line 167:


Don't forget any customization you may have done such as local html pages.
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)
<pre>
# tar zcvf rt_attachments.tar.gz /opt/rt5/var/attachments
# scp rt_attachments.tar.gz root@your-new-server:/opt/rt5/var/attachments
# ...
</pre>
=== 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)
<pre>
# apt-get remove libdbd-pg-perl
# apt-get install cpanminus build-essential libpq-dev
# cpanm DBD::Pg
</pre>
(DBD::Pg 3.14.2 is currently in Debian testing, so hopefully this issue will be resolved in the next Debian release.)


=== FullText indexing ===
=== FullText indexing ===
Line 148: Line 204:
   
   
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 211:
     Table      => 'Attachments',
     Table      => 'Attachments',
  );
  );
</pre>
   
   
Now run a first indexing
Now run a first indexing

Latest revision as of 07:43, 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

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, on 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.