Skip to content

Putting It All Together

The steps in the earlier chapters build and add one check in isolation. Here's the whole cycle end to end — namespace, category, check, threshold, a suite mixed with built-in checks, then actually running and evaluating it. Every command and its output below was run for real against a fresh install.

1. Catalog

Register a namespace, a category, the check's function, the check itself, and a threshold:

SELECT pghf.upsert_namespace('ACME', 'Acme Corp Custom Checks');
SELECT pghf.upsert_category('ACME', '01', 'Business Metrics');

CREATE OR REPLACE FUNCTION pghf.chk_acme01_001(p_run_id uuid) RETURNS pghf.check_result
LANGUAGE sql AS $$
    SELECT ROW('COLLECTED',
                jsonb_build_object('value', (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public')),
                NULL)::pghf.check_result;
$$;

SELECT pghf.create_check(
    p_namespace_code    => 'ACME',
    p_category_code     => '01',
    p_run_number        => 1,
    p_check_name        => 'public schema table count',
    p_routine           => 'pghf.chk_acme01_001(uuid)',
    p_result_type       => 'integer',
    p_rationale         => 'a runaway table count in public can indicate a migration or ORM leaking temp tables',
    p_numeric_direction => 'lower_is_better'
);
-- create_check
-- --------------
--  ACME01-001

SELECT pghf.set_threshold('ACME01-001', p_warn_value => '50'::jsonb, p_crit_value => '200'::jsonb);

(Swap the check body for whatever actually matters to you — a query against your own tables, a call out to an external API, anything an ordinary PostgreSQL function can do. The table-count query here is just a stand-in that runs on any install with no setup, and "ORM" refers to a code library that maps application objects to database tables — see the Glossary.)

2. Suite

Mix the new check with the built-in connection/availability checks:

SELECT pghf.create_check_run('nightly', 'nightly health + business checks');
SELECT * FROM pghf.add_checks_matching('nightly', 'PGHF01-*', false);   -- bulk-add the 6 built-ins
SELECT pghf.add_check_to_run('nightly', 'ACME01-001');                  -- append the custom one

 position |  check_id  | namespace_code |               check_name
----------+------------+-----------------+----------------------------------------
        1 | PGHF01-001 | PGHF            | SSL/TLS enabled
        2 | PGHF01-002 | PGHF            | PostgreSQL version
        3 | PGHF01-003 | PGHF            | Connection saturation
        4 | PGHF01-004 | PGHF            | Oldest idle-in-transaction session age
        5 | PGHF01-005 | PGHF            | Per-database connection counts
        6 | PGHF01-006 | PGHF            | pg_hba TRUST on non-loopback
        7 | ACME01-001 | ACME            | public schema table count

3. Run it

Collect, evaluate, then read the results (or use the combined convenience call to do the first two in one step — see Using the Seed Catalog in the User Guide):

CALL pghf.execute_check_run(p_run_key => 'nightly');
CALL pghf.evaluate_run(p_run_id => '<the run_id from pghf.runs>');
SELECT * FROM pghf.get_results_sql('<run_id>');

   severity    | category_id |  check_id  |               check_name               | value |                message
---------------+-------------+------------+-----------------------------------------+-------+-----------------------------------------
 ok            | ACME01      | ACME01-001 | public schema table count              | 0     |
 not_evaluable | PGHF01      | PGHF01-001 | SSL/TLS enabled                        | false | no threshold configured for this check
 not_evaluable | PGHF01      | PGHF01-002 | PostgreSQL version                     | 18    | no threshold configured for this check
 ok            | PGHF01      | PGHF01-003 | Connection saturation                  | 1.03  |
 ok            | PGHF01      | PGHF01-004 | Oldest idle-in-transaction session age | 0     |
 not_evaluable | PGHF01      | PGHF01-005 | Per-database connection counts         |       | no threshold configured for this check
 not_evaluable | PGHF01      | PGHF01-006 | pg_hba TRUST on non-loopback           | 0     | no threshold configured for this check

The custom check evaluates exactly like a built-in one — ok, because zero tables is comfortably under its warning threshold of 50 — because nothing in the evaluation engine distinguishes a built-in check from a custom one; both are simply rows in the same catalog tables. Every connection-and-availability check here genuinely collects real data too — none of them are permanent placeholders that only ever report themselves as skipped (see the Licence book).

Maintaining it afterwards

None of this needs another visit to the SQL above:

  • Inactivating a check (see Inactivating a Check) turns it off without touching its history.
  • Inactivating your own namespace or category (see Inactivating a Namespace or Category in the User Guide) blocks further edits without disturbing anything already running.
  • The framework's own seeding function never touches your custom namespace — it only ever writes to the built-in one — so resetting the built-in catalog (see Resetting to Defaults) leaves your own checks exactly as you left them.
  • The retention function trims old history when you decide you no longer need it (see Retention and Cleanup).

Continue to Passing In Extra Input.