If You think there’s an easy and fully automated way of migrating a Roundcube installation running for years on MySQL – to a PostgreSQL database – forget it. You will probably have to take a few steps before automation could be applied to this process. In this post I’m trying to describe all steps of a successful migration of Roundcube from o MySQL to a PostgreSQL database.

Requirements

• A box with Roundcube running on MySQL.
• A running PostgreSQL server.
• Bash shell with root access.
• phpMyadmin and phpPgAdmin may become handy.

Before You begin

• Backup Roundcube’s MySQL database.
• Backup Roundcube’s files.
• Read the whole article before You begin.

Preparations

Upgrade Roundcube

Stay on MySQL and upgrade Roundcube to the latest version. And don’t forget about Roundcube’s upgrading instructions and the important section Post-Upgrade Activities.

Cleanup current MySQL data

After years on MySQL and upgrades the database contains a lot of junk, that is probably not needed anymore and can cause (and will) problems during importing (duplicates, unexpected columns, etc.).

First we have to clean up all core tables created by mysql.initial.sql.

Clean up users table

Remove all old users, use the last_login column of table users. In my case there were a lot of duplicated entries that caused problems during importing data into PostgreSQL. So it’s better to cleanup before.

SELECT *
    FROM `users`
    WHERE `last_login` < '2013-01-01'
;

If the result is OK – delete:

DELETE
  FROM `users` 
  WHERE `last_login` < '2013-01-01'
;

Clean up sessions table

DELETE
  FROM `session` 
  WHERE `changed` < '2013-01-01'
;

Clean up cache tables

DELETE
  FROM `cache_messages` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

DELETE
  FROM `cache_thread` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

DELETE
  FROM `cache_index` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

DELETE
  FROM `cache` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

DELETE
  FROM `cache_shared` 
  WHERE cache_key NOT IN (
    SELECT cache_key  FROM `cache` WHERE 1
  )
;

Clean up contact tables

Show all contacts assigned to nonexisting users:

SELECT * 
  FROM `contacts` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

And again, if You’re OK with the results, You can delete them:

DELETE
  FROM `contacts` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

and then:

DELETE
  FROM `contactgroupmembers` 
  WHERE contact_id NOT IN (
    SELECT contact_id  FROM `contacts` WHERE 1
  )
;

DELETE
  FROM `contactgroups` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

Clean up identities table

Show all identities assigned to nonexisting users:

SELECT * 
  FROM `identities` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

And again, if You’re OK with the results – delete:

DELETE
  FROM `identities` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

Clean up dictionary table

DELETE
  FROM `dictionary` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

Clean up searches table

DELETE
  FROM `searches` 
  WHERE user_id NOT IN (
    SELECT user_id  FROM `users` WHERE 1
  )
;

Verify the current MySQL database structure

I had problems because my contacts table contained columns that were not expected during import. That’s why You should verify, that the cleaned up MySQL database has a structure expected by mysql.initial.sql. In my case there was an alias column in the contacts table – I simply deleted it.

So in general, remove all columns that are not expected by the initial structure, or the initial structure + modifications applied by plugins You use. So in some cases You will have to check the inital SQL for used plugins too, and verify against both (when You really need to export/import plugins data).

Prepare the destination PostgreSQL database

Switch to PostgreSQL console:

psql -U postgres

Create a PostgreSQL user for Roundcube:

CREATE USER roundcube_dbuser 
    WITH PASSWORD 'roundcube_dbpassword';

Create a PostgreSQL database for Roundcube:

CREATE DATABASE roundcube_dbname;

Setup database privileges:

GRANT ALL PRIVILEGES ON DATABASE roundcube_dbname
   TO roundcube_dbuser;
ALTER DATABASE roundcube_dbname OWNER 
   TO roundcube_dbname;

and exit the console (\q).

Prepare the initial PostgreSQL database structure

Change directory to the root of your upgraded Roundcube:

cd /var/www/html/roundcube

All initial database structures are found in ./SQL/*.initial.sql. What we need is the postgres.initial.sql.

cat SQL/postgres.initial.sql \
    | psql -U roundcube_dbuser -d roundcube_dbname

Database structure created, now it’s a good idea to do the same with all plugins:

for plugin_initial_sql in ./plugins/*/SQL/postgres.initial.sql; \
do \
    cat $plugin_initial_sql \
        | psql -U roundcube_dbuser -d roundcube_dbname \
done

The PostgreSQL database structure for Roundcube is now ready to accept data.

Export

Exporting Roundcube’s MySQL database data to CSV

Now it’s time to dump the MySQL database. We will not dump in a classic way, this becomes tricky. First create a directory for the dump and make it accessible for the MySQL and the PostgreSQL user:

mkdir /tmp/sqldump
chmod 777 /tmp/sqldump

And now dump the cleaned up MySQL database:

mysqldump \
    --tab=/tmp/sqldump \
    --fields-terminated-by="\t" \
    --fields-optionally-enclosed-by='"' \
    --fields-escaped-by='\\' \
    --lines-terminated-by='\n' \
    --default-character-set=utf8 \
    --skip-tz-utc \
    -u roundcube_dbuser \
    -p \
    roundcube_dbname

This will create dumps in /tmp/sqldump/ and all *.txt files are now important, because they contain the data used during import.

Import

Importing Roundcube’s CSV data into PostgreSQL

Now we have the destination database prepared and the source database (MySQL) after cleaning up is already exported to CSV in /tmp/sqldump. We’re ready to import the data into our PostgreSQL database:

for tablename in \
    users \
    cache \
    cache_index \
    cache_messages \
    cache_thread \
    contactgroups \
    contacts \
    contactgroupmembers \
    dictionary \
    identities \
    searches \
    session; \
do \
    datafile=/tmp/sqldump/${tablename}.txt; \
    echo $tablename;
    echo "COPY ${tablename}" \
        " FROM '${datafile}'" \
        " WITH DELIMITER AS E'\t'" \
        " NULL AS E'\\N'" \
        " ESCAPE E'\\\'" \
        " CSV" \
        | psql -U postgres -d roundcube_dbname; \
done

If something goes wrong in this step, You’ll probably have to modify by hand rows that trigger errors – they’re found in /tmp/sqldump/*.txt files. If there are many rows causing errors You should consider cleaning up Your source database again and recheck the source database structure again. Or just drop a comment and show me the error messages.

Finishing touches

After a successful import it’s time to switch Roundcube to use PostgreSQL. Just edit ./config/db.inc.php:

$config['db_dsnw'] = 'pgsql://roundcube_dbuser:roundcube_dbpassword@localhost/roundcube_dbname';

I hope this helps 8-)