ManualDatabaseConversion
Jump to navigation
Jump to search
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:
- Backup youre production database!
- 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. - On current (MySQL) run:
/opt/rt4/sbin/rt-serializer --clone --page 200 --directory /var/tmp/serialized --force --size 500
- On new (Postgres) run:
sudo -u postgres psql -c "drop database rtdb;"
- On new (Postgres) optionally run:
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'some_password';"
-- you will need this password in next step - 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. - 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 :). - On new (Postgres) run:
/opt/rt4/sbin/rt-importer /var/tmp/serialized
- On new (Postgres) run:
PERL5LIB="/opt/rt4/lib:/opt/rt4/local/lib" /opt/rt4/etc/upgrade/reset-sequences
- 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