Re: [HACKERS] Determine state of cluster (HA) - Mailing list pgsql-hackers

On 13 October 2017 at 08:50, Joshua D. Drake <jd@commandprompt.com> wrote:
> -Hackers,
>
> I had a long call with a firm developing front end proxy/cache/HA for
> Postgres today. Essentially the software is a replacement for PGPool in
> entirety but also supports analytics etc... When I was asking them about
> pain points they talked about the below and I was wondering if this is a
> problem we would like to solve.

IMO: no one node knows the full state of the system, or can know it.

I'd love PostgreSQL to help users more with scaling, HA, etc. But I
think it's a big job. We'd need:

- a node topology of some kind, communicated between nodes
- heartbeat and monitoring
- failover coordination
- pooling/proxying
- STONITH/fencing
- etc.

That said, I do think it'd be very desirable for us to introduce a
greater link from a standby to master:

- Get info about master. We should finish merging recovery.conf into
postgresql.conf.

-

> b. Attempt to connect to the host directly, if not...
> c. use the slave and use the hostname via dblink to connect to the master,
> as the hostname , i.e. select * from dblink('" + connInfo + "
> dbname=postgres', 'select inet_server_addr()') AS t(inet_server_addr inet).
> This is necessary in the event the hostname used in the recovery.conf file
> is not resolvable from the outside.

OK, so "connect directly" here means from some 3rd party, the one
doing the querying of the replica.

> 1.  The dblink call doesn't have a way to specify a timeout, so we have to
> use Java futures to control how long this may take to a reasonable amount of
> time;

statement_timeout doesn't work?

If not, that sounds like a sensible, separate feature to add. Patches welcome!

> 2.  NAT mapping may result in us detecting IP ranges that are not accessible
> to the application nodes.

PostgreSQL can't do anything about this one.

> 3.  there is no easy way to monitor for state changes as they happen,
> allowing faster failovers, everything has to be polled based on events;

It'd be pretty simple to write a function that sleeps in the backend
until it's promoted. I don't know off the top of my head if we set all
proc latches when we promote, but if we don't it's probably harmless
and somewhat useful to do so.

Either way, you'd do long-polling. Call the function and let the
connection block until something interesting happens. Use TCP
keepalives to make sure you notice if it dies. Have the function
return when the state changes.

> 4.  It doesn't support cascading replication very well, although we could
> augment the logic to allow us to map the relationship between nodes.
> 5.  There is no way to connect to a db node with something akin to
> SQL-Server's "application intent" flags, to allow a connection to be
> rejected if we wish it to be a read/write connection.  This helps detect the
> state of the node directly without having to ask any further questions of
> the node, and makes it easier to "stall" during connection until a proper
> connection can be made.

That sounds desirable, and a good step toward eventually being able to
transparently re-route read/write queries from replica to master.
Which is where I'd like to land up eventually.

Again, that'd be a sensible patch to submit, quite separately to the
other topics.

> 6.  The master, on shutdown, will not actually close and disable connections
> as it shuts down, instead, it will issue an error that it is shutting down
> as it does so.

Er, yes? I don't understand what you are getting at here.

Can you describe expected vs actual behaviour in more detail?


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers