failover

[fluca1978]

I've a doubt about the configuration for failover. According to the documentation https://agroal.github.io/pgagroal/failover.html, failover happens when "clients can't write to it" (a PostgreSQL instance). What does that mean? Because at first I was thinking about a server that goes in read-only mode, but than it could also be a failover because a cluster is not responding. Is such a case a "cannot write" scenario?
What is exactly that triggers the failover?

Moreover, what is selected as the new primary? I suspect the next cluster configured into the pgagroal.conf file, is that correct? Or better, is it the first not-primary host found? I mean, if I configure pgagroal with something like:

[standby]
host = 129.168.222.2
port = 5432
primary = off

[primary]
host = 192.168.222.1
port = 5432
primary = on

[clone]
host = 192.168.222.3
port = 5432
primary = off

once the primary node fails, pgagroal is selecting the standby one, right? Assuming the standby is not getting promoted, or once promoted start failing itself, is pgagroal able to select the next one, that is "clone" in the above configuration?

[jesperpedersen]

Failover happens when a write fails against the primary. You can setup pgagroal to run against a replica instance and only issue reads against it.

The replica being promoted is chosen from the list of hosts in a "first-to-last" manner - excluding the failed primary of course. So, in your case it would be primary to standby to clone.

You can use the switch-to and reset-server commands to affect the state of the hosts.

[jesperpedersen]

A failed network write. pgagroal doesn't use any disk space except for the application itself and the configuration files.

pgagroal doesn't look at the queries sent, so it is up to the user application to filter the queries at the moment. #9 will be implemented at some point.

[fluca1978]

A failed network write. pgagroal doesn't use any disk space except for the application itself and the configuration files.

I was referring to PostgreSQL going out of disk space, thus preventing any database write. But from the above I understand that is "any socket write (i.e., packet sent) that fails".

pgagroal doesn't look at the queries sent, so it is up to the user application to filter the queries at the moment. #9 will be implemented at some point.

Yes, I've read #9, but I was asking how to achieve such "user application filtering" you are referring to. I mean, how can I instrument an application to connect to a slave thru pgagroal?

[jesperpedersen]

Any "successful" error from the server will be sent back to the client to act on - e.g. no fail-over will happen. We could look into other scenarios where promotion could be in place. Any help with that would be great.

Filtering queries at the user application level is very specific to the frameworks in place. Once #9 is implemented it would be simpler to setup, but for now you have to manually select the correct pgagroal instance to connect to (one for read-write and one for read-only).

[fluca1978]

Any "successful" error from the server will be sent back to the client to act on - e.g. no fail-over will happen. We could look into other scenarios where promotion could be in place. Any help with that would be great.

Ok, now it is clear: the server must "disappear" for a failover to happen.

Filtering queries at the user application level is very specific to the frameworks in place. Once #9 is implemented it would be simpler to setup, but for now you have to manually select the correct pgagroal instance to connect to (one for read-write and one for read-only).

Sorry to bother you again on this, but assuming the configuration file above, where we have primary, standby and clone, is a single pgagroal -d able to serve all of them or are you suggesting to start different pgagroal instances with different configuration?

[jesperpedersen]

You could have pgagroal running on :2345 to serve read-write operations with a fail-over enabled - e.g. your current configuration.

Then have another instance running - on another port - to serve read-only operations with only replica servers so without fail-over.

[fluca1978]

Thanks, it is clear now: I need different configurations.