multiple / backup sql servers for sql server access

[Jack Snodgrass]

I prefer to use a 2 db approach where I specify 2 different Database 
servers as opposed to HAproxy.


I see on: 
http://url7179.mylinuxguy.net/ls/click?upn=qw3mUGlvypxOIEjnzoVznczKsajdyZbXY6uGLmuK5J6I8VjNhJjPybLy8wVGpS-2Bj0XbXGjmYqz5kJ-2FqxxfqOlGg-2BYYTMl6kvDX9RScub4pw-3DiZ-y_HoH8w59qZ-2Bj-2BbJehmds8moczgghS3-2F0vIoq8mKxnoqHFUA-2FKFd9jq4TgdfEmAU-2FSWpM9f7B0SoDorUDEKzJ-2BtbrkdGth-2FAHpNmCyUfmKKbdLuOXK7lTXEQtvGP23U-2BfeI9z2hSI4rH3f0FegjA105MonT9Vz2ocF5jn91eKSHNAlUBEMEF2MRK6qsEeyDGF9EioRQPztEHQ-2FDT8KEsd2LA-3D-3D 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 http://url7179.mylinuxguy.net/ls/click?upn=qw3mUGlvypxOIEjnzoVznczKsajdyZbXY6uGLmuK5J6I8VjNhJjPybLy8wVGpS-2Bj0XbXGjmYqz5kJ-2FqxxfqOlF2WopHtP1GVbQbwTMhLwO0-3DmMmY_HoH8w59qZ-2Bj-2BbJehmds8moczgghS3-2F0vIoq8mKxnoqHFUA-2FKFd9jq4TgdfEmAU-2FS1-2BTtbS0DPgVfzdC3Hcin2PyMMheNIGpmO3VNfLEq9Zc-2FC3qlZMeaXUrfXw0x-2FkBZ9vXOXM-2FfFcdFCyfeFGdCfxg6PezBl3OKSoFan8uOOxh3hQqPQZZp6nhcucUPdb9bsuS8K14ww3Pk7ngaxQmzjw-3D-3D 
> 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-3DGi6o_HoH8w59qZ-2Bj-2BbJehmds8moczgghS3-2F0vIoq8mKxnoqHFUA-2FKFd9jq4TgdfEmAU-2FSDD3QbZkLXrzchTCC0eI50V35beidH4bZ4lcPaPCmxI3dzZMZzrRyGxB6-2B37N8f75zsozULs5YejC8ih6OJHvmMYfgsm0VkExuCbTjU2Rd8bTIcdGLUkUJF3nySSClF11arEIDq6Eii2tdImvBvCtgg-3D-3D>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://dovecot.org/pipermail/dovecot/attachments/20220830/f8e8821b/attachment.htm>