Postgresql instructions and config-examples (work-in-progress)

[insensitiveclod]

Hey there.
I'm working on a way to provide postgresql users (and debian+postgresql users in particular, as that's me) a way to more easily get Vexim2 installed, configured and up-and-running.
A side issue i'd like to work on is to help people understand how to get Vexim2 working together with an LMTP/Sieve setup (which i have yet to finish, myself)

For this reason, I've forked to a local branch under https://github.com/insensitiveclod/vexim2 and started doing some re-ordering of documentation and config-files.
I do realize that one of the items in /TODO is to provide some auto-config script/framework that would configure the database and likely provide the right set of config files for use. This goes far beyond the scope of what I am trying to do on the short run, howver: getting people to actually be able to use PostgreSQL without too much fuss, right now , by providing ready-to-use configs + docs.

The main gist of the changes made so far:

• docs/configure now has a .mysql variant and a .pgsql variant
• docs/debian-conf.d now has a mysql and pgsql subdir
• vexim/config/variables.php.example has a check for dbtype.
• README.md has been edited to reflect the things above.
• A chapter about LMTP/Sieve considerations has been added to README.md

I would welcome some comments on whether or not this kind of way of providing configuration-alternatives is the right way to go or if there's better ways to go about handling the mysql/postgres issue.

In effect, there are now 4 variants of the exim-config included in the vexim2 package; the README tells you which one you'll need and what to change, where:
docs/config.mysql
docs/config.pgsql
docs/debian-conf.d/mysql
docs/debian-conf.d/pgsql

This works well enough, but whenever something changes somewhere in php or database-land, 4 sets of config end up needing to be checked/changed/verified.

While working on this, I came to some insights:

• The 'domain' lookups seem to really require some syntax-changes to be ported from mysql to pgsql. All (most?) of the other things work as is.

• The rest seems to be a question of replacing 'lookup mysql' with 'lookup pgsql', and converting 'quote_mysql' into 'quote_pgsql'.

• The remaining change is the 'hide mysql_servers' to 'hide pgsql_servers'. Trivial

This made me wonder about two aspects of all this:

1. We could eradicate most of the config-differences between the mysql and pgsql variants if we could 'abstract' the 'lookup mysql|pgsql' to something like 'lookup $dbtype' ; similar with 'quote_mysql|pgsql'. A few if-then-elses for specific syntax-differences would take care of the rest.
2. Would there be a way of auto-creating the docs/configure file and/or the debian-conf.d files from some set of templates so that maintenance of these scripts for the devs becomes easier to handle. Then again, not too much changes there, I reckon, so this question might well have 'Nah, not really' as an answer: there's a good chance of adding more complexity and things to maintain for just a slight benefit.

I'd love to hear some thoughts on both the work I've done so far and the approach I went for; as well as the two considerations below. My intention, of course, is to provide something that could/would be included in mainline vexim2 at some point.

Thanks in advance.

[rimas-kudelis]

I began working on similar unification last year as part of my effort to review the whole config file, but never really finished it. I could push my work to a branch if you would like to take a look and maybe take it further.

[insensitiveclod]

That'd be very helpful and very welcome!

I've been running vexim with postgres and dovecot for many years now and it's been as stable as a rock. However, it seems there might be recent changes in the recent vexim releases regarding the database, etc..

I am currently debugging the current state of postgresql/vexim2 on a new installation; there's likely something that recently changed somewhere along the way that's giving me issues (pgsql lookups in vexim config fails because of some type issue.)

Extra material to look at would be greatly appreciated.

[rimas-kudelis]

Pushed as 9dcd750 to https://github.com/rimas-kudelis/vexim2/commits/updated-exim4-config. I named the new file configure.new to have both of them available while developing. And configure.default is a vanilla copy of Exim's default config file as of last year, I think.

Since the change is rather big and I think incorporates a few other tasks as well, I ran out of enthusiasm somewhere along the way, and never really managed to finish it or test it, but if you're up for it, there's certainly no reason for us to duplicate each other's work. We could do it together if you like.

One of my goals is/was to unify not only config options, but queries as well. I think we should avoid using MySQL-specific of PostgreSQL-specific syntax and instead stick with what both database engines support.

[insensitiveclod]

Thanks !
Let me take a look at the work done and see just how much there is to do.

I concur with the latter assertion, too.
I ended up tracing the issue above to the way Mysql and Postgresql vary greatly regarding the way they handle an AND on a 'smallint' datatype. In this case users.on_spamassassin AND domains.spamassassin in the virtual_domains router .

Postgresql required some type-casting to allow that kind of thing. Works well enough now; though I would imagine there might be a more implementation-insensitive manner to handle this issue without having to go and change the table-definition to use 'boolean' instead of 'smallint' on everyone's install.

My current short-term goal is to get the 'mainline' mysql-version of everything to have a postgresql-equivalent that works. After that's made working and I understood the differences/challenges, an approach to how to make both work from a single query/config may present itself, I hope.

Thanks for the headsup about the push. I hope to have some evenings this week to put work into it.

[rimas-kudelis]

IMO it's OK to use boolean fields where supported. MySQL aliases them to tinyint, but that doesn't mean we can't call them boolean in our scripts.