multiple / backup sql servers for sql server access

[Brendan Kearney]

presumably, you would have the same database parameters on different 
hosts, so only adding an additional "host=" parameter should suffice.  i 
would only add the additional parameters that are unique or different, 
and that should work.

connect = "host=<dbserver1> host=<dbserver2> dbname=<dbname> 
user=<dbuser> password=<dbpass>"

sorry to hear your going to test this in Prod. :(

On 8/30/22 6:10 PM, Jack Snodgrass wrote:
>
>
> I prefer to use a 2 db approach where I specify 2 different Database 
> servers as opposed to HAproxy.
>
>
> I see on: 
> https://doc.dovecot.org/configuration_manual/authentication/sql where 
> it says:
>
> *You can add multiple host parameters to the SQL connect string. 
> Dovecot will do round robin load balancing between them. If one of 
> them goes down, the others will handle the traffic.*
>
> it would be really nice if that was expanded opon in the docs.... I am 
> not sure WHO to ask that that be clarified.
>
> I currently have:
>
> connect = "host=<dbserver> dbname=<dbname> user=<dbuser> 
> password=<dbpass>"
>
> ... would I use:
>
> connect = "host=<dbserver> dbname=<dbname> user=<dbuser> 
> password=<dbpass> host=<dbserver2> dbname=<dbname2> user=<dbuser2> 
> password=<dbpass2>"
>
> and the system would just see host/host, dbname/dbname user/user 
> password/password and automatically map the correct 
> dbname/user/password to the correct host or it is assumed that the 2nd 
> host is the only thing that changes and the dbname/user/password are 
> the same? or do I use:
>
> connect = "host=<dbserver> dbname=<dbname> user=<dbuser> 
> password=<dbpass> host2=<dbserver> dbname2=<dbname> user2=<dbuser> 
> password2=<dbpass>"
>
> to specify the host and host2 info?
>
>
> "You can add multiple host parameters to the SQL connect string." is a 
> bit lacking in info when you think about the possibility of having a 
> different user/pass or dbname for the 2nd host instance.
>
> I am going to play around with this on my production box... but having 
> a bit more info in the docs would be preferred.
>
>
> - jack
>
>
>
> On 8/30/22 07:57 AM, Brendan Kearney wrote:
>>
>> per https://doc.dovecot.org/configuration_manual/authentication/sql/, 
>> you can add more than one "host=" parameter in the "connect" 
>> directive, and leave dovecot to do round-robin load balancing.  there 
>> will probably be a delay in processing while a failed database 
>> connection attempt times out.
>>
>> or you can use a load balanced database footprint, using something 
>> like haproxy.  its not as simple as putting multiple databases behind 
>> haproxy and calling it done.  i use mariadb, which via galera, can do 
>> multi-primary HA, where all 3 primary instances can take write or 
>> read events.  you need to configure each mariadb instance with galara 
>> replication and then setup haproxy to properly attach to the 
>> databases and perform a service check.
>>
>> postgresql or other databases may require different mechanisms to 
>> achieve fault tolerant HA.
>>
>> On 8/30/22 4:18 AM, Sami Ketola wrote:
>>>
>>>
>>>> On 30. Aug 2022, at 5.13, Jack Snodgrass <jack at mylinuxguy.net> wrote:
>>>>
>>>>
>>>> I am using this file:
>>>>
>>>> dovecot-sql.conf.ext
>>>>
>>>> and in there  I have a
>>>>
>>>> connect = "host=<dbserver> dbname=<dbname> user=<dbuser> 
>>>> password=<dbpass>"
>>>>
>>>>
>>>> My <dbserver> was down and I lost mail access for a few days before 
>>>> I realized that there was an issue.  How can I specify a backup 
>>>> server so that if my primary sql server goes down, a backup sql 
>>>> server will be queried?
>>>>
>>>
>>> Use haproxy.
>>>
>>> Sami
>>>
>>>
> -- 
> jack - Southlake Texas - mylinuxguy.net 
> <http://url7179.mylinuxguy.net/ls/click?upn=qw3mUGlvypxOIEjnzoVznaPhh1QZR9rdwcvCQ0qavLA-3DUiiX_5qhfSnrBXIaXI7rIewPfzICYfubUoDIKRg6-2F2vb7-2BoHPlQu-2FfbGVKyxQX-2F2MK4nxxMgskp-2B7lkCgnLNSC-2BkHg-2BIZivmGnYzgw6K5Dw5Rkff8q-2FIoZHz2vWzOlfXpUNTznP1U4-2FAL3aStk0Rg0h6GpRg4at3NFRAD3w1S5-2B-2BXy4ne0c-2FXuQOKNksIOpgzzBd5FuBUlJpA0KfWocho2TNHnA-3D-3D>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://dovecot.org/pipermail/dovecot/attachments/20220830/c17563f8/attachment.htm>