Re: Middleware Messages for FE/BE

Actually the way I have been thinking about this would be a message

"Hey replica x.x.x.x, please take over the write head role"

Which would

1. cause the replica x.x.x.x to self-promote at this point-in-WAL
1A - optionally the old write head becomes a replica (it should
mention it in message if it plans to stay around as a replica)
2. tells other replicas to switch over and start replicating from it
3. tells all connected clients to switch, if they need read-write access.

This could be even done without timeline switch, as it should
guarantee that there is no split brain writing
Of course there are (and should be) ways to still use the WALs
normally for cases where replica x.x.x.x does not exists, like PITR

And making this play nicely with Logical Decoding is another can of
worms needing to be solved, but it is a start to becoming "Cloud
Native" :)

-----
Hannu Krosing
Google Cloud - We have a long list of planned contributions and we are hiring.
Contact me if interested.

On Thu, Aug 19, 2021 at 11:36 AM Hannu Krosing <hannuk(at)google(dot)com> wrote:
>
> One more set of "standard middleware messages" clients/middleware
> could turn on could be reporting LSNs
> * various local LSNs for progress of WAL persisting
> * reporting replication state of some or all replicas
>
> -----
> Hannu Krosing
> Google Cloud - We have a long list of planned contributions and we are hiring.
> Contact me if interested.
>
> On Thu, Aug 19, 2021 at 11:33 AM Hannu Krosing <hannuk(at)google(dot)com> wrote:
> >
> > Need for this does come up quite often so I very much support this.
> >
> > In addition to keeping a registry there likely need to be some other
> > "generally agreed" rules as well, like
> > * it being safe to ignore any and all of the middleware messages (at
> > least with no degradation from the state of not having them)
> > * and maybe even a standard way to turn them on and off.
> >
> > On/Off switch could be of course done using flags for each
> > individual use case, but it would be good to agree conventions.
> >
> > Another thing to agree would be a set of standard messages, like "I am
> > overloaded, consider moving some load away" or "Planning to switch
> > over to replica x.x.x.x, please follow"
> >
> > -----
> > Hannu Krosing
> > Google Cloud - We have a long list of planned contributions and we are hiring.
> > Contact me if interested.
> >
> >
> > On Thu, Aug 19, 2021 at 10:33 AM Simon Riggs
> > <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> > >
> > > The current FE/BE protocol assumes that the client is talking to the
> > > server directly. If middleware wants to do something like load
> > > balancing, the only current option is to inspect the incoming commands
> > > and take action from that. That approach performs poorly and has
> > > proven difficult to maintain, limiting the functionality in Postgres
> > > ecosystem middleware.
> > >
> > > It would be useful to have a way to speak to middleware within a
> > > session or prior to each command. There are ways to frig this and
> > > obviously it can always be done out-of-core, but there is a clear
> > > requirement for various client tools to agree a standard way for them
> > > to send messages to middleware rather than the database server. If we
> > > get PostgreSQL Project approval for this, then authors of client and
> > > middleware tools will know how to interoperate and can begin adding
> > > features to take advantage of this, allowing the Postgres ecosystem to
> > > improve and extend its middleware.
> > >
> > > Byte1('M')
> > > Identifies the message as a middleware message. (Or perhaps use 'U'
> > > for User Message?).
> > >
> > > Int32
> > > Length of message contents in bytes, including self.
> > >
> > > Int64
> > > Routing/class identifier, allowing middleware to quickly ignore whole
> > > classes of message if not appropriate. We would run some kind of
> > > allocation scheme to ensure unique meaning, probably via the Postgres
> > > Wiki. The first 2 billion values would be reserved for allocation by
> > > the PostgreSQL Project itself, values beyond that open for external
> > > allocation.
> > >
> > > Byten
> > > The message itself, where n is the remaining bytes in the message.
> > >
> > > The message is intended for middleware only. The server always ignores
> > > these messages, with an optional debug facility that can be enabled to
> > > allow printing NOTICEs to allow testing.
> > >
> > > I will supply a patch to any agreed message format, together with a
> > > new libpq call to utilise this.
> > >
> > > In summary: the patch is easy, the point is we need agreement to allow
> > > and encourage interoperation between clients and middleware.
> > >
> > > Thoughts?
> > >
> > > --
> > > Simon Riggs http://www.EnterpriseDB.com/
> > >
> > >