Postgresql tcp check is not working

[zozo6015 (Peter)]

Hello,

I have setup in heartbeat a tcp check for postgresql but I don't seam to manage to have it working.

The config looks like the following:

- type: tcp
  schedule: '@every 5s'
  hosts: ["172.168.17.2"]
  ports: [5432]
  check.send: 'Check'
  check.receive: 'Check'
  tags: ["db-tier", "postgresql"]
  fields:
    '@environment': staging3
    region: Canada
    DC: OVH

Is there anything I do wrong in here?

[ruflin]

Can you share your version, logs and the output events you get?

[martinr_ubi (Martin R.)]

Hi,

postgresql doesn't expect to receive a payload string like "Check" over TCP and it won't answer with an answer containing "Check" either. It expect a SQL client to start chatting the right protocol for how it's configured. The only thing you can do with postgresql is check if the port is open.

I think you were just confused by the example giving here:
https://www.elastic.co/guide/en/beats/heartbeat/current/configuration-heartbeat-options.html#configuration-heartbeat-options

- type: tcp
  schedule: '@every 5s'
  hosts: ["myhost:7"]  # default TCP Echo Protocol
  check.send: "Check"
  check.receive: "Check"

Which is applicable for an application that listens on TCP port 7, will accept a string "Check" and will answer with a string "Check". (https://en.wikipedia.org/wiki/Echo_Protocol)

The whole check.send/check.receive is optional and only applicable when the monitored application can receive and answer with strings over TCP. (Simple string exchange over TCP)

postgresql is not one of those as far as I know, if you ship a string in postgresql, it's not going to answer you or at least it's not going to answer with "Check". As such, since you have check.receive: "Check", heartbeat is expecting an answer in the form of the string "Check". That's going to mean the monitor will fail since it never got the string it expected based on your config.

To check applications/software which do not answer with specific strings over TCP when you send them specific strings over TCP... simply remove check.send and check.receive from the monitor config, they are optional. This will instruct heartbeat to simply establish a TCP connection, which in an of itself is a bidirectional operation requiring that the other end participate to set it up. Once and if established successfully, heartbeat will consider the monitor a success because the TCP connection was established. It's not required to actually send something in/over it, like a string.

What I described is "checking if the port is open". Which is a basic check for a DB engine, but still somewhat useful since it will be closed if postgresql is not running at all and heartbeat will fail the monitor.

Establishing the TCP connection and then sending a string over it as second step, to then expect a specific string in return, is optional and would work with something like zookeeper:

$ echo ruok | nc 127.0.0.1 5111
imok

(Sending "ruok" to zookeeper and receiving "imok" in return, on port 5111)

One last example which is useful is when heartbeat sends nothing but expect an answer.

$ nc 10.10.10.10 22
SSH-2.0-OpenSSH_7.4

SSHd sends a string over TCP (answers) when you connect to it, without you sending anything to it first.
Can be checked with heartbeat by specifying check.receive: "SSH-2.0-OpenSSH_7.4" and not including any line with check.send, since you're not sending anything.

The doc touches those thing here:
https://www.elastic.co/guide/en/beats/heartbeat/current/configuration-heartbeat-options.html#monitor-tcp-check

Short:
Remove your check.send and check.receive and heartbeat will simply check if port 5432 is open on IP 172.168.17.2. if it is, the monitor will be UP. Keep those check config for softwares with which you can exchange strings with. (or do "nc 172.168.17.2 5432" and check if postgresql replies in a systematic way with a string, I don't have a postgresql I can test with, you could then configure heartbeat to expect what you received...)

Martin

[zozo6015 (Peter)]

Thank you for your answear. I have taken out the check/send option and indeed the postgres shows up. Also tried to use nc to see if I can get postgresql to reply with various commands but it's silent like a fish. For now I will be just using it as it is. Will see if I will have any issues in the future with it.