# CipherMail to use external PostgreSQL server

**URL:** <https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450>\
**Category:** Gateway\
**Created:** [June 29, 2015, 8:48pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450 "2015-06-29T20:48:05Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Laz\_C\_Peterson](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@Laz\_C\_Peterson](https://community.ciphermail.com/u/Laz_C_Peterson)\
**Post date:** [June 29, 2015, 8:48pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/1 "2015-06-29T20:48:05Z")

</div>

How can I configure CipherMail to use an external DB server?

We are looking to replicate PSQL using Postgres-XC on our database servers, and offload that duty from the mail server. Any experience with this?

~ Laz Peterson  
Paravis, LLC

---

<div class="post-metadata">

**Author:** ![Laz\_C\_Peterson](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@Laz\_C\_Peterson](https://community.ciphermail.com/u/Laz_C_Peterson)\
**Post date:** [June 29, 2015, 10:06pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/2 "2015-06-29T22:06:59Z")

</div>

Ohh, nevermind. I exported the database using the djigzo user, then restored it on the remote server. After that, I edited the conf/hibernate.cfg.xml file and changed the remote database there.

All sorted out!

But if anyone has experience using Postgres-XC and CipherMail, that would be good info to have.

~ Laz Peterson  
Paravis, LLC  
Ph: 951.319.3240 x201

> **···**
>
> > On Jun 29, 2015, at 1:48 PM, Laz C. Peterson \<laz(a)paravis.net\> wrote:
> > 
> > How can I configure CipherMail to use an external DB server?
> > 
> > We are looking to replicate PSQL using Postgres-XC on our database servers, and offload that duty from the mail server. Any experience with this?
> > 
> > ~ Laz Peterson  
> > Paravis, LLC  
> > \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > Users mailing list  
> > Users(a)lists.djigzo.com  
> > [https://lists.djigzo.com/lists/listinfo/users](https://lists.djigzo.com/lists/listinfo/users)

---

<div class="post-metadata">

**Author:** ![lst\_hoe021](https://avatars.discourse-cdn.com/v4/letter/l/58956e/32.png) [@lst\_hoe021](https://community.ciphermail.com/u/lst_hoe021)\
**Post date:** [June 30, 2015, 9:48am UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/3 "2015-06-30T09:48:18Z")

</div>

Zitat von "Laz C. Peterson" \<laz(a)paravis.net\>:

> Ohh, nevermind. I exported the database using the djigzo user, then  
> restored it on the remote server. After that, I edited the  
> conf/hibernate.cfg.xml file and changed the remote database there.
> 
> All sorted out!
> 
> But if anyone has experience using Postgres-XC and CipherMail, that  
> would be good info to have.

No experience with Postgres-XC but be sure to use a encrypted  
connection if using a remote database as the database contains  
sensitive information.

Regards

Andreas

---

<div class="post-metadata">

**Author:** ![Laz\_C\_Peterson](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@Laz\_C\_Peterson](https://community.ciphermail.com/u/Laz_C_Peterson)\
**Post date:** [June 30, 2015, 1:29pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/4 "2015-06-30T13:29:01Z")

</div>

Yes, all communication goes through encrypted tunnels.

Our goal is to have high availability for all services running on databases, but PostgreSQL seems to be a tough nut to crack.

~ Laz Peterson  
Paravis, LLC

> **···**
>
> > On Jun 30, 2015, at 2:48 AM, lst\_hoe02(a)kwsoft.de wrote:
> > 
> > Zitat von "Laz C. Peterson" \<laz(a)paravis.net\>:
> > 
> > > Ohh, nevermind. I exported the database using the djigzo user, then restored it on the remote server. After that, I edited the conf/hibernate.cfg.xml file and changed the remote database there.
> > > 
> > > All sorted out!
> > > 
> > > But if anyone has experience using Postgres-XC and CipherMail, that would be good info to have.
> > 
> > No experience with Postgres-XC but be sure to use a encrypted connection if using a remote database as the database contains sensitive information.
> > 
> > Regards
> > 
> > Andreas
> > 
> > \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > Users mailing list  
> > Users(a)lists.djigzo.com  
> > [https://lists.djigzo.com/lists/listinfo/users](https://lists.djigzo.com/lists/listinfo/users)

---

<div class="post-metadata">

**Author:** ![lst\_hoe021](https://avatars.discourse-cdn.com/v4/letter/l/58956e/32.png) [@lst\_hoe021](https://community.ciphermail.com/u/lst_hoe021)\
**Post date:** [June 30, 2015, 2:51pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/5 "2015-06-30T14:51:32Z")

</div>

Zitat von "Laz C. Peterson" \<laz(a)paravis.net\>:

> Yes, all communication goes through encrypted tunnels.
> 
> Our goal is to have high availability for all services running on  
> databases, but PostgreSQL seems to be a tough nut to crack.

Why?  
[https://wiki.postgresql.org/wiki/Replication,\_Clustering,\_and\_Connection\_Pooling](https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling)

Never used it because we are not really 24x7 but it looks like there  
are a lot of options to do hot/warm/cold standby/replication whatever.

Regards

Andreas

---

<div class="post-metadata">

**Author:** ![Laz\_C\_Peterson](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@Laz\_C\_Peterson](https://community.ciphermail.com/u/Laz_C_Peterson)\
**Post date:** [June 30, 2015, 2:53pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/6 "2015-06-30T14:53:21Z")

</div>

It turned out be a lot more difficult than I had expected. Beyond my intelligence level. Ha ha ha … I am so much more familiar with MySQL and have years of experience with replication, so that definitely is a better option for me.

~ Laz Peterson  
Paravis, LLC  
Ph: 951.319.3240 x201

> **···**
>
> > On Jun 30, 2015, at 7:51 AM, lst\_hoe02(a)kwsoft.de wrote:
> > 
> > Zitat von "Laz C. Peterson" \<laz(a)paravis.net\>:
> > 
> > > Yes, all communication goes through encrypted tunnels.
> > > 
> > > Our goal is to have high availability for all services running on databases, but PostgreSQL seems to be a tough nut to crack.
> > 
> > Why?  
> > [Replication, Clustering, and Connection Pooling - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling)
> > 
> > Never used it because we are not really 24x7 but it looks like there are a lot of options to do hot/warm/cold standby/replication whatever.
> > 
> > Regards
> > 
> > Andreas
> > 
> > \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > Users mailing list  
> > Users(a)lists.djigzo.com  
> > [https://lists.djigzo.com/lists/listinfo/users](https://lists.djigzo.com/lists/listinfo/users)

---

<div class="post-metadata">

**Author:** ![Django](https://avatars.discourse-cdn.com/v4/letter/d/e36b37/32.png) [@Django](https://community.ciphermail.com/u/Django)\
**Post date:** [July 1, 2015, 8:51am UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/7 "2015-07-01T08:51:16Z")

</div>

Heyho Laz!

Quoting "Laz C. Peterson" \<laz(a)paravis.net\>:

> It turned out be a lot more difficult than I had expected.

me2! 😕

May you send me your MaraDB database scheme?

ttyl  
Django

> **···**
>
> --  
> [http://dokuwiki.nausch.org](http://dokuwiki.nausch.org)  
> [http://wetterstation-pliening.info](http://wetterstation-pliening.info)  
> [http://ebersberger-liedersammlung.de](http://ebersberger-liedersammlung.de)

---

<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:** [July 1, 2015, 11:49am UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/8 "2015-07-01T11:49:14Z")

</div>

Supporting MySQL (MariaDB) requires a few minor changes to the code. The  
database schema generated by Hibernate (when using the MySQL dialect)  
contains a number of statements which MySQL does not like. For example  
the database name 'blob' and the column name "index" are reserved names  
for MySQL (not for Postgres). Also MySQL does not support long strings  
in indexes (see for more info  
[sql - MySQL error: key specification without a key length - Stack Overflow](http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length)).  
Hibernate also generates TINYBLOB for byte arrays but these are too  
small for most binary data (certificates etc.) so these must be changed  
to LONGBLOB. Because Hibernate generates a faulty database scheme, all  
unit tests that are using databases will fail because the unit tests  
will for each test clear the database and generate a fresh database  
using the automatically generated database scheme. With MySQL this will  
therefore not work because the scheme is not correct.

Changing the Blob table name, quoting the index column name, replacing  
text with varchar and removing some indexes fixes the issues for MySQL.  
However this results in a database schema change which then impacts all  
the existing CipherMail databases. Another option would be to use a  
different database naming strategy only for MySQL but this requires  
extensive testing. The tests can be changed to directly import sql  
instead of using the Hibernate tools but since there are a lot of tests  
it might be a lot of work.

The backup/restore functionality depends on Postgres so the  
backup/restore will not work unless the backup/restore script is  
modified to support MySQL. Even though in principle MySQL (and other  
databases) can be supported, it's probably not something we are going to  
support unless there is a good business reason to do so. Changing the  
code to support MySQL is not the biggest problem. Maintaining it is  
something else. Testing the gateway on all supported flavours of Linux  
is already a lot of work. If we add MySQL to the mix, testing and  
maintaining becomes even harder.

Similar to other products we have a "black-box" version of our gateway.  
The virtual appliance is a fully tested solution and what kind of  
database it uses should in principle be just an implementation detail.  
Because we standardize on a number of products for the virtual appliance  
we can maintain a high level of reliability.

To sum, we have it working on MySQL and in principle it's doable. But  
unless there is a good business reason to support MySQL we will not  
officially support it.

I can give you the information on the changes to be made to get it  
working with MySQL.

Kind regards,

Martijn Brinkers

> **···**
>
> On 07/01/2015 10:51 AM, django(a)nausch.org wrote:
> 
> > Quoting "Laz C. Peterson" \<laz(a)paravis.net\>:
> > 
> > > It turned out be a lot more difficult than I had expected.
> > 
> > me2! 😕
> > 
> > May you send me your MaraDB database scheme?
> 
> --  
> CipherMail email encryption
> 
> Open source email encryption gateway with support for S/MIME, OpenPGP  
> and PDF messaging.
> 
> > **[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:** ![Django](https://avatars.discourse-cdn.com/v4/letter/d/e36b37/32.png) [@Django](https://community.ciphermail.com/u/Django)\
**Post date:** [July 1, 2015, 12:00pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/9 "2015-07-01T12:00:08Z")

</div>

HI Laz!

may you can take a look into my mysql-database-structure (attached file).

ttyl  
Django

[djigzo\_mysql\_table\_structure.sql](https://community.ciphermail.com/uploads/short-url/tPolfptrG4Nb7BSRP47IAdwH1Sd.sql) (11.8 KB)

> **···**
>
> --  
> [http://dokuwiki.nausch.org](http://dokuwiki.nausch.org)  
> [http://wetterstation-pliening.info](http://wetterstation-pliening.info)  
> [http://ebersberger-liedersammlung.de](http://ebersberger-liedersammlung.de)

---

<div class="post-metadata">

**Author:** ![Laz\_C\_Peterson](https://avatars.discourse-cdn.com/v4/letter/l/94ad74/32.png) [@Laz\_C\_Peterson](https://community.ciphermail.com/u/Laz_C_Peterson)\
**Post date:** [July 1, 2015, 1:35pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/10 "2015-07-01T13:35:56Z")

</div>

Yes Martijn, I definitely understand about the solid reasons not to take on the major task of maintaining something that has no valid purpose in your business.

Let me ask you this … (Because this is the reason behind my interest.) Do you have any clients or CipherMail/Djigzo installations that are either clustered, configured for high availability or possibly replicating configurations/certificates across multiple sites in a live-live configuration? (Not a live-standby.)

Since all of our mail goes through your gateway, it does become a single point of failure. I guess we could set a mapping in Postfix to filter out the subject line tag for outgoing and incoming email, but that is a little beyond my capabilities and doesn’t necessary guarantee a CipherMail-based correspondence will be properly processed. Or maybe I am just overcomplicating things too much!

I do currently have two CipherMail installations now configured to share the same PostgreSQL database. The original goal was to have a bidirectionally replicated PostgreSQL, but I’ve got to do a lot more testing and that’s at least a half-dozen VM’s down the road (ha ha). MySQL replication is very well done for databases that aren’t extremely active with hundreds of thousands or millions of transactions going on. So that was my thought …

Again, I do agree with you that it is a lot of work to maintain something new that is outside of the given standard (ex, CipherMail appliance). And with any modifications to the existing version to support MySQL, the same would have to be done on my end to support MySQL in new versions. But yes I would love to see your notes if you don’t mind.

I looked at the datatype conversion tables supplied by MySQL for PostgreSQL migration. That was giving me a little bit of an issue at first, with the datatypes you mentioned.

All in all, this was simply my approach to build a HA cluster of CipherMail. I have no problem with PostreSQL, just my poor little brain likes to live in its comfort zone. 🙂

~ Laz Peterson  
Paravis, LLC  
Ph: 951.319.3240 x201

> **···**
>
> > On Jul 1, 2015, at 4:49 AM, Martijn Brinkers \<martijn(a)ciphermail.com\> wrote:
> > 
> > On 07/01/2015 10:51 AM, django(a)nausch.org wrote:
> > 
> > > Quoting "Laz C. Peterson" \<laz(a)paravis.net\>:
> > > 
> > > > It turned out be a lot more difficult than I had expected.
> > > 
> > > me2! 😕
> > > 
> > > May you send me your MaraDB database scheme?
> > 
> > Supporting MySQL (MariaDB) requires a few minor changes to the code. The  
> > database schema generated by Hibernate (when using the MySQL dialect)  
> > contains a number of statements which MySQL does not like. For example  
> > the database name 'blob' and the column name "index" are reserved names  
> > for MySQL (not for Postgres). Also MySQL does not support long strings  
> > in indexes (see for more info  
> > [sql - MySQL error: key specification without a key length - Stack Overflow](http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length)).  
> > Hibernate also generates TINYBLOB for byte arrays but these are too  
> > small for most binary data (certificates etc.) so these must be changed  
> > to LONGBLOB. Because Hibernate generates a faulty database scheme, all  
> > unit tests that are using databases will fail because the unit tests  
> > will for each test clear the database and generate a fresh database  
> > using the automatically generated database scheme. With MySQL this will  
> > therefore not work because the scheme is not correct.
> > 
> > Changing the Blob table name, quoting the index column name, replacing  
> > text with varchar and removing some indexes fixes the issues for MySQL.  
> > However this results in a database schema change which then impacts all  
> > the existing CipherMail databases. Another option would be to use a  
> > different database naming strategy only for MySQL but this requires  
> > extensive testing. The tests can be changed to directly import sql  
> > instead of using the Hibernate tools but since there are a lot of tests  
> > it might be a lot of work.
> > 
> > The backup/restore functionality depends on Postgres so the  
> > backup/restore will not work unless the backup/restore script is  
> > modified to support MySQL. Even though in principle MySQL (and other  
> > databases) can be supported, it's probably not something we are going to  
> > support unless there is a good business reason to do so. Changing the  
> > code to support MySQL is not the biggest problem. Maintaining it is  
> > something else. Testing the gateway on all supported flavours of Linux  
> > is already a lot of work. If we add MySQL to the mix, testing and  
> > maintaining becomes even harder.
> > 
> > Similar to other products we have a "black-box" version of our gateway.  
> > The virtual appliance is a fully tested solution and what kind of  
> > database it uses should in principle be just an implementation detail.  
> > Because we standardize on a number of products for the virtual appliance  
> > we can maintain a high level of reliability.
> > 
> > To sum, we have it working on MySQL and in principle it's doable. But  
> > unless there is a good business reason to support MySQL we will not  
> > officially support it.
> > 
> > I can give you the information on the changes to be made to get it  
> > working with MySQL.
> > 
> > Kind regards,
> > 
> > Martijn Brinkers
> > 
> > --  
> > CipherMail email encryption
> > 
> > Open source email encryption gateway with support for S/MIME, OpenPGP  
> > and PDF messaging.
> > 
> > [https://www.ciphermail.com](https://www.ciphermail.com)
> > 
> > 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)

---

<div class="post-metadata">

**Author:** ![Django](https://avatars.discourse-cdn.com/v4/letter/d/e36b37/32.png) [@Django](https://community.ciphermail.com/u/Django)\
**Post date:** [July 1, 2015, 1:50pm UTC](https://community.ciphermail.com/t/ciphermail-to-use-external-postgresql-server/450/11 "2015-07-01T13:50:42Z")

</div>

HI Laz,

Quoting "Laz C. Peterson" \<laz(a)paravis.net\>:

> Since all of our mail goes through your gateway, it does become a  
> single point of failure.

That's right!

> I do currently have two CipherMail installations now configured to  
> share the same PostgreSQL database.

Me 2. 😉 Here we have 4 fronted MX and will setup two  
ciphermail-gateways on different location. They should use our  
redundant MariaDB cluster.

> All in all, this was simply my approach to build a HA cluster of CipherMail.

yes!

> I have no problem with PostreSQL,

I have a major theme with postgresql, 'cause it's not allowed, only  
oracle or MariaDB.

ttyl  
Django

> **···**
>
> --  
> [http://dokuwiki.nausch.org](http://dokuwiki.nausch.org)  
> [http://wetterstation-pliening.info](http://wetterstation-pliening.info)  
> [http://ebersberger-liedersammlung.de](http://ebersberger-liedersammlung.de)