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.

Note

🤓 Zammad version requirement ahead

Below commands will only work with Zammad 5.3.0 or higher. Please make sure to update the latest (MySQL supported) version first.

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¶

Stop Zammad:
```
$ systemctl stop zammad
```
Create a backup of your instance.

Install PostgreSQL¶

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

# CentOS 7
$ yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ yum install postgresql14-server postgresql14-contrib
$ postgresql14-setup initdb
$ systemctl start postgresql14
$ systemctl enable postgresql14

# CentOS 8
$ yum install postgresql-server postgresql-contrib
$ postgresql-setup initdb
$ systemctl start postgresql
$ systemctl enable postgresql

$ zypper refresh
$ zypper install postgresql postgresql-server postgresql-contrib
# openSuSE 15 also requires:
$ zypper install postgresql-server-devel
$ systemctl start postgresql
$ systemctl enable postgresql

Please also have a look at Configure Database server.

Nothing to do, continue with the next step. 🎉

Install PostgreSQL Dependencies

$ apt install libpq-dev

# CentOS 7
$ yum install postgresql14-libs postgresql14-devel

# CentOS 8
$ yum install postgresql-libs postgresql-devel

$ zypper install postgresql-devel

Install Gems for Zammad

$ su - zammad
$ bundle config set without "test development mysql"
$ bundle install

# CentOS 7 users - above command might fail, run the following
# command and repeat above bundle install.
# Adjust pg_config path according to your environment
$ gem install pg -v '1.2.3' -- --with-pg-config=/usr/pgsql-14/bin/pg_config

Install pgloader¶

$ apt update
$ apt install pgloader

$ yum install -y https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7-x86_64/pgloader-3.6.2-1.rhel7.x86_64.rpm

$ zypper refresh
$ zypper install pgloader

Create pgloader command file¶

Create a command file for pgloader with:

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

$ su - zammad
$ 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

$ su - zammad
$ rake db:create

Migrate¶

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

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

Once you are ready and setup you can start the actual migration:

$ pgloader --verbose /tmp/pgloader-command

Finishing¶

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

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

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

# Run as root
$ systemctl start zammad