Skip to content

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

License

Notifications You must be signed in to change notification settings

thumbtack/pgdoctor

Repository files navigation

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.