Re: [Sqlgrey-users] postgery to sqlgrey

[Farkas L. <lf...@bp...>]

Lionel Bouton wrote:
> 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 

always the latest:-) i package the redhat/fedora postgrey rpms.

> 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.

that's not so easy! most of these important emails comes a few hours 
before deadlines (usualy thuesday 18:00 and thursday 18:00) which makes 
the think a bit complicated:-(

> 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).

if easier it can be a solution too.

>> 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 sqlgrey is not better than postgrey in term of "failure" then i don't 
see any good reason why switch to sqlgrey? do you know?

> - if needed (sqlgrey can cope with database unavailability), configure 
> replication to a slave database.

it is currently possible?

> If the database fails to answer properly (connection down or sql 
> errors), sqlgrey won't crash (this was fully debugged not long ago) but 

what does the "won't crash" means? in this case response with a DUNNO?

> 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 

aha.

> *failure*, but you will have a single point of *inefficiency* : the 
> database.

that's far better!

> 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.

imho it'd be better to be able to configure more (slave) sql server for 
sqlgrey in stead of dns manipulation.

> 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.

this dns trick is not realy like to me so probably we won't use this.

> 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,

we use nagios but an automatick failover what i realy would like to see.

> - 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 

imho it's be enough to switch to the slave and the slave can replicate 
eg. once a day the master (before becoming the master) and nagios can 
notify the sysadm to restore the master manualy and restart sqlgrey 
manualy to use the original master. this scenario as the first step 
would be better then the current one.

> 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...).

NEVER SWITCH BACK TO THE ORIGINAL MASTER AUTOMATICALY!
i think it's acceptable to be some kind of manual configuration in this 
case.

> 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).

no i'm not worry about any kind of sql server. i worry about the system 
ie. the machine itself (hardware, network etc..) i don't see any sql 
server crash either but see many kind of hardware, network problem.

-- 
   Levente                               "Si vis pacem para bellum!"