On 23rd of January, Robert Haas committed patch:
pg_isready New command-line utility to test whether a server is ready to accept connections. Phil Sorber, reviewed by Michael Paquier and Peter Eisentraut
Disclaimer: Phil is my colleague in OmniTI
In October 2012, Phil posted mail to pgsql-hackers mailing list where he attached first version of pg_ping tool.
This tool was supposed to make checking if PostgreSQL server is up – easier.
3 months, quite a lot of mails, and at least one name change later – we got it.
Usually, when one wants to check if PostgreSQL is running, she/he just connects and tries things like ‘SELECT 1'. But libpq, which (for those of you that don't know) is the library that implements communication between client and server, has a specialized function PQping(), which does it nicer – without running actual query. Just connection.
So, how to use it (pg_isready program)? –help is pretty standard:
=$ pg_isready --help pg_isready issues a connection check to a PostgreSQL database. Usage: pg_isready [OPTION]... Options: -d, --dbname=DBNAME database name -q, --quiet run quietly -V, --version output version information, then exit -?, --help show this help, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -t, --timeout=SECS seconds to wait when attempting connection, 0 disables (default: 3) -U, --username=USERNAME database username Report bugs to <email@example.com>.
Usage is pretty siple:
=$ pg_isready ; echo "return code = $?" /tmp:5920 - accepting connections return code = 0
In case when I'll try to connect to database that is down:
=$ pg_isready -p 5432 ; echo "return code = $?" /tmp:5432 - no response return code = 2
All in all – pretty simple thing, but it is nice addition that will simplify monitoring.