postgresql - Postgres - testing database connection in bash

Question

I wonder if there is an alternative to the psql command to test the connection to a postgresql database using bash.

I'm setting up a Core OS cluster and have a side service which should perform the equivalent of psql 'host=xxx port=xxx dbname=xxx user=xxx' every minute to determine if the service is running, and more important, if one can connect to it using the given parameters).

I cannot install postgres directly on Core OS. The command usually used in Core OS is something like curl -f ${COREOS_PUBLIC_IPV4}:%i;. But it tells only if the service itself is running on the given port, without any access check.

Thank you in advance!

question from:https://stackoverflow.com/questions/26911508/postgres-testing-database-connection-in-bash

Answer 1

pg_isready is a utility for checking the connection status of a PostgreSQL database server. The exit status specifies the result of the connection check.

It can easily be used in bash. PostgresSQL Docs - pg_isready

Example Usage:

pg_isready -d <db_name> -h <host_name> -p <port_number> -U <db_user>                      

Exit Status

pg_isready returns the following to the shell:

  0 - if the server is accepting connections normally, 
  1 - if the server is rejecting connections (for example during startup), 
  2 - if there was no response to the connection attempt, and 
  3 - if no attempt was made (for example due to invalid parameters).

Notice: man pg_isready states: It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt.