# Database Migration

**URL:** <https://community.ciphermail.com/t/database-migration/506>\
**Category:** Gateway\
**Created:** [June 2, 2016, 10:48pm UTC](https://community.ciphermail.com/t/database-migration/506 "2016-06-02T22:48:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Prof\_Dr\_Michael\_Sche](https://avatars.discourse-cdn.com/v4/letter/p/2bfe46/32.png) [@Prof\_Dr\_Michael\_Sche](https://community.ciphermail.com/u/Prof_Dr_Michael_Sche)\
**Post date:** [June 2, 2016, 10:48pm UTC](https://community.ciphermail.com/t/database-migration/506/1 "2016-06-02T22:48:55Z")

</div>

Dear All,

In preparing to move the two Ciphermail servers (djigzo\_3.0.5-0) I am keeping in a dual SOHO situation from Centos 6 to Centos 7, I would also very much like to switch from postgres to mysql. That would allow me to work with a Percona mysql cluster to synchronize the servers instead of continuing with bucardo synchronization or investigate the new built-in and probably better postgres synchronization possibilities.

Based on the install instructions, I was able to setup Ciphermail on Centos 7 with mysql. Running "mysql djigzo \< /usr/local/djigzo/conf/database/sql/djigzo.mysql.sql" (in my case modified to run on an external host) does create the usual 29 tables.

What I find myself unable to do is to convert the data (admin, atmin\_authority, authority, blob, current certificates, certificates\_email, properties, crls, keyring, keyring\_email, keyring\_userid, keystore, named\_blob, pgp\_trust\_list, pgp\_trust\_list\_namevalues, properties, properties\_namevalues, userpreferences, userpreferences\_certificates, userpreferences\_inheritedpreferences, userpreferences\_named\_certificates, users) from postgres to mysql. I was naively thinking that ODBC and mysql workbench could do the job in a straightforward manner, but I did not find that feasible. For example, there are lots of "truncated key column length ..." warnings (logfile available upon request) and I did not find the result to work. I must admit that I am far from being a database expert also.

Is it realistic to migrate the database or would one have to start from scratch even in terms of users and certificates?

Regards,

Michael

---

<div class="post-metadata">

**Author:** ![martijn](https://dub1.discourse-cdn.com/flex017/user_avatar/community.ciphermail.com/martijn/32/127_2.png) [@martijn](https://community.ciphermail.com/u/martijn)\
**Post date:** [June 3, 2016, 12:48pm UTC](https://community.ciphermail.com/t/database-migration/506/2 "2016-06-03T12:48:46Z")

</div>

Unfortunately an easy migration path is currently not available. The  
problem is that MySQL has some strict requirements about table names,  
length of fields, types etc. It's therefore not as easy as copying the  
tables one to one. The most important data is the certificates, keys and  
PGP keys. These can be exported from the gateway to a file which can  
then be imported into the gateway. A user object is only required if you  
override an inherited value so in most cases a user object is not required.

Kind regards,

Martijn Brinkers

> **···**
>
> On 06/03/2016 12:48 AM, Prof. Dr. Michael Schefczyk wrote:
> 
> > Dear All,
> > 
> > In preparing to move the two Ciphermail servers (djigzo\_3.0.5-0) I am  
> > keeping in a dual SOHO situation from Centos 6 to Centos 7, I would  
> > also very much like to switch from postgres to mysql. That would  
> > allow me to work with a Percona mysql cluster to synchronize the  
> > servers instead of continuing with bucardo synchronization or  
> > investigate the new built-in and probably better postgres  
> > synchronization possibilities.
> > 
> > Based on the install instructions, I was able to setup Ciphermail on  
> > Centos 7 with mysql. Running "mysql djigzo \<  
> > /usr/local/djigzo/conf/database/sql/djigzo.mysql.sql" (in my case  
> > modified to run on an external host) does create the usual 29  
> > tables.
> > 
> > What I find myself unable to do is to convert the data (admin,  
> > atmin\_authority, authority, blob, current certificates,  
> > certificates\_email, properties, crls, keyring, keyring\_email,  
> > keyring\_userid, keystore, named\_blob, pgp\_trust\_list,  
> > pgp\_trust\_list\_namevalues, properties, properties\_namevalues,  
> > userpreferences, userpreferences\_certificates,  
> > userpreferences\_inheritedpreferences,  
> > userpreferences\_named\_certificates, users) from postgres to mysql. I  
> > was naively thinking that ODBC and mysql workbench could do the job  
> > in a straightforward manner, but I did not find that feasible. For  
> > example, there are lots of "truncated key column length ..." warnings  
> > (logfile available upon request) and I did not find the result to  
> > work. I must admit that I am far from being a database expert also.
> > 
> > Is it realistic to migrate the database or would one have to start  
> > from scratch even in terms of users and certificates?
> 
> --  
> CipherMail email encryption
> 
> Email encryption with support for S/MIME, OpenPGP, PDF encryption and  
> secure webmail pull.
> 
> > **[CipherMail email encryption and digital signatures](https://www.ciphermail.com)**
> >
> > Easy to use server-side email encryption for automatic encryption and digital signing of email.
> 
> Twitter: [http://twitter.com/CipherMail](http://twitter.com/CipherMail)
> 
> --  
> CipherMail email encryption
> 
> Email encryption with support for S/MIME, OpenPGP, PDF encryption and  
> secure webmail pull.
> 
> > **[CipherMail email encryption and digital signatures](https://www.ciphermail.com)**
> >
> > Easy to use server-side email encryption for automatic encryption and digital signing of email.
> 
> Twitter: [http://twitter.com/CipherMail](http://twitter.com/CipherMail)

---

<div class="post-metadata">

**Author:** ![Prof\_Dr\_Michael\_Sche](https://avatars.discourse-cdn.com/v4/letter/p/2bfe46/32.png) [@Prof\_Dr\_Michael\_Sche](https://community.ciphermail.com/u/Prof_Dr_Michael_Sche)\
**Post date:** [June 3, 2016, 2:31pm UTC](https://community.ciphermail.com/t/database-migration/506/3 "2016-06-03T14:31:19Z")

</div>

Dear Martijn,

Thank you for confirming this. I did try all I could in terms of adapting the prefixes and table names in the database I did convert through the mysql workbench. In the end, foreign key constraints and the like did seem unsurmountable to me. I will now try the way of moving the certificates and manually configuring the rest. My database is not that large in terms of number of users and the like, that this would be a major issue.

Regards,

Michael

> **···**
>
> -----Ursprüngliche Nachricht-----  
> Von: users-bounces(a)lists.djigzo.com [mailto:users-bounces(a)[lists.djigzo.com](http://lists.djigzo.com)] Im Auftrag von Martijn Brinkers  
> Gesendet: Freitag, 3. Juni 2016 14:49  
> An: users(a)lists.djigzo.com  
> Betreff: Re: Database Migration
> 
> On 06/03/2016 12:48 AM, Prof. Dr. Michael Schefczyk wrote:
> 
> > Dear All,
> > 
> > In preparing to move the two Ciphermail servers (djigzo\_3.0.5-0) I am  
> > keeping in a dual SOHO situation from Centos 6 to Centos 7, I would  
> > also very much like to switch from postgres to mysql. That would allow  
> > me to work with a Percona mysql cluster to synchronize the servers  
> > instead of continuing with bucardo synchronization or investigate the  
> > new built-in and probably better postgres synchronization  
> > possibilities.
> > 
> > Based on the install instructions, I was able to setup Ciphermail on  
> > Centos 7 with mysql. Running "mysql djigzo \<  
> > /usr/local/djigzo/conf/database/sql/djigzo.mysql.sql" (in my case  
> > modified to run on an external host) does create the usual 29 tables.
> > 
> > What I find myself unable to do is to convert the data (admin,  
> > atmin\_authority, authority, blob, current certificates,  
> > certificates\_email, properties, crls, keyring, keyring\_email,  
> > keyring\_userid, keystore, named\_blob, pgp\_trust\_list,  
> > pgp\_trust\_list\_namevalues, properties, properties\_namevalues,  
> > userpreferences, userpreferences\_certificates,  
> > userpreferences\_inheritedpreferences,  
> > userpreferences\_named\_certificates, users) from postgres to mysql. I  
> > was naively thinking that ODBC and mysql workbench could do the job in  
> > a straightforward manner, but I did not find that feasible. For  
> > example, there are lots of "truncated key column length ..." warnings  
> > (logfile available upon request) and I did not find the result to  
> > work. I must admit that I am far from being a database expert also.
> > 
> > Is it realistic to migrate the database or would one have to start  
> > from scratch even in terms of users and certificates?
> 
> Unfortunately an easy migration path is currently not available. The problem is that MySQL has some strict requirements about table names, length of fields, types etc. It's therefore not as easy as copying the tables one to one. The most important data is the certificates, keys and PGP keys. These can be exported from the gateway to a file which can then be imported into the gateway. A user object is only required if you override an inherited value so in most cases a user object is not required.
> 
> Kind regards,
> 
> Martijn Brinkers
> 
> --  
> CipherMail email encryption
> 
> Email encryption with support for S/MIME, OpenPGP, PDF encryption and secure webmail pull.
> 
> > **[CipherMail email encryption and digital signatures](https://www.ciphermail.com)**
> >
> > Easy to use server-side email encryption for automatic encryption and digital signing of email.
> 
> Twitter: [http://twitter.com/CipherMail](http://twitter.com/CipherMail)
> 
> --  
> CipherMail email encryption
> 
> Email encryption with support for S/MIME, OpenPGP, PDF encryption and secure webmail pull.
> 
> > **[CipherMail email encryption and digital signatures](https://www.ciphermail.com)**
> >
> > Easy to use server-side email encryption for automatic encryption and digital signing of email.
> 
> Twitter: [http://twitter.com/CipherMail](http://twitter.com/CipherMail)  
> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> Users mailing list  
> Users(a)lists.djigzo.com  
> [https://lists.djigzo.com/lists/listinfo/users](https://lists.djigzo.com/lists/listinfo/users)