Example for 'query' as an option for connection_check_type

[pmodi84]

Question -

Can someone please share an example for 'query' as an option for connection_check_type ?

[pmodi84]

Hello Everyone, Can someone please share an example for 'query' as an option for connection_check_type ?

[ibarwick]

Hi,

If connection_check_type is set to query, repmgr uses SELECT 1 as a simple throw-away query to elicit a response from the server.

I see that's not really clear from the documentation, we'll fix that.

If you really want to, you can specify your own query with the connection_check_query configuration parameter, but note that's an undocumented option intended for testing.

[parthmodi84]

Thank you so much for the update!

two more questions -

1. does connection_check_query makes new connection and execute query or does it use existing connection ?
2. how do we specify our custom query to parameter .. can we call procedure or it has to be select statement only ?

connection_check_query = 'select column1 from table1' ? connection_check_query = 'call connection_check' ?

[chvsunny]

@ibarwick Can you please help us with below questions? (Consolidating all the previous questions too)

1. From which version of repmgr, this new parameter connection_check_query is available? We tried on 5.0.0, but no luck.
2. Does connection_check_query makes new connection and execute query or does it use existing connection ? If it uses existing connection, is there a way to force a new connection for every check? We want to make sure the health of Primary by making a new connection and run custom query.
3. How does repmgrd identify the health of Priamry with connection_check_query? Is it based on output of the query or return code of the query?

Thanks, chvsunny.

[chvsunny]

@ibarwick or someone, can you please help us with questions asked before?

Thanks, chvsunny.

[ibarwick]

@ibarwick Can you please help us with below questions? (Consolidating all the previous questions too)

1. From which version of repmgr, this new parameter `connection_check_query` is available? We tried on 5.0.0, but no luck.

5.2

2. Does connection_check_query makes new connection and execute query or does it use existing connection ?

It uses the existing connection.

If it uses existing connection, is there a way to force a new connection for every check?

There is no way of doing that.

3. How does repmgrd identify the health of Priamry with `connection_check_query`? Is it based on output of the query or return code of the query?

It checks that the query succeeded within async_query_timeout seconds, but not the result.

As noted previously, connection_check_query was added for internal testing purposes; the default is SELECT 1 (which was previously hard-coded). Note that any more complex query is at risk of failure, which may cause repmgr to incorrectly interpret the result as failure of the node being checked, even though the server is otherwise available.

[chvsunny]

Thanks @ibarwick for all the answers. Few follow up questions:

If it uses existing connection, is there a way to force a new connection for every check?

There is no way of doing that.

Can we request this as an enhancement in the next version? May be New / Existing connection can be defined via parameter ?

As noted previously, connection_check_query was added for internal testing purposes; the default is SELECT 1 (which was previously hard-coded). Note that any more complex query is at risk of failure, which may cause repmgr to incorrectly interpret the result as failure of the node being checked, even though the server is otherwise available.

I understand its developed for internal testing, but is there a plan for providing this connection_check_query as a standard feature? Can we rely on this feature?

I have actually tested this, by passing different sorts of queries for connection_check_query, like syntax error, relation doesn't exist, select 2 and select 0. In all the cases, repmgrd is treating primary is up. Can you please help me to identify the appropriate failure signal? It will help us to come up with out custom query.

Here is an example config for connection_check_query on replica: connection_check_query='SELECT is 0'

from repmgrd log on replica: [2021-03-01 12:53:57] [DEBUG] connection check type is "query" [2021-03-01 12:53:58] [DEBUG] replication lag in bytes is: 0

from primary log: 2021-03-01 20:53:57 UTC [14455]: [60-1] user=repmgr,db=repmgr,app=repmgr,client=x.x.x.x ERROR: syntax error at or near "0" at character 11 2021-03-01 20:53:57 UTC [14455]: [61-1] user=repmgr,db=repmgr,app=repmgr,client=x.x.x.x STATEMENT: SELECT is 0

Thanks, chvsunny

[ibarwick]

Thanks @ibarwick for all the answers. Few follow up questions:

If it uses existing connection, is there a way to force a new connection for every check?

There is no way of doing that.

Can we request this as an enhancement in the next version? May be New / Existing connection can be defined via parameter ?

I have noted the request, but please be aware that developer time is limited.

As noted previously, connection_check_query was added for internal testing purposes; the default is SELECT 1 (which was previously hard-coded). Note that any more complex query is at risk of failure, which may cause repmgr to incorrectly interpret the result as failure of the node being checked, even though the server is otherwise available.

I understand its developed for internal testing, but is there a plan for providing this connection_check_query as a standard feature?

Not currently.

Can we rely on this feature?

I don't imagine it will disappear, as it is used for internal testing, but can't guarantee anything.

I have actually tested this, by passing different sorts of queries for connection_check_query, like syntax error, relation doesn't exist, select 2 and select 0. In all the cases, repmgrd is treating primary is up. Can you please help me to identify the appropriate failure signal? It will help us to come up with out custom query.

Here is an example config for connection_check_query on replica: connection_check_query='SELECT is 0'

from repmgrd log on replica: [2021-03-01 12:53:57] [DEBUG] connection check type is "query" [2021-03-01 12:53:58] [DEBUG] replication lag in bytes is: 0

from primary log: 2021-03-01 20:53:57 UTC [14455]: [60-1] user=repmgr,db=repmgr,app=repmgr,client=x.x.x.x ERROR: syntax error at or near "0" at character 11 2021-03-01 20:53:57 UTC [14455]: [61-1] user=repmgr,db=repmgr,app=repmgr,client=x.x.x.x STATEMENT: SELECT is 0

To clarify, the check is to verify whether the server is accepting queries; the result (or error in the case of an incorrect query, which is not something this check is designed to deal with) is ignored.

Regards

Ian Barwick