NAME
pgear - listen for and respond to PostgreSQL notifications.
SYNOPSIS
pgear [OPTION]... --run=CMD NAME
pgear [OPTION]... --sql=SQL NAME
DESCRIPTION
pgear is intended to simplify the use of PostgreSQL notification
events. On startup, it daemonizes, connects to a database and enters
its 'listen loop'.
On entering its listen loop, pgear issues 'LISTEN NAME'
and runs CMD or runs SQL. Then it waits for occurrences of
the notification of NAME, as produced by the PostgreSQL
NOTIFY statement. Running CMD or SQL is referred to
below as the 'response'.
On each receipt of a notification, pgear performs the response
and resumes waiting. It remains in this loop until:
-
pgear loses its database connection, see below.
A response returns a failure code. pgear logs the failure
and exits.
pgear receives a SIGINT or SIGTERM. pgear exits.
In the last case, the signal is blocked if it arrives while a response
is in progress. When the response is done, pgear exits.
Typically your CMD or SQL looks at some state in the
database to determine what (if anything) it needs to do, e.g. which
products are low on stock. pgear assumes that the response is
harmless if it has nothing to do.
If events for pgear occurred before pgear started, it
would be an unnecessary delay to do nothing until pgear receives
the next event. So pgear responds on entering its listen loop,
after it issues 'LISTEN NAME' but before it starts waiting for
events. This sequence ensures that if the PostgreSQL server sends an
event just after the initial response, pgear still handles the
event; there is no gap where an event might be lost.
pgear runs CMD with the system(3) function.
pgear runs SQL on the database connection it acquires on
startup (or reacquires later). SQL may not contain more than
one statement.
If pgear loses its database connection it periodically attempts
to reconnect. Attempts continue for a maximum of about one minute.
If all attempts fail, pgear logs the failure and exits. On an
attempt succeeding, pgear re-enters its listen loop. This
handles a brief outage such as a restart of the PostgreSQL cluster.
NOTIFICATION PAYLOAD
From 9.0 releases onwards, PostgreSQL offers the ability to supply a
string, the payload, with each notification.
If pgear is connected to a 9.0+ server, it will pass the payload
as the first and only argument to CMD, if you used the
--run option. When there is no payload value, pgear
supplies an empty string as the argument value.
If pgear is connected to a 9.0+ server and you used the
--sql option, then pgear examines your SQL for the
presence of the string "$1". If "$1" is present, pgear supplies
the payload as the parameter value, or, if there is no payload,
supplies an empty string as the parameter value. pgear will fail on
startup if you supply such SQL (e.g. --sql='select myfunc($1)')
and the server version is earlier than 9.0.
OPTIONS
Mandatory arguments to long options are mandatory for short options
too.
- -a, --attached
-
Remain attached to your terminal and log to stdout and stderr. By
default pgear daemonizes and logs to syslog(3).
- -c, --connect=NAME
-
Use the service named NAME in the PostgreSQL connection service
file. By default pgear attempts to connect to the default
database of the user running pgear.
- -p, --pidfile=PIDFILE
-
Write the process id into the file named PIDFILE and lock the
file. This provides control on the number of running instances of the
program. Use --help to see the default PIDFILE.
- -t, --timeout=N
-
On daemonizing, the parent process waits N seconds for a signal
from the child that indicates whether the child completed its setup.
Use --timeout=0 to have the parent wait indefinitely. Use
--help to see the default N. This option is ignored if
you use --attached.
- -v, --verbose
-
Set logging verbosity. The verbosity starts at zero (silence except
for warnings and errors) and increases by 1 for each verbosity option
you supply.
- --help
-
Print help.
- --version
-
Print version number.
EXAMPLES
Run in the foreground, connect to your default database, listen for
the event 'foo' and do nothing much in response:
-
pgear --attached -vvv --sql='SELECT NULL' foo
Run as a daemon, connect via the service 'fort', listen for 'speak'
and respond with a sagacious utterance in syslog:
-
PGSYSCONFDIR=~/mypgdir pgear \
-
--verbose --connect=fort \
--run='logger $(fortune -s)' speak
FILES
If you use the --connect option, you need a
pg_service.conf connection service file, typically in
/etc/postgresql or /etc/postgresql-common. The example above uses the
environment variable PGSYSCONFDIR to point to a custom
directory.
SEE ALSO
See notify(7) or the PostgreSQL documentation on the
NOTIFY statement. See the PostgreSQL documentation for details
on the connection service file (for PostgreSQL 8.4, section 30.15).
DIAGNOSTICS
The exit statuses of pgear starting in daemon mode are
-
- 0
- Success, the child process completed its setup.
- 1
- Failure, the child was unable to complete its setup.
- 2
- Unknown, the parent timed out while waiting for the child.
BUGS
Report bugs to bugs@acrasis.net.
Home page <http://acrasis.net/code/pgear>.
AUTHOR
Nick Willson