thumbtack/pgdoctor

Simple, lightweight web service to perform health checks on PostgreSQL instances

pgDoctor

Simple, lightweight, web service used to define and run custom health checks on PostgreSQL instances.

Dependencies

• Header files for libpq5 (PostgreSQL library)
• GNU libmicrohttpd
• check, a unit test framework for C

On a Debian-based system, these can easily be installed by running

sudo apt-get install libpq-dev libmicrohttpd-dev check

Build and install

• Running tests (optional):

  The default configuration parameters for running tests rely on a local PostgreSQL instance listening on port 5432. Connections will be established with the postgres user and no password.

  If this does not match the system you're using, simply edit the relevant pg_* parameters on tests/pgdoctor.cfg.

  To actually run the tests all we need to do is execute

  make check

  If the last line of the output shows something like 100%: Checks: X, Failures: 0, Errors: 0, where X is the number of checks performed, all is good.

• Building:

  make

• Installing

  sudo make install

Configuration

A default configuration file is created under /etc/pgdoctor.cfg. Each setting is preceded by a comment describing it briefly.

Runtime settings

Parameter	Description	Default
http_port	Port to listen on	8071
syslog_facility	Syslog facility (local) to messages log to	local7

Target PostgreSQL instance

Parameter	Description	Default
pg_host	Host name of the instance pgDoctor will connect to	localhost
pg_port	Port on which the target server is listening	5432
pg_user	User to connect with	postgres
pg_password	Password to use with pg_user	empty
pg_database	Name of the database to connect to	postgres

Health checks

Parameter	Description	Default
pg_connection_timeout	Timeout (seconds) when connecting to PostgreSQL	3
pg_max_replication_lag	Maximum acceptable delay (seconds) on streaming replication	-1 (disabled)

Custom health checks

pgDoctor supports the definition of custom health checks in the form of arbitrary SQL queries — one check per line.

These may be defined in two forms:

• a plain SQL query, and the health check is considered successful if and only if it is executed without any errors;
• a SQL query (must return exactly one field) and a condition, in which case the health check is considered successful when the query is executed without and errors and the condition evaluates to true.

Conditional checks are of the form

"QUERY" comparion_operator "VALUE"

where comparison_operator is one of <, >, or =. QUERY is any valid SQL command (surrounded by double-quotes), and VALUE (also surrounded by double-quotes) is the expected result from QUERY.

When using =, a string comparison is performed. For both < and > floating point values are used.

---

Simple examples — each health check passes iff the query runs without any errors

• "SELECT 1"
• "SELECT NOW()"

Conditional example — successful iff on_rotation equals 1

• "SELECT on_rotation FROM maintenance WHERE hostname = 'production-replica3'" = "1"

Contact

• pgdoctor group — general discussion list.

pgdoctor's Issues

Possible wrong information about replication lag

As I found pgdoctor using next query to determine replication lag:
"SELECT extract('epoch' from " \ "(now()-pg_last_xact_replay_timestamp())) " \ "AS replication_lag;"
This query may return incorrect value in case when there are no updates for database for some period. So if last update (delete or insert) was, for example, a hour ago this query will return value of replication_lag eq to 1 hour but it is not true! There are just no new date, so actually no replication lag.

Passwords can't contain equal signs

I had an issue with pgdoctor failing health checks because a password had "=" in it.

I submitted a PR that fixes the issue: #13

Interesting project

Marco,
This is an interesting project, that might be useful to many people.
A great improvement would be adding some documentation on hot to use pgDoctor.
Actual usage is not clear without extensive studying of source codes, which would be a brier for adoption.
Igor

Debian package