I've attached a chump little utility called pg_ping(1) and the necessary update to pg_ctl(1) to see if a database is up and running or not. There are three things that I haven't completed with this that I'll try and get to the next time I have a small chunk of time available:
1) I'd hoped to add some basic profiling support so that admins could
time the amount of time it takes to have a connection be
established (I've found it to be strangely useful).
2) A -f switch that does a "full" test and actually executes "SELECT
TRUE" from the database.
3) Poke around to see if I could add support for getopt_long()
Anyway, let me throw this out that way it can get kicked around as
appropriate. Because it's so simple in its nature and because right
now pg_ctl(1) is kinda broken in its current state (setup any kind of
auth and pg_ctl hangs), it'd be slick 50 if this patch could sneak
into the 7.4 release. :)
-sc
PS, it looks like there's a bug in the backend (not sure how old my
CVS copy is) if you comment out all of you pg_hba.conf entries, you
get a garbled message on the backend:
2003-07-18 15:53:40 [15386] FATAL: No pg_hba.conf entry for host ^L, user pgsql,
database template1
^^
--
Sean Chittenden
Index: src/bin/pg_ctl/pg_ctl.sh
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/src/bin/pg_ctl/pg_ctl.sh,v
retrieving revision 1.32
diff -u -r1.32 pg_ctl.sh
--- src/bin/pg_ctl/pg_ctl.sh 20 Mar 2003 05:00:14 -0000 1.32
+++ src/bin/pg_ctl/pg_ctl.sh 18 Jul 2003 23:04:03 -0000
@@ -98,12 +98,12 @@
fi
# Check if needed programs actually exist in path
-if [ -x "$self_path/postmaster" ] && [ -x "$self_path/psql" ]; then
+if [ -x "$self_path/postmaster" ] && [ -x "$self_path/pg_ping" ]; then
PGPATH="$self_path"
-elif [ -x "$bindir/postmaster" ] && [ -x "$bindir/psql" ]; then
+elif [ -x "$bindir/postmaster" ] && [ -x "$bindir/pg_ping" ]; then
PGPATH="$bindir"
else
- echo "The programs 'postmaster' and 'psql' are needed by $CMDNAME but" 1>&2
+ echo "The programs 'postmaster' and 'pg_ping' are needed by $CMDNAME but" 1>&2
echo "were not found in the directory '$bindir'." 1>&2
echo "Check your installation." 1>&2
exit 1
@@ -358,16 +358,12 @@
fi
fi
-# FIXME: This is horribly misconceived.
-# 1) If password authentication is set up, the connection will fail.
-# 2) If a virtual host is set up, the connection may fail.
-# 3) If network traffic filters are set up tight enough, the connection
+# FIXME: This is less misconceived, but not perfect.
+# 1) If a virtual host is set up, the connection may fail.
+# 2) If network traffic filters are set up tight enough, the connection
# may fail.
-# 4) When no Unix domain sockets are available, the connection will
-# fail. (Using TCP/IP by default ain't better.)
-# 5) If the dynamic loader is not set up correctly (for this user/at
-# this time), psql will fail (to find libpq).
-# 6) If psql is misconfigured, this may fail.
+# 3) If the dynamic loader is not set up correctly (for this user/at
+# this time), pg_ping will fail (to find libpq).
# Attempt to use the right port
# Use PGPORT if set, otherwise look in the configuration file
@@ -384,7 +380,13 @@
$silence_echo $ECHO_N "waiting for postmaster to start..."$ECHO_C
while :
do
- if "$PGPATH/psql" -p $PGPORT -l >/dev/null 2>&1
+ if [ -z "$PGPORT" ];then
+ PGPORT_OPT=""
+ else
+ PGPORT_OPT="-p $PGPORT"
+ fi
+
+ if "$PGPATH/pg_ping" -q $PGPORT_OPT
then
break;
else
Index: src/bin/Makefile
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/src/bin/Makefile,v
retrieving revision 1.39
diff -u -r1.39 Makefile
--- src/bin/Makefile 3 Sep 2002 21:45:43 -0000 1.39
+++ src/bin/Makefile 18 Jul 2003 19:56:09 -0000
@@ -15,7 +15,7 @@
DIRS := initdb initlocation ipcclean pg_ctl pg_dump pg_id \
psql scripts pg_config pg_controldata pg_resetxlog \
- pg_encoding
+ pg_encoding pg_ping
ifeq ($(with_tcl), yes)
DIRS += pgtclsh
Index: doc/src/sgml/ref/allfiles.sgml
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/doc/src/sgml/ref/allfiles.sgml,v
retrieving revision 1.54
diff -u -r1.54 allfiles.sgml
--- doc/src/sgml/ref/allfiles.sgml 27 Jun 2003 14:45:25 -0000 1.54
+++ doc/src/sgml/ref/allfiles.sgml 18 Jul 2003 23:48:20 -0000
@@ -116,6 +116,7 @@
<!entity pgCtl system "pg_ctl-ref.sgml">
<!entity pgDump system "pg_dump.sgml">
<!entity pgDumpall system "pg_dumpall.sgml">
+<!entity pgPing system "pg_ping-ref.sgml">
<!entity pgResetxlog system "pg_resetxlog.sgml">
<!entity pgRestore system "pg_restore.sgml">
<!entity pgTclSh system "pgtclsh.sgml">
Index: doc/src/sgml/ref/pg_ping-ref.sgml
===================================================================
RCS file: doc/src/sgml/ref/pg_ping-ref.sgml
diff -N doc/src/sgml/ref/pg_ping-ref.sgml
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ doc/src/sgml/ref/pg_ping-ref.sgml 18 Jul 2003 23:42:20 -0000
@@ -0,0 +1,198 @@
+<!--
+$Header$
+PostgreSQL documentation
+-->
+
+<refentry id="APP-PGPING">
+ <refmeta>
+ <refentrytitle
id="APP-PGPING-TITLE"><application>pg_ping</application></refentrytitle>
+ <manvolnum>1</manvolnum>
+ <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname id="pgping">pg_ping</refname>
+ <refpurpose>Test to see if a <productname>PostgreSQL</productname> database is up
or down</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+ <cmdsynopsis>
+ <command>pg_ping</command>
+ <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
+ <arg>-d <replaceable>dbname</replaceable></arg>
+ <arg>-h <replaceable>host</replaceable></arg>
+ <arg>-p <replaceable>port</replaceable></arg>
+ <arg>-q</arg>
+ <arg>-w <replaceable>connection timeout</replaceable></arg>
+ <arg>-v</arg>
+ </cmdsynopsis>
+ </refsynopsisdiv>
+
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <application>pg_ping</application> is a utility for determining
+ whether or not a specified <productname>PostgreSQL</productname>
+ database is up and running or down. If up,
+ <application>pg_ping</application> exits with a 0 return status: if
+ down, it exists with an exit code of 1.
+ </para>
+
+ <para>
+ <application>pg_ping</application> uses
+ <application>libpq</application> and can be used to test whether
+ any local or remote <application>PostgreSQL</application> database
+ is up or down. Any files, environment variables, or settings that
+ are applicable to <application>libpq</application> apply to
+ <application>pg_ping</application>.
+ </para>
+ </refsect1>
+
+
+ <refsect1>
+ <title>Options</title>
+
+ <para>
+ <application>pg_ping</application> accepts the following command-line arguments:
+
+ <variablelist>
+ <varlistentry>
+ <term><option>-d</option> <replaceable
class="parameter">dbname</replaceable></term>
+ <listitem>
+ <para>
+ Specifies the database name to connect to.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-h</option> <replaceable
class="parameter">host</replaceable></term>
+ <listitem>
+ <para>
+ Specifies the host to connect to.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-p</option> <replaceable
class="parameter">port</replaceable></term>
+ <listitem>
+ <para>
+ Specifies the port to connect to.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-q</option></term>
+ <listitem>
+ <para>
+ Decreases the verbosity of <application>pg_ping</application>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-w</option> <replaceable
class="parameter">connection_timeout</replaceable></term>
+ <listitem>
+ <para>
+ Specifies the connection timeout.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-v</option></term>
+ <listitem>
+ <para>
+ Increases the verbosity of <application>pg_ping</application>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+ </refsect1>
+
+
+ <refsect1>
+ <title>Diagnostics</title>
+
+ <para>
+ The output of <application>pg_ping</application> is dependent on
+ the verbosity, however, by default,
+ <application>pg_ping</application> will emmit the following
+ output:
+ <variablelist>
+ <varlistentry>
+ <term><computeroutput>UP</computeroutput></term>
+ <listitem>
+ <para>
+ The database is up and running and able to accept connections.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><computeroutput>DOWN</computeroutput></term>
+ <listitem>
+ <para>
+ The database is down and unavailable for one reason or another.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+ </refsect1>
+
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To see if the database on <literal>db.example.com</literal> is up:
+<screen>
+<prompt>$ </prompt><userinput>pg_ping -h db.example.com</userinput>
+</screen>
+ </para>
+
+ <para>
+ To use <application>pg_ping</application> in a script and
+ silently, use the <literal>-q</literal> option to rely on the exit
+ code:
+<screen>
+<prompt>$ </prompt><userinput>pg_ping -q -h db.example.com</userinput>
+</screen>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="app-pg-ctl" endterm="app-pg-ctl-title"></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
Index: doc/src/sgml/ref/pg_ctl-ref.sgml
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/doc/src/sgml/ref/pg_ctl-ref.sgml,v
retrieving revision 1.22
diff -u -r1.22 pg_ctl-ref.sgml
--- doc/src/sgml/ref/pg_ctl-ref.sgml 25 Mar 2003 16:15:42 -0000 1.22
+++ doc/src/sgml/ref/pg_ctl-ref.sgml 18 Jul 2003 23:45:59 -0000
@@ -214,9 +214,9 @@
Wait for the start or shutdown to complete. Times out after
60 seconds. This is the default for shutdowns. A successful
shutdown is indicated by removal of the <acronym>PID</acronym>
- file. For starting up, a successful <command>psql -l</command>
+ file. For starting up, a successful <application>pg_ping</application>
indicates success. <command>pg_ctl</command> will attempt to
- use the proper port for psql. If the environment variable
+ use the proper port for <application>pg_ping</application>. If the
environment variable
PGPORT exists, that is used. Otherwise, it will see if a port
has been set in the <filename>postgresql.conf</filename> file.
If neither of those is used, it will use the default port that
@@ -259,7 +259,7 @@
<listitem>
<para>
- Default port for <xref linkend="app-psql"> (used by the -w option).
+ Default port for <xref linkend="app-pgping"> (used by the -w option).
</para>
</listitem>
</varlistentry>
@@ -321,7 +321,7 @@
<listitem>
<para>
This file, located in the data directory, is parsed to find the
- proper port to use with <application>psql</application> when the
+ proper port to use with <application>pg_ping</application> when the
<option>-w</option> is given in <option>start</option> mode.
</para>
</listitem>
Index: doc/src/sgml/release.sgml
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/doc/src/sgml/release.sgml,v
retrieving revision 1.198
diff -u -r1.198 release.sgml
--- doc/src/sgml/release.sgml 17 Jul 2003 00:55:36 -0000 1.198
+++ doc/src/sgml/release.sgml 18 Jul 2003 23:50:49 -0000
@@ -55,6 +55,7 @@
Long options for psql and pg_dump are now available on all platforms
Read-only transactions
Object owners can allow grantees to grant the privilege to others (grant option)
+Add "pg_ping" utility to fix pg_ctl -w when an authentication scheme is in use
]]></literallayout>
</sect1>
Index: src/bin/pg_ping/Makefile
===================================================================
RCS file: src/bin/pg_ping/Makefile
diff -N src/bin/pg_ping/Makefile
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ src/bin/pg_ping/Makefile 18 Jul 2003 22:43:52 -0000
@@ -0,0 +1,38 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/bin/pg_ping
+#
+# Copyright (C) 2000 by PostgreSQL Global Development Team
+#
+# $Header$
+#
+#-------------------------------------------------------------------------
+
+subdir = src/bin/pg_ping
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+CFLAGS += -I../../../src/interfaces/libpq
+
+all: pg_ping
+
+pg_ping: pg_ping.o
+ $(CC) $(CFLAGS) -I../../../src/interfaces/libpq $(LDFLAGS) $(libpq) $^ $(LIBS)
-o $@
+
+install: all installdirs
+ $(INSTALL_PROGRAM) pg_ping$(X) $(DESTDIR)$(bindir)/pg_ping$(X)
+
+installdirs:
+ $(mkinstalldirs) $(DESTDIR)$(bindir)
+
+uninstall:
+ rm -f $(DESTDIR)$(bindir)/pg_ping$(X)
+
+depend dep:
+ $(CC) -MM $(CFLAGS) *.c >depend
+
+clean distclean maintainer-clean:
+ rm -f pg_ping$(X) pg_ping.o
+
+ifeq (depend,$(wildcard depend))
+include depend
+endif
Index: src/bin/pg_ping/pg_ping.c
===================================================================
RCS file: src/bin/pg_ping/pg_ping.c
diff -N src/bin/pg_ping/pg_ping.c
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ src/bin/pg_ping/pg_ping.c 18 Jul 2003 23:32:08 -0000
@@ -0,0 +1,221 @@
+/*
+ * pg_ping.c
+ *
+ * A very basic "ping" utility to see if PostgreSQL is alive and responding to
+ * requests. This doesn't do anything as fancy as perform an SQL query, but it
+ * is intended to provide basic testing functionality to see if a database is
+ * up or down (primarily in startup/shutdown scripts).
+ *
+ * Copyright (C) 2003 by PostgreSQL Global Development Group
+ *
+ * $Header$
+ */
+#ifdef HAVE_GETOPT_H
+#include <getopt.h>
+#endif
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <unistd.h>
+#include <sys/types.h>
+
+#include "libpq-fe.h"
+#include "postgres_fe.h"
+
+void usage(char *);
+
+
+int
+main(int argc, char *argv[])
+{
+ int c,
+ ret = 0,
+ connstate = -1,
+ verbosity = 1;
+ char *conninfo = NULL,
+ *dbname = NULL,
+ *host = NULL,
+ *port = NULL,
+ *statestr = NULL,
+ *timeout = NULL;
+ PGconn *conn;
+
+ extern int optind;
+ extern char *optarg;
+
+ while ((c = getopt(argc, argv, "d:h:qp:vw:")) != -1)
+ {
+ switch (c)
+ {
+ case 'd':
+ asprintf(&dbname, "dbname='%s'", optarg);
+ break;
+ case 'h':
+ asprintf(&host, "host='%s'", optarg);
+ break;
+ case 'q':
+ verbosity--;
+ break;
+ case 'p':
+ asprintf(&port, "port='%s'", optarg);
+ break;
+ case 'u':
+ /* It'd be slick if there was a global
+ * PGSQL_WWW_DOCS global that way
+ * developers could embed URLs in help
+ * messages and the URLs would be
+ * updated every release, such as:
+ *
+ * printf("%s/%s", PGSQL_WWW_DOCS, "libpq-files.html");
+ */
+ fprintf(stderr, "Username not supported by %s. Use a
~/.pgpass file instead\n", argv[0]);
+ exit(1);
+ case 'v':
+ verbosity++;
+ break;
+ case 'w':
+ asprintf(&timeout, "connect_timeout='%s'", optarg);
+ break;
+ default:
+ usage(argv[0]);
+ }
+ }
+
+ if (argc - optind != 0)
+ usage(argv[0]);
+
+ /* Not having a PQconnect struct and having to work with
+ * strings is the sucks. */
+ if (dbname != NULL)
+ asprintf(&conninfo, "%s", dbname);
+
+ if (host != NULL) {
+ if (conninfo == NULL)
+ asprintf(&conninfo, "%s", host);
+ else
+ asprintf(&conninfo, "%s;%s", strdup(conninfo),host);
+ }
+
+ if (port != NULL) {
+ if (conninfo == NULL)
+ asprintf(&conninfo, "%s", port);
+ else
+ asprintf(&conninfo, "%s;%s", strdup(conninfo),port);
+ }
+
+ if (timeout != NULL) {
+ if (conninfo == NULL)
+ asprintf(&conninfo, "%s", timeout);
+ else
+ asprintf(&conninfo, "%s;%s", strdup(conninfo),timeout);
+ }
+
+ if (conninfo == NULL)
+ conninfo = "";
+
+ if (verbosity >= 3)
+ printf("Connect string: %s\n", conninfo);
+
+ conn = PQconnectStart(conninfo);
+ do {
+ statestr = NULL;
+ switch(PQstatus(conn)) {
+ case CONNECTION_OK:
+ if (connstate != 0) {
+ statestr = "Database up and running.";
+ connstate = 0;
+ }
+ ret = 0;
+ break;
+ case CONNECTION_BAD:
+ if (connstate != 1) {
+ statestr = "Unable to make connection.";
+ connstate = 1;
+ }
+ ret = 1;
+ break;
+ case CONNECTION_STARTED:
+ if (connstate != 2) {
+ statestr = "Waiting for connection to be
made.";
+ connstate = 2;
+ }
+ ret = -1;
+ break;
+ case CONNECTION_MADE:
+ if (connstate != 3) {
+ statestr = "Connection OK; waiting to send.";
+ connstate = 3;
+ }
+ ret = 0;
+ break;
+ case CONNECTION_AWAITING_RESPONSE:
+ if (connstate != 4) {
+ statestr = "Waiting for a response from the
postmaster.";
+ connstate = 4;
+ }
+ ret = -1;
+ break;
+ case CONNECTION_AUTH_OK:
+ if (connstate != 5) {
+ statestr = "Received authentication; waiting
for backend startup.";
+ connstate = 5;
+ }
+ ret = -1;
+ break;
+ case CONNECTION_SETENV:
+ if (connstate != 6) {
+ statestr = "Negotiating environment.";
+ connstate = 6;
+ }
+ ret = -1;
+ break;
+ case CONNECTION_SSL_STARTUP:
+ if (connstate != 7) {
+ statestr = "Negotiating SSL.";
+ connstate = 7;
+ }
+ ret = -1;
+ break;
+ case CONNECTION_NEEDED:
+ if (connstate != 8) {
+ statestr = "Internal state: connect() needed.";
+ connstate = 8;
+ }
+ ret = -1;
+ break;
+ default:
+ fprintf(stderr, "Unknown status code, aborting\n");
+ abort();
+ }
+
+ if (verbosity >= 3)
+ printf("%s\n", statestr);
+ } while (ret == -1);
+
+ PQfinish(conn);
+
+ if (verbosity >= 1) {
+ if (verbosity >= 2)
+ printf("Database ");
+
+ if (ret == 0)
+ printf("UP");
+ else
+ printf("DOWN");
+
+ if (verbosity >= 2)
+ printf(": %s\n", statestr);
+ else
+ printf("\n");
+ }
+
+ return(ret);
+}
+
+
+void
+usage(char *progname)
+{
+ fprintf(stderr, "Usage: %s [-q] [-v] [-d dbname] [-h hostname] [-p port] [-w
timeout]\n", progname);
+ exit(1);
+}
pgp00000.pgp
Description: PGP signature
