Migrate to PostgreSQL server

Support for MySQL/MariaDB will be dopped in Zammad 7.0 upwards. Make sure to migrate your existing instance of Zammad to PostgreSQL before that update.

The following guide will provide you with a rough direction through that migration process.

Warning

Proof of concept ahead

As the technical details may differ from system to system, this guide comes without any warranty. Please proceed at your own risk. In doubt please refer to the documentation of the tools used.

Preparation

  1. Stop Zammad:

    $ systemctl stop zammad
    
  2. Create a backup of your instance.

Install PostgreSQL

$ apt update
$ apt install postgresql postgresql-contrib
$ systemctl start postgresql
$ systemctl enable postgresql

Please also have a look at Configure Database server.

Nothing to do, continue with the next step. 🎉

Database Credentials

Get the credentials Zammad is currently using to access your MySQL/MariaDB database from config/database.yml. You will need them later.

Now adjust the configuration file to fill in the credentials for your new PostgreSQL server. Use postgresql as adapter.

Tip

🤓 For easiest usage …

If you provide your Zammad user with database creation permission, you can run db:create in the following section. If you don’t want that, you’ll have to create the database manually.

Create empty database

Now you need to create an empty database in PostgreSQL.

$ su - zammad
$ rake db:create

Install pgloader and migrate

$ apt update
$ apt install pgloader

Create command file

Create a command file for pgloader for example in /tmp/pgloader-command:

LOAD DATABASE
             FROM mysql://zammad:mysql_password@localhost/zammad
             INTO pgsql://zammad:pgsql_password@localhost/zammad

ALTER SCHEMA 'zammad' RENAME TO 'public'

WITH BATCH CONCURRENCY = 1;

If your database names and/or database usernames are different from zammad adjust accordingly. And don’t forget to replace mysql_password and psql_password.

Migrate

You can check your configuration by running pgloader in a dry run first:

$ pgloader --dry-run /tmp/pgloader-command

Finishing

After the migration has completed, you’ll better clear some cache files:

$ su - zammad
$ rails r 'Rails.cache.clear'

# Run as root
$ systemctl start zammad