Migrate to PostgreSQL Server

The following guide will give you some hints for the migration from MySQL/MariaDB to PostgreSQL. Starting with Zammad 7, the only supported database server is PostgreSQL.

Warning

  • The commands on this page will only work with Zammad 5.3 or higher.

  • Make sure to migrate before upgrading to Zammad 7. An upgrade to Zammad 7 is not possible on a MySQL installation.

  • 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.

Install pgloader

$ apt update
$ apt install pgloader

Create pgloader Command File

Create a command file for pgloader with:

$ zammad run rake zammad:db:pgloader > /tmp/pgloader-command

Afterwards, you need to tweak the created file with the correct URL of the target PostgreSQL server.

-- Adjust the PostgreSQL URL below to correct value before executing this command file.
INTO pgsql://zammad:pgsql_password@localhost/zammad

You will at least need to replace psql_password placeholder in the provided example.

Verify the rest of the MySQL credentials in the command file, they should reflect the configuration of your current environment.

Database Credentials

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.

$ zammad run rake db:create

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, it is recommended to remove some cache files:

$ zammad run rails r 'Rails.cache.clear'
$ systemctl start zammad