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!

Megaera

I cannot install postgres directly on Core OS: so what can you install? – Gare

Docker containers... so basically the options seem 1. do the job in bash or 2. run a container (which is quite suboptimal for checking the availability of other units) – Megaera

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.

Tamarra

This should be the accepted answer in my view @Megaera – Tricornered

@Showiness an example : pg_isready -d dbname -h localhost -p 5432 -U postgres – Grunter

@Grunter The example was already added to the answer on Apr 23, but thank you anyway. – Showiness

He says: I cannot install postgres directly on Core OS. So, he cant use any postgres utility. – Brough

@NamGVU it's a bad answer not answering the question at all – Toomay

Who cares if it doesn't answer the original question while it answer many others people question that are landing here, like me ;-) At least it answers the question in title. – Myotonia

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'

Linette

Thank you David. Unfortunately, none of these language is installed on CoreOs (which is readonly...) – Megaera

This does not answer the question of how to test from BASH. – Vocal

Agree with Steven, secondly i don't have psycopg2 in my docker container – Iraqi

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
      '

Vulcanize

This command check if port $DB_PORT is open on $DB_HOST and if something is listening on this port. This can not validate that a database is running and that we can connect to it. – Sirdar

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

Quizmaster

Answer 5

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

Confiture

postgres=# \c conninfo FATAL: database "conninfo" does not exist – Soble

@RafisGaneev , just \c without conninfo – Nikko

I guess conninfo was just a name of sample database. – Stephenstephenie

@saulius2. Good time to edit my post. – Confiture