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!

Solution 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.

Solution 2

you can write a simple connection script in your language of choice.

hopefully your Core OS system has one of perl, php, python, ruby, etc installed

here is one in python:

#!/usr/bin/python2.4
#
import psycopg2
try:
    db = psycopg2.connect("dbname='...' user='...' host='...' password='...'")
except:
    exit(1)

exit(0)

now your cmdline looks like this

python psqltest.py && echo 'OK' || echo 'FAIL'

Solution 3

You can build a simple container that extends the first (to conserve disk) to perform the check. For example:

FROM postgres

ENTRYPOINT [ "psql", "-h", "$POSTGRES_PORT_5432_TCP_ADDR",  "-p", "$POSTGRES_PORT_5432_TCP_PORT" ]

If you're using a different image than postgres, of course use that one. You can use pretty much any command line you like and still check exit codes from bash on the CoreOS host:

#!/bin/sh
if ! docker run --link postgres:postgres psql --command "select * from foo;" ; then
   # Do something
fi