If I understand open source Postgres correctly, though, this setup basically requires that the application be read-only, or at least be intelligent enough not to attempt to write to a host if it has failed over to it, right? Don't you have to have human intervention to actually fail the master / primary over for write purposes? Sent with Proton Mail secure email. ------- Original Message ------- On Tuesday, February 21st, 2023 at 10:28 PM, Marcus Jodorf <bofh at killfile.de> wrote:> On 2/21/23 18:04, Lars Schotte wrote: >> > Yes, that looks nice, I am going to try that too. > >> > Because every other option is based on some other software, > > like relayd or nginx, it is all possible, but adds complexity. > >> > The best would be to have it inside connection string. >>> /etc/dovecot/dovecot-sql.conf.ext: >> ... > connect= host=host1 host=host2 dbname=<db> user=<user> password=<pwd> >>>> I'm using that since pretty much 2006 - if I'm not mistaken - on my > little servers. Simply works. >> If one server is not reachable you just get an error log entry in > mail.err that connect failed to the database that is down. > But that is all - dovecot keeps working as normal. >> BTW: Same with postfix. Simply list an additional fallback and it just > works. >> Best, >> Marcus-------------- next part -------------- A non-text attachment was scrubbed... Name: publickey - dmwhite823 at protonmail.com - 0x320CD582.asc Type: application/pgp-keys Size: 1739 bytes Desc: not available URL: <https://dovecot.org/pipermail/dovecot/attachments/20230222/d1f409fb/attachment.key> -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 509 bytes Desc: OpenPGP digital signature URL: <https://dovecot.org/pipermail/dovecot/attachments/20230222/d1f409fb/attachment.sig>
On 2/22/23 11:29, David White wrote:> If I understand open source Postgres correctly, though, this setup basically requires that the application be read-only, or at least be intelligent enough not to attempt to write to a host if it has failed over to it, right? Don't you have to have human intervention to actually fail the master / primary over for write purposes?Yes, that is correct. But in the context of using Dovecot (passdb/userdb/quota read-only is normally sufficient). For write fail-over you would typically use for example Pgpool-II. Best, Marcus
I don't even get what the advatages are of doing this with sql. If you use local replicated ldap and use local credential caching then your master ldap can go down without issues, even the local caching handle some local slapd issues. I guess the local caching is also faster. Afaik were databases not designed for this purpose and a better fit is ldap.> > If I understand open source Postgres correctly, though, this setup > basically requires that the application be read-only, or at least be > intelligent enough not to attempt to write to a host if it has failed > over to it, right? Don't you have to have human intervention to actually > fail the master / primary over for write purposes? > > Sent with Proton Mail secure email. > > ------- Original Message ------- > On Tuesday, February 21st, 2023 at 10:28 PM, Marcus Jodorf > <bofh at killfile.de> wrote: > > > > On 2/21/23 18:04, Lars Schotte wrote: > > > > > > Yes, that looks nice, I am going to try that too. > > > > > > > Because every other option is based on some other software, > > > like relayd or nginx, it is all possible, but adds complexity. > > > > > > > The best would be to have it inside connection string. > > > > > > > > /etc/dovecot/dovecot-sql.conf.ext: > > > > > ... > > connect= host=host1 host=host2 dbname=<db> user=<user> password=<pwd> > > > > > > > > > > > I'm using that since pretty much 2006 - if I'm not mistaken - on my > > little servers. Simply works. > > > > > If one server is not reachable you just get an error log entry in > > mail.err that connect failed to the database that is down. > > But that is all - dovecot keeps working as normal. > > > > > BTW: Same with postfix. Simply list an additional fallback and it just > > works. > > > > > Best, > > > > > Marcus
think about this, you have connect= host=host1 host=host2 ... when host1 fails, you must kick that host while it is down, each and every single time you want a new connection to the database. there is no record saying that box is down, no logic in the app saying i've already tried that box, so avoid it for "some period of time".? you have to attempt a connection to it, since it is listed first in your connection parameters.? you have to get an error back or wait for a timeout period to occur, and then move onto the next box. have you ever faced a DNS resolution issue, where DNS was not working and everything slowed to a crawl because of it?? similar kind of delays in processing here.? you have to have an error or timeout occur before you move onto the next configured box.? when you are dealing with high volume production environments, this is not a scaled solution. with load balancing, you have an active connection from your app to the database VIP.? then there is a secondary connection from the load balancer to the database host in the pool.? when that connection dies and goes away, the app retries the connection to VIP and is automatically assigned to a different, alive and available host in the pool.? no waiting for an error or timeout to occur, and then trying the next host in the config. when you put the infrastructure together properly, no human intervention is required to mark a box down and not send traffic to it.? as i said: it requires a bit of supporting infrastructure to get it all working, but you wind up with a */highly-available, fault tolerant PostgreSQL footprint with automatic failover./* by having the load balancer keep track of which hosts are alive and available, you dont have any guess work as to which host to connect to.? the app just has to retry the connection.? once a host is marked down by the load balancer, no traffic will be sent to it until it passes a health check. the health checking with PostgreSQL is the ETCd and Patroni pieces.? those two processes are critical in the chain.? with the health of the boxes and processes handled, knowing which PG host is the active R/W one is simple.? when failure occurs, there is an election process to promote one of the standby hosts to the active R/W node, and that is reported up to the load balancer through the Patroni -> ETCd chain.? all new connections wind up going to the newly elected active R/W node in the pool. *//* On 2/22/23 5:29 AM, David White wrote:> If I understand open source Postgres correctly, though, this setup basically requires that the application be read-only, or at least be intelligent enough not to attempt to write to a host if it has failed over to it, right? Don't you have to have human intervention to actually fail the master / primary over for write purposes? > > Sent with Proton Mail secure email. > > ------- Original Message ------- > On Tuesday, February 21st, 2023 at 10:28 PM, Marcus Jodorf<bofh at killfile.de> wrote: > > >> On 2/21/23 18:04, Lars Schotte wrote: >> >>> Yes, that looks nice, I am going to try that too. >>> >>> Because every other option is based on some other software, >>> like relayd or nginx, it is all possible, but adds complexity. >>> >>> The best would be to have it inside connection string. >> /etc/dovecot/dovecot-sql.conf.ext: >> >> ... >> connect= host=host1 host=host2 dbname=<db> user=<user> password=<pwd> >> >> I'm using that since pretty much 2006 - if I'm not mistaken - on my >> little servers. Simply works. >> >> If one server is not reachable you just get an error log entry in >> mail.err that connect failed to the database that is down. >> But that is all - dovecot keeps working as normal. >> >> BTW: Same with postfix. Simply list an additional fallback and it just >> works. >> >> Best, >> >> Marcus-------------- next part -------------- An HTML attachment was scrubbed... URL: <https://dovecot.org/pipermail/dovecot/attachments/20230222/aea7b64c/attachment.htm>