ManualDatabaseConversion - Revision history

Phanousk at 09:45, 29 October 2024

2024-10-29T09:45:44Z

← Older revision		Revision as of 05:45, 29 October 2024
Line 6:		Line 6:
	# On current (MySQL) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> -- to fix some garbage gathered through the years of upgrading your system. If you want to first check what it will do, just ommit the <code>--resolve</code> option.		# On current (MySQL) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> -- to fix some garbage gathered through the years of upgrading your system. If you want to first check what it will do, just ommit the <code>--resolve</code> option.
	# On current (MySQL) run: <code>/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500 </code>		# On current (MySQL) run: <code>/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500 </code>
	#* BEWARE: If you use [https://metacpan.org/pod/RT::Extension::MergeUsers RT::Extension::MergeUsers], you should disable it before serialization. Otherwise the import won't run correctly.		#* BEWARE: If you use [https://metacpan.org/pod/RT::Extension::MergeUsers RT::Extension::MergeUsers], you should disable it before serialization. Otherwise the import afterwards won't run correctly.
	# On new (Postgres) run: <code>sudo -u postgres psql -c "drop database rtdb;"</code>		# On new (Postgres) run: <code>sudo -u postgres psql -c "drop database rtdb;"</code>
	# On new (Postgres) optionally run: <code>sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"</code> -- you will need this password in next step		# On new (Postgres) optionally run: <code>sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"</code> -- you will need this password in next step

Phanousk: Add some new info about MergeUsers extension.

2024-10-29T09:45:04Z

Add some new info about MergeUsers extension.

← Older revision		Revision as of 05:45, 29 October 2024
Line 1:		Line 1:
	= Converting the database from MySQL (MariaDB) to PostgreSQL =		= Converting the database from MySQL (MariaDB) to PostgreSQL =
	This is a very straightforward article for set-up environment. For some deeper details refer [[MigrateMysql2PostgresqlV4\|older article]] about migrating.		This is a very straightforward article for set-up environment. For some deeper details refer [[MigrateMysql2PostgresqlV4\|older article]] about migrating. This should also work for serializing and importing the same database type, not just MariaDB to Postgres.

	This HowTo assume you have set-up some current production RT instance with MySQL or MariaDB database server and want to move it to PostgreSQL which should be more robust. So here we go:		This HowTo assume you have set-up some current production RT instance with MySQL or MariaDB database server and want to move it to PostgreSQL which should be more robust. So here we go:
	# Backup ~~youre~~ production database!		# Backup your production database!
	# On current (MySQL) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> -- to fix some garbage gathered through the years of upgrading your system. If you want to first check what it will do, just ommit the <code>--resolve</code> option.		# On current (MySQL) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> -- to fix some garbage gathered through the years of upgrading your system. If you want to first check what it will do, just ommit the <code>--resolve</code> option.
	# On current (MySQL) run: <code>/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500 </code>		# On current (MySQL) run: <code>/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500 </code>
			#* BEWARE: If you use [https://metacpan.org/pod/RT::Extension::MergeUsers RT::Extension::MergeUsers], you should disable it before serialization. Otherwise the import won't run correctly.
	# On new (Postgres) run: <code>sudo -u postgres psql -c "drop database rtdb;"</code>		# On new (Postgres) run: <code>sudo -u postgres psql -c "drop database rtdb;"</code>
	# On new (Postgres) optionally run: <code>sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"</code> -- you will need this password in next step		# On new (Postgres) optionally run: <code>sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"</code> -- you will need this password in next step
Line 12:		Line 13:
	# On new (Postgres) run: <code>/opt/rt4/sbin/rt-importer /var/tmp/serialized</code>		# On new (Postgres) run: <code>/opt/rt4/sbin/rt-importer /var/tmp/serialized</code>
	# On new (Postgres) run: <code>PERL5LIB="/opt/rt4/lib:/opt/rt4/local/lib" /opt/rt4/etc/upgrade/reset-sequences</code>		# On new (Postgres) run: <code>PERL5LIB="/opt/rt4/lib:/opt/rt4/local/lib" /opt/rt4/etc/upgrade/reset-sequences</code>
	# On new (Postgres) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> -- to create CachedMembers in database		#* Resetting sequences is necessary especially for Postgres + data serialized with --clone.
			# On new (Postgres) run: <code>/opt/rt4/sbin/rt-validator --check --resolve --force</code> -- to create CachedMembers in database

	===== Fix encoding patch =====		===== Fix encoding patch =====

Phanousk at 07:32, 23 July 2024

2024-07-23T07:32:32Z

← Older revision		Revision as of 03:32, 23 July 2024
Line 12:		Line 12:
	# On new (Postgres) run: <code>/opt/rt4/sbin/rt-importer /var/tmp/serialized</code>		# On new (Postgres) run: <code>/opt/rt4/sbin/rt-importer /var/tmp/serialized</code>
	# On new (Postgres) run: <code>PERL5LIB="/opt/rt4/lib:/opt/rt4/local/lib" /opt/rt4/etc/upgrade/reset-sequences</code>		# On new (Postgres) run: <code>PERL5LIB="/opt/rt4/lib:/opt/rt4/local/lib" /opt/rt4/etc/upgrade/reset-sequences</code>
			# On new (Postgres) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> -- to create CachedMembers in database

	===== Fix encoding patch =====		===== Fix encoding patch =====

Phanousk: /* Converting the database from MySQL (MariaDB) to PostgreSQL */

2024-07-18T15:55:59Z

Converting the database from MySQL (MariaDB) to PostgreSQL

← Older revision		Revision as of 11:55, 18 July 2024
Line 4:		Line 4:
	This HowTo assume you have set-up some current production RT instance with MySQL or MariaDB database server and want to move it to PostgreSQL which should be more robust. So here we go:		This HowTo assume you have set-up some current production RT instance with MySQL or MariaDB database server and want to move it to PostgreSQL which should be more robust. So here we go:
	# Backup youre production database!		# Backup youre production database!
	# On current (MySQL) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> to fix some garbage gathered through the years of upgrading your system. If you want to first check what it will do, just ommit the <code>--resolve</code> option.		# On current (MySQL) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> -- to fix some garbage gathered through the years of upgrading your system. If you want to first check what it will do, just ommit the <code>--resolve</code> option.
	# On current (MySQL) run: <code>/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500 </code>		# On current (MySQL) run: <code>/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500 </code>
	# On new (Postgres) run: <code>sudo -u postgres psql -c "drop database rtdb;"</code>		# On new (Postgres) run: <code>sudo -u postgres psql -c "drop database rtdb;"</code>
	# On new (Postgres) optionally run: <code>sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"</code> -- you will need this password in next step		# On new (Postgres) optionally run: <code>sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"</code> -- you will need this password in next step
	# On new (Postgres) run: <code>/opt/rt4/sbin/rt-setup-database --action create,schema,acl</code> -- This works only when there is a <code>$DatabaseAdmin</code> variable defined in RT_SiteConfig (or ~~RT_Configu~~). Usually this is set during the <code>./configure</code> phase of RT installation.		# On new (Postgres) run: <code>/opt/rt4/sbin/rt-setup-database --action create,schema,acl</code> -- This works only when there is a <code>$DatabaseAdmin</code> variable defined in RT_SiteConfig (or RT_Config). Usually this is set during the <code>./configure</code> phase of RT installation.
	# On new (Postgres) run: <code>patch -d /opt/rt4/ -p1 < path/to/fix-importer-encoding-for-pg.patch</code> -- Usually needed in non-english instances of RT. This patch is official by BP from year 2018 but surprisingly not present in current RTs in 2024 :).		# On new (Postgres) run: <code>patch -d /opt/rt4/ -p1 < path/to/fix-importer-encoding-for-pg.patch</code> -- Usually needed in non-english instances of RT. This patch is official by BP from year 2018 but surprisingly not present in current RTs in 2024 :).
	# On new (Postgres) run: <code>/opt/rt4/sbin/rt-importer /var/tmp/serialized</code>		# On new (Postgres) run: <code>/opt/rt4/sbin/rt-importer /var/tmp/serialized</code>

Phanousk at 15:54, 18 July 2024

2024-07-18T15:54:57Z

← Older revision		Revision as of 11:54, 18 July 2024
Line 1:		Line 1:
	= Converting the database from MySQL (MariaDB) to PostgreSQL =		= Converting the database from MySQL (MariaDB) to PostgreSQL =
	For some deeper details refer [[MigrateMysql2PostgresqlV4\|older article]] about migrating.		This is a very straightforward article for set-up environment. For some deeper details refer [[MigrateMysql2PostgresqlV4\|older article]] about migrating.

	This HowTo assume you have set-up some current production RT instance with MySQL or MariaDB database server and want to move it to PostgreSQL which should be more robust. So here we go:		This HowTo assume you have set-up some current production RT instance with MySQL or MariaDB database server and want to move it to PostgreSQL which should be more robust. So here we go:

Phanousk: new article

2024-07-18T15:53:52Z

new article

New page

= Converting the database from MySQL (MariaDB) to PostgreSQL =
For some deeper details refer [[MigrateMysql2PostgresqlV4|older article]] about migrating.

This HowTo assume you have set-up some current production RT instance with MySQL or MariaDB database server and want to move it to PostgreSQL which should be more robust. So here we go:
# Backup youre production database!
# On current (MySQL) run: <code>/opt/rt4/sbin/rt-validator --check --resolve</code> to fix some garbage gathered through the years of upgrading your system. If you want to first check what it will do, just ommit the <code>--resolve</code> option.
# On current (MySQL) run: <code>/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500 </code>
# On new (Postgres) run: <code>sudo -u postgres psql -c "drop database rtdb;"</code>
# On new (Postgres) optionally run: <code>sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"</code> -- you will need this password in next step
# On new (Postgres) run: <code>/opt/rt4/sbin/rt-setup-database --action create,schema,acl</code> -- This works only when there is a <code>$DatabaseAdmin</code> variable defined in RT_SiteConfig (or RT_Configu). Usually this is set during the <code>./configure</code> phase of RT installation.
# On new (Postgres) run: <code>patch -d /opt/rt4/ -p1 < path/to/fix-importer-encoding-for-pg.patch</code> -- Usually needed in non-english instances of RT. This patch is official by BP from year 2018 but surprisingly not present in current RTs in 2024 :).
# On new (Postgres) run: <code>/opt/rt4/sbin/rt-importer /var/tmp/serialized</code>
# On new (Postgres) run: <code>PERL5LIB="/opt/rt4/lib:/opt/rt4/local/lib" /opt/rt4/etc/upgrade/reset-sequences</code>

===== Fix encoding patch =====
#cat fix-importer-encoding-for-pg.patch
From 3b20d15fc798d2b3fd9c5667a074aa7cb22e14d9 Mon Sep 17 00:00:00 2001
From: sunnavy <sunnavy@bestpractical.com>
Date: Tue, 3 Jul 2018 03:26:36 +0800
Subject: [PATCH] Always pass UTF-8 decoded data to Create method for rt-importer

MySQL and Pg are different: serializer generates UTF-8 encoded bytes for
MySQL and UTF-8 decoded string for Pg, respectively. This is not a
problem if you stick to the same database, but when you try to migrate
an RT db from MySQL to Pg, encoding issues could happen because Pg
expects UTF-8 decoded values instead of encoded ones. MySQL on the other
hand, isn't that picky.

This commit fixes this particular issue by always passing UTF-8 decoded string to Create.
---
lib/RT/Migrate/Importer.pm | 9 +++++++++
1 file changed, 9 insertions(+)

diff --git a/lib/RT/Migrate/Importer.pm b/lib/RT/Migrate/Importer.pm
index 6eef04532a..7a04bffef1 100644
--- a/lib/RT/Migrate/Importer.pm
+++ b/lib/RT/Migrate/Importer.pm
@@ -319,6 +319,15 @@ sub Create {
my ($id, $msg) = eval {
# catch and rethrow on the outside so we can provide more info
local $SIG{__DIE__};
+
+ # Make sure data passed to Create are UTF-8 decoded. Without this,
+ # data could be be wrongly encoded on Pg.
+ for my $field ( keys %$data ) {
+ if ( $data->{$field} && !utf8::is_utf8( $data->{$field} ) ) {
+ utf8::decode( $data->{$field} );
+ }
+ }
+
$obj->DBIx::SearchBuilder::Record::Create(
%{$data}
);

=== Troubleshooting ===
===== Importer duplicate key error =====
If you encounter something like this:
[warning]: DBD::Pg::st execute failed: ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(26654) already exists. at /usr/local/share/perl/5.36.0/DBIx/SearchBuilder/Handle.pm line 634.
(/usr/local/share/perl/5.36.0/DBIx/SearchBuilder/Handle.pm:634)
and the import can't end succesfully, try this patch. It should be fixed in RT 5.0.8 and maybe in 4.4.8 also.

#cat Skip-unnecessary-post-actions-when-importing-cloned.patch
diff --git a/lib/RT/Migrate/Importer.pm b/lib/RT/Migrate/Importer.pm
index 946798bdb6..d3d3ec0171 100644
--- a/lib/RT/Migrate/Importer.pm
+++ b/lib/RT/Migrate/Importer.pm
@@ -361,6 +361,8 @@ sub Create {
}

$self->{ObjectCount}{$class}++;
+ return $obj if $self->{Clone};
+
$self->Resolve( $uid => $class, $id );

# Load it back to get real values into the columns