Using the Seed Catalog¶

At the end of installation, pg_health_framework populates its entire built-in catalog in one automatic call to pghf.seed_data(). Most sites never register a namespace, category, or check of their own — running and evaluating what's already there is the whole job. This chapter covers exactly what's in the box, and how to run it end to end, using nothing but what's seeded. Creating Your Own Checks and Thresholds covers extending it, for sites that need to.

You can re-run the seeding function yourself at any time — it's perfectly safe, and it's how a future version of the framework delivers fixes and new checks (see Resetting to Defaults):

SELECT pghf.seed_data();
-- NOTICE:  seed_data(): skipping suite default_no_spock — spock extension is not installed on this node, so it
--          would be redundant with 'default' (whose category-12 checks already SKIP cleanly). Install spock and
--          call pghf.seed_data() again to create this suite.
-- NOTICE:  pghf.seed_data(): namespace, 15 categories, 165 checks, 1 suites, 41 thresholds upserted.

That first message — and getting one suite instead of two — only appears when the Spock extension isn't installed on your server, which is the common case for a plain PostgreSQL install. Spock is pgEdge's extension for multi-master replication; see Suites below for what changes if you have it.

What's seeded¶

1 namespace — PGHF, "PostgreSQL Health Framework."
15 categories underneath it — see the table below.
165 checks — one PostgreSQL function each, covering everything from basic reachability to Spock replication lag. See Metadata Columns Explained for what's recorded about each one.
1 or 2 suites — default always, plus default_no_spock only when the Spock extension is installed (see Suites below). Both are built as self-maintaining queries over the check catalog rather than a hand-maintained list, so a check added to a non-excluded category later is picked up automatically the next time you re-seed, with no file to edit.
41 default thresholds — carried over from pgEdge's own upstream pg-healthcheck project's tuned defaults; see the Licence book for attribution.

Categories¶

Every check's ID is built from PGHF plus its category code plus a zero-padded run number — for example, PGHF01-006.

Category code	Category	Checks
01	Connection & Availability	6
02	pgBackRest Configuration & WAL Archiving	2
03	Performance	17
04	Long-Running Queries & Lock Contention	11
05	Vacuum & Wraparound	15
06	Index Health	10
07	TOAST	8
08	Visibility Map Integrity	6
09	WAL & Replication Slots	15
10	pg_upgrade Readiness (opt-in)	15
11	Security Posture	11
12	pgEdge / Spock Cluster	25
13	OS & Resource-Level Checks	8
14	WAL Growth	13
15	Replication Health	3

165 checks in total. ("WAL" is PostgreSQL's write-ahead log, its own internal record of every change made to the database — see the Glossary if you'd like the full explanation. "TOAST" is PostgreSQL's mechanism for storing very large field values out of line from the main table row.)

Suites¶

Suite	Seeded when?	Contents
`default`	Always	Every non-retired check except category 10 (pg_upgrade readiness, which needs a target version number and is opt-in only). Includes the Spock-cluster checks (category 12) — they cleanly report themselves as skipped on a server without Spock.
`default_no_spock`	Only if the Spock extension is installed on this server	Same as `default`, but with category 12 left out entirely, for sites that want Spock checks structurally absent from the plan rather than merely reporting as skipped.

The seeding function decides whether to create default_no_spock by checking, at the moment it runs, whether Spock is actually installed — on a server that was never going to run Spock checks in the first place, a whole second suite whose only purpose is excluding them adds nothing that default doesn't already give you for free (its category-12 checks simply report as skipped). If you install Spock later, running the seeding function again creates default_no_spock at that point. Installing Spock is never required to use this framework.

For a pg_upgrade-readiness pass, create a dedicated suite (or add the category-10 checks to one yourself) and pass a target PostgreSQL version:

CALL pghf.execute_check_run(p_run_key => 'my_upgrade_check', p_target_version => 17);

Running it end to end¶

Collect, evaluate, then read the results. Every command and its output below was run for real against a fresh install, with no custom checks registered.

CALL pghf.execute_check_run(p_run_key => 'default');
SELECT run_id FROM pghf.runs ORDER BY started_at DESC LIMIT 1;
-- 688f424c-737a-40d9-92fb-26ec2dbaf94c

("run_id" is a UUID — a long, randomly generated identifier chosen so that two different ones will, for all practical purposes, never clash. See the Glossary.)

150 checks ran (165 minus the 15 opt-in category-10 upgrade-readiness checks), landing:

  status   | count
-----------+-------
 COLLECTED |   116
 SKIPPED   |    34

The 34 skipped checks are entirely expected on a fresh, non-Spock, non-SSL, no-pgBackRest single-node install — every Spock check reports itself as skipped with no Spock extension installed, every check that depends on some other extension does the same, and so on. A skipped result is the check correctly reporting "this doesn't apply here" — not a failure. (This framework also simply doesn't register checks that could never collect real data from inside a SQL session at all, in any configuration — client-side network probes, reads of the host's own filesystem, calls out to an external command-line tool. See the Licence book for exactly where these were removed and why.)

Now judge the collected data against the seeded thresholds:

CALL pghf.evaluate_run(p_run_id => '688f424c-737a-40d9-92fb-26ec2dbaf94c');
SELECT severity, count(*) FROM pghf.get_results_sql('688f424c-737a-40d9-92fb-26ec2dbaf94c') GROUP BY severity ORDER BY count(*) DESC;

   severity    | count
---------------+-------
 not_evaluable |    89
 unknown       |    34
 ok            |    26
 critical      |     1

unknown tracks the 34 skipped checks one-for-one — a real collection gap, never silently read as ok. not_evaluable is the 89 checks with no default threshold seeded (many of these report a structured breakdown with no single number to threshold; others are purely informational). Only the 27 checks with a seeded threshold get a real verdict — 26 ok, and one genuine finding:

SELECT * FROM pghf.get_results_sql('688f424c-737a-40d9-92fb-26ec2dbaf94c', p_min_severity => 'warning');

 severity | category_id |  check_id  |                  check_name                   | value  |                  message
----------+-------------+------------+-----------------------------------------------+--------+--------------------------------------------
 critical | PGHF11      | PGHF11-009 | Superuser login roles as % of all login roles | 100.00 | value 100.00 exceeds critical threshold 50

A real, useful result: on this fresh test cluster, postgres is the only login role, so 100% of login roles are superusers — exactly the kind of thing this framework exists to catch. p_min_severity => 'warning' is what an operator would actually use day to day: it works like a logging level, showing the tier you name and everything more severe, so everything below warning — noise on a healthy system — is left out. This is the query that belongs in a runbook or a notification job.

That's the entire lifecycle using nothing beyond what automatic seeding already provided — no namespace, category, check, or threshold was registered by hand. See How Threshold Evaluation Works for the full severity scale, tuning these defaults, and debounce (requiring more than one bad result in a row before reporting a breach); see Scheduling Health Checks for running this on a recurring basis.

One call instead of two: pghf.run_and_evaluate() runs execute_check_run() and evaluate_run() together, in a single call, with the same parameters as execute_check_run():

CALL pghf.run_and_evaluate(p_run_key => 'default');
-- p_run_id
-- --------------------------------------
-- 93b2158b-0dba-4331-9c92-603aca14d9c5

This produces identical results to the two separate calls above — the same 150 checks collected and evaluated, the same single critical finding. Reading the results back out is still a separate step either way. This must be called as a standalone CALL statement on its own — not from inside a function, and not inside an explicit transaction block — because it internally commits twice, once for each stage it wraps.

Continue to Metadata Columns Explained.