Re: [HACKERS] Determine state of cluster (HA)

On 10/15/2017 07:39 PM, Craig Ringer wrote:
> 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.

That isn't exactly true. We do know if our replication state is current 
but only from the master which is part of the problem.

> 
> 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.

I don't think we need all of that. This is more of a request to make it 
easier for those deploying HA to determine the state of Postgres.

> 
> 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.

Definitely.

>> 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?

That would be a work around definitely but I think it would be better to 
say: ALTER SYSTEM SET PROMOTE TIMEOUT '120' (Example, let's not get off 
into the weeds :P) and if the standby can't receive a ping/ack within 
120 it will promote itself.

> PostgreSQL can't do anything about this one.

Yes that's true.

>> 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.

Great.

> 
>> 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.

Yes, I will need to go back to them on this one. I think what they mean 
is that if we have a connection that is getting closed it doesn't return 
why it is closing. It just throws an error.

> 
> Can you describe expected vs actual behaviour in more detail?
> 

I will need to get back to them on this but I think the behavior would 
be to have a return value of why the connection was closed vs just 
throwing an error. Say, "RETURN 66" means someone executed 
pg_going_to_failover() vs pg_terminate_backend() which could be for 
different reasons.

Thanks for responding, I am mostly the intermediary here,

JD


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
*****     Unless otherwise stated, opinions are my own.   *****


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