Re: [Sqlgrey-users] postgery to sqlgrey

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

Lionel Bouton wrote:
> In the case of one sqlgrey instance on each mx even if you don't have 
> *any* slave, as I explained you won't have any single point of failure 
> (you won't have any greylisting when the database wil go down though).

you've got right, so i wouldn't like failure neither postfix without 
greylist:-)

>> [...] - that's why there is slave ldap servers,
> 
> 
> 
> The devil is in the details. If I understand correctly, you want SQLgrey 
> instances to know a slave or list of slaves and fallback automatically 
> to the slave or one of the slaves if the master doesn't answer.
> What I don't know is how you will solve the following problems related 
> to the writes done to the database by SQLgrey :
> - one SQLgrey can't access the master due to a temporary link problem 
> and switch to the slave and try to update its database content although 
> the slave won't authorize it (you can't allow writes to a slave or 
> you'll end up with consistency problems or PRIMARY KEY collisions as I 
> explained in a precedent message), should it scan the list of servers 
> until it finds the one accepting writes ?
> - in case of multiple slaves, how do you make every SQLgrey instances 
> aware of the one among them becoming the new master.

first of all i'd be happy with one master and one slave. second i don't 
know the right solution just that what i would like to see. and i talk 
about it a few other bigger site sysadm and i collect the problems. anyway
- my first assumption if one of the mx can't access to one sql server, 
then none can do it. otherwise it's a real strange thing in case we can 
accept no greylisting just dunno.
- try to use replication between sql servers.
- allow write to the slave to and when the master wake up then replicate 
back the data too.
- in my case actualy there is no master and slave just there is two sql 
server with the same database (or almost the same and there are certain 
point when they are syncing) and there is always one which is rw by all 
greylist server (first).

imho the greylist database is not so complicated. it's easy to recognize 
which records should have to replicate. only old/expired record have to 
delete and always the last updated one is the latest and all record has 
timestemp (because that's the main purpose the database) so it's easy to 
know which is the last updated.

> Here are simple questions to make sure we speak of the same things. Do 
> you agree with the following statements ?
> - one and only one sql server should accept writes from every SQLgrey 
> instances. Let's call it the RW server (read-write).

no. both, but all greylist server rw one of them at the same time.

> - all other sql servers replicate the content of the server above in a 
> timely manner (using MySQL replication process for example). Let's call 
> them the RO servers (read-only).

partly. it can be done by mysql replication or as i wrote above since 
from the database it's easy to recognize what should have to replicate 
it may can be done by the greylist server in a scheduled way (every 
minutes or 5 minutes updates the sql server which is not the current rw 
server). eg. update the current database plus the another database 
called "none-replicated", flush these data to the other server in every 
1,5,10 minutes and do a full replication in every hour. i don't know 
which is the better and/or easier.

> - when the RW server fails,
>  . either one of the slaves and *only* one switches to the RW status, 
> making sure all other RO servers are now synchronising with it (this is 
> not a simple process, I don't know how MySQL replication handles the 
> fact that RO servers will not necessarily have the same content when the 
> RW fails).
>  . either none of them switch to RW and SQLgrey now only have RO 
> databases so it cannot store new connection attempts in database : it 
> can't greylist anymore so it *must* let every message pass (-> slaves 
> won't be used at all).

see above.

> - when a failed server comes back online it must do so in a RO state if 
> there is a RW server already, if not it can become the RW server but 
> should have the most recent data available in its database when doing so 
> (or your auto-whitelisting efficiency will suffer and you can consider 
> reconnections as brand new connections).

partly. i assume there is always one sql server which accept rw we can 
call this master. and even if the previous master come back that just 
become an sql server which periodicaly update his database (through 
mysql replication or greylist service). here i always talk about sql 
servers not greylist servers. suppose there is one greylist servers on 
all mx and two sql servers somewhere. one sql server is the up-to-date 
and one is the replication. as i wrote i assume that when the sql server 
gone it's gone for everyone.

> If you don't agree with one of them, please explain why.

i hope it's help.

-- 
   Levente                               "Si vis pacem para bellum!"