Postgres - testing database connection in bash

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.

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 psycopg2try:    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'

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

FROM postgresENTRYPOINT [ "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/shif ! docker run --link postgres:postgres psql --command "select * from foo;" ; then   # Do somethingfi