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!

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>                      

Installation

sudo apt install -y postgresql-client

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.

Answer 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'

Answer 3

For a pure bash implementation, you could use:

DB_HOST=localhost DB_PORT=5432 bash -c 'printf "" 2>>/dev/null >>/dev/tcp/${DB_HOST}/${DB_PORT}'

which will return 0 if the database is ready to accept connections, otherwise 1.

Note: this will only test the database readiness, but it does not perform any authentication.

I use it inside docker-compose as an entrypoint to wait for the db to be ready before starting the service (depends_on only checks if the container is alive).

For example:

    # wait for db to accept connections, then start the process
    entrypoint: |
      bash -c '
        until printf "" 2>>/dev/null >>/dev/tcp/db-host/5432;
          do sleep 1;
        done;
        target/release/app; # execute application
      '

Answer 4

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

Answer 5

To see the connection information in PSQL Shell (psql) use \c