MigrateMysql2PostgresqlV4 - Revision history

Robl: chown -> chmod

2023-01-04T17:41:03Z

chown -> chmod

← Older revision		Revision as of 13:41, 4 January 2023
Line 154:		Line 154:

	Make it executable:		Make it executable:
	~~chown~~ 777 /path/to/Update-Pg-Sequences.sh		chmod 777 /path/to/Update-Pg-Sequences.sh

	Then run it:		Then run it:

Robl at 11:43, 6 January 2021

2021-01-06T11:43:15Z

← Older revision		Revision as of 07:43, 6 January 2021
Line 82:		Line 82:
	This will cause incoming messages to be queued on remote servers.		This will cause incoming messages to be queued on remote servers.

	Then, an your source server, export your database using rt-serializer:		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

Robl: add notes external storage / debian perl libs

2021-01-06T11:32:39Z

add notes external storage / debian perl libs

@@ Line 20: / Line 20: @@
   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, ...).
@@ Line 62: / Line 72: @@
       $self->PushCollections(qw(Attributes));
   </pre>
 Then, an your source server, export your database using rt-serializer:
@@ Line 148: / Line 167: @@
 Don't forget any customization you may have done such as local html pages.
 === FullText indexing ===

Robl: nowiki seems to not work properly. let's try pre. Fix migration script example.

2021-01-06T10:45:10Z

nowiki seems to not work properly. let's try pre. Fix migration script example.

← Older revision		Revision as of 06:45, 6 January 2021
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

Admin: 6 revisions imported

2016-04-06T20:14:35Z

6 revisions imported

← Older revision		Revision as of 16:14, 6 April 2016
(No difference)

203.28.240.65: /* What is this page ? */

2015-11-24T03:37:09Z

What is this page ?

← Older revision		Revision as of 23:37, 23 November 2015
Line 3:		Line 3:

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



	~~test~~

	== Prepare the new server ==		== Prepare the new server ==

203.28.240.65: /* What is this page ? */

2015-11-24T03:36:47Z

What is this page ?

← Older revision		Revision as of 23:36, 23 November 2015
Line 3:		Line 3:

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



			test

	== Prepare the new server ==		== Prepare the new server ==

160.228.28.21: small fixes and added reference to ticket 29158.

2014-06-02T16:09:26Z

small fixes and added reference to ticket 29158.

← Older revision		Revision as of 12:09, 2 June 2014
Line 45:		Line 45:

	=== 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:
Line 71:		Line 71:
	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:
Line 90:		Line 90:
	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		#!/bin/bash
	<nowiki>#</nowiki> Request-Tracker mygration to Postgresql Companion script		<nowiki>#</nowiki> Request-Tracker mygration to Postgresql Companion script
Line 97:		Line 97:
	<nowiki>#</nowiki> last_value of sequences.		<nowiki>#</nowiki> last_value of sequences.
	<nowiki>#		<nowiki>#
	#</nowiki> This script is a quick and dirty fix that updates the Sequences		#</nowiki> This script is a quick and dirty fix that updates the Sequences
	<nowiki>#</nowiki> last value from the MAX_id used in each table		<nowiki>#</nowiki> last value from the MAX_id used in each table
	<nowiki>#		<nowiki>#
	#</nowiki> This script must be run as user postgres:		#</nowiki> This script must be run as user postgres:
	<nowiki>#</nowiki> sudo -u postgres /path/to/Update-Pg-Sequences.sh		<nowiki>#</nowiki> sudo -u postgres /path/to/Update-Pg-Sequences.sh
	<nowiki>#set -x		<nowiki>#set -x
	DBNAME="rt4"		DBNAME="rt4"
	cd /tmp		cd /tmp
	#</nowiki> First list all sequences from DB		#</nowiki> First list all sequences from DB
	LIST="$(psql -t -d $DBNAME -c 'select sequence_name from information_schema.sequences;')"		LIST="$(psql -t -d $DBNAME -c 'select sequence_name from information_schema.sequences;')"
	declare -A before_seq		declare -A before_seq

160.228.28.21: Focus on RT and Postgresql aspects, and removed other steps. Added the patch for ObjectScrips export/import from ticket 29949

2014-06-02T16:01:27Z

Focus on RT and Postgresql aspects, and removed other steps. Added the patch for ObjectScrips export/import from ticket 29949

New page

== 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

<u>'''CAUTION: do NOT initialize the database as the script may ask (do NOT run "make initdb")'''</u>

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&results=64e68aace8abdfa0fcd99f3490b4d094).

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&results=64e68aace8abdfa0fcd99f3490b4d094).

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