ManualDatabaseConversion

Converting the database from MySQL (MariaDB) to PostgreSQL

This is a very straightforward article for set-up environment. For some deeper details refer 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:

1. Backup youre production database!
2. On current (MySQL) run: /opt/rt4/sbin/rt-validator --check --resolve -- 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 --resolve option.
3. On current (MySQL) run: /opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500
4. On new (Postgres) run: sudo -u postgres psql -c "drop database rtdb;"
5. On new (Postgres) optionally run: sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';" -- you will need this password in next step
6. On new (Postgres) run: /opt/rt4/sbin/rt-setup-database --action create,schema,acl -- This works only when there is a $DatabaseAdmin variable defined in RT_SiteConfig (or RT_Config). Usually this is set during the ./configure phase of RT installation.
7. On new (Postgres) run: patch -d /opt/rt4/ -p1 < path/to/fix-importer-encoding-for-pg.patch -- 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 :).
8. On new (Postgres) run: /opt/rt4/sbin/rt-importer /var/tmp/serialized
9. On new (Postgres) run: PERL5LIB="/opt/rt4/lib:/opt/rt4/local/lib" /opt/rt4/etc/upgrade/reset-sequences
10. On new (Postgres) run: /opt/rt4/sbin/rt-validator --check --resolve -- to create CachedMembers in database

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