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

1. Backup your 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
   • BEWARE: If you use RT::Extension::MergeUsers, you should disable it before serialization. Otherwise the import won't run correctly.
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
   • Resetting sequences is necessary especially for Postgres + data serialized with --clone.
10. On new (Postgres) run: /opt/rt4/sbin/rt-validator --check --resolve --force -- 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