Re: [Sqlgrey-users] postgery to sqlgrey

[Lionel B. <lio...@bo...>]

Farkas Levente wrote the following on 12/14/04 15:04 :

> hi,
> we may be switch from postgrey to sqlgray. afaik they use the same 
> database setup since sql is a for of post. is there any way to migrate 
> from postgrey to sqlgrey? we wouldn't like tostart with a fresh clean 
> database since it cause a lot's of headacke for all of the currently 
> confirmed sender/recepints. it's be very useful.


Currently there's no way of migrating the greylisting data in the 
postgrey database to sqlgrey. I'm not sure if reusing postgrey data is a 
good idea, I'd have to check in details the postgrey version you use to 
see if it would be doable. That would be quite a lot of work and I don't 
think I can find the time for such a specific task.
Anyway, with auto-whitelisting your users shouldn't notice much of a 
delay. Just make the switch on Friday's evening and let the marginal 
week-end trafic populate the auto-whitelist tables.
In the best case, to ease transition, what I *could* add is a way to 
plug another policy daemon into sqlgrey and let the messages pass and 
populate the database when the other policy daemon answers "DUNNO" or 
"PREPEND *", that would need a bit of tweaking to not ask the other 
policy daemon when not needed. That will not make it on the top of my 
TODO list in the short future though (the more code in and the less 
manageable the project becomes).

> another question is there any howto/readme or other short description 
> how to use eg. mysql data replication amonf the mx mail servers with 
> sqlgrey? that's the only reason we like to switch to sqlgrey since we 
> _must_ use one greylist server (otherwise it's unusable) and currently 
> if our postgrey server die (the service or the whole machine), than 
> all of our mx's pofix die (postfix can't work without the configured 
> policy server). so one good solution to use a separate sql server on 
> all mx host and replicate the database among them. any advice?


Configuring replication between different MySQL databases would imply 
master-master setups (you want each database to be able to write to each 
other) and it could trigger nasty primary key clashes. IIRC MySQL 
replication doesn't ensure that a statement did update all databases 
when it is commited : 2 databases could have the same primary key added 
for one of SQLgrey's tables -> expect nasty errors (and look in MySQL 
docs for how it handles such situations).

In your configuration, I'd advise you to do the following :
- use one sqlgrey instance on each of your postfix servers using 
greylisting, all configured to use the same database.
- if needed (sqlgrey can cope with database unavailability), configure 
replication to a slave database.

If the database fails to answer properly (connection down or sql 
errors), sqlgrey won't crash (this was fully debugged not long ago) but 
will try to reconnect on each new message coming in and unless the 
database comes back let them pass (no greylisting) to ensure no 
legitimate trafic will be blocked. You will not have a single point of 
*failure*, but you will have a single point of *inefficiency* : the 
database.
If you use a slave database, you can either : make it take over the 
master's IP, update the DNS to reroute sqlgrey connections to the slave 
(my personnal choice - put low TTLs in the DNS - this way you don't have 
to take the master fully down if only its database system is down), or 
(if it can be made quickly) put the master back online.
After switching to the slave, you'll probably have to make it the master 
and reconfigure your old master as a slave.
If you do this, please consider writing a quick HOWTO describing the 
failure handling and master back online, even something sketchy will do, 
I can write the final HOWTO but I need the experience of someone who 
actually did setup and test a master-slave scenario.

In your case, there is two things I think SQLgrey still lacks :
- e-mail the postmaster or a configurable address when the database goes 
down/comes back online (currently in my TODO list), work-around : use 
Nagios to monitor your databases,
- allow automatic switching to fallback databases, but for this to be 
implemented correctly I need to know how each RDBMs handle a master 
coming back online (will it become a slave as I believe or will it take 
its master role back eventually ? How do SQLgrey check which database is 
the master at startup time : think of a SQLgrey restart after the master 
went down and comes back as a slave...).

Finally if you are worried about MySQL stability consider PostgreSQL. 
I've not yet personnally seen MySQL crash on me, but I worked as a 
software developper for a company that uses MySQL on really high loads 
(2x 4-way Hyperthreading Xeon in master-slave setup that just have the 
head out of the water) and they had crashes with 4.0 versions at least 
once a month. PostgreSQL seems more robust to me. In the end you should 
use the database you are the most comfortable with (did already 
successfully backup and restore, know how to optimize for speed or 
memory footprint and so on).

> thanks in advance.
>

Your welcomed,

Lionel.