Re: [HACKERS] Determine state of cluster (HA) - Mailing list pgsql-hackers
From | Joshua D. Drake |
---|---|
Subject | Re: [HACKERS] Determine state of cluster (HA) |
Date | |
Msg-id | 2f87424f-fa59-6532-a6af-10593359a92a@commandprompt.com Whole thread Raw |
In response to | [HACKERS] Determine state of cluster (HA) ("Joshua D. Drake" <jd@commandprompt.com>) |
List | pgsql-hackers |
On 10/12/2017 05:50 PM, Joshua D. Drake wrote: > -Hackers, Bumping this. > > 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: > > Per your request, here is our failover issue. > > 1. In a modern devops environment, the database should be able to scale > and morph over time based on need. > 2. Tools that are leveraging the database should be able to easily > discover and potentially control (with permissions) the database. > Currently, you can discover the master and what nodes are syncing off of > it, but on a failure, a tool can't easily discover what orchestration > has done on the back-end to make the cluster whole again, i.e. from the > slave, you can't discover the master reliably and easily. > > The logic that our code now uses is to: > > 1. Find the master > 2. Add replication nodes per the master's configuration. > > To find a master, we start with a list of candidate nodes that MAY be a > master at any point, and: > 1. issue "SELECT pg_is_in_recovery()" to find if it is a slave > a. If so, use "SELECT pg_read_file('recovery.conf')" to extract the host > 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. > d. Use the dblink connection to ID the master node via select > inet_server_addr(); > e. connect to the IP provided by the master. > f. Repeat through nodes until we get a master. > > Issues: > 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; > 2. NAT mapping may result in us detecting IP ranges that are not > accessible to the application nodes. > 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; > 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. > 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. > > Fundamentally, the biggest issue is that it is very hard to determine > the state of the cluster by asking all the nodes, in particular in the > case of a failure. Some state information is lost that is necessary to > talk to the cluster moving forward in a reliable manner. > > -- 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
pgsql-hackers by date: