On 10/15/2017 07:39 PM, Craig Ringer wrote:
On 13 October 2017 at 08:50, Joshua D. Drake <j...@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
