fr: add support for multi-az postgres with read-only replication

[joshua-auchincloss]

Is your feature request related to a problem? Please describe.

Yes, current implementation assumes all postgres data stores have static read-write configurations (i.e. will always be read-write for the lifecycle of the temporal instance).

Describe the solution you'd like

Add support for postgres configurations such that:

• Nodes may utilize dynamic read-only/read-write (HA) postgres servers between multiple instances.
• Where write transactions are required, fall back to available read-write (master) nodes to mitigate issues with HA compatibility.
• Default queries only utilize read-only nodes (optional but bonus points if so).

Describe alternatives you've considered

No alternatives, if HA is enabled between multiple postgres servers (e.g temporal does not check pg_is_in_recovery), temporal will eventually fail upon (postgres) instance recovery / failover.

Additional context

Postgres configured such that:

• 1 master / read-write instance
• 1 or more read-only instance(s)
• Nodes may failover from read-write to read-only (and vice versa) at any point.

Happy to provide docker configurations for reproducible context for the feature request.

[gow]

Hi @joshua-auchincloss I'm evaluating the feasibility of this feature. This is a very good feature to have. But not sure about the complexity it introduces.
Could you please provide the docker setup so that I can try it out?