Skip to content

C01 — Connection & Availability

Whether the server can be reached and used at all — TCP/SSL reachability, whether the server's own readiness signals (pg_isready-style checks done from inside a session) look healthy, and whether active connections are approaching max_connections. This is the category worth checking first: a problem here can make every other category's data stale or unreachable.

6 checks, PGHF01-001 through PGHF01-006. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF01-001 — SSL/TLS enabled

Without SSL, credentials and every row of data travel the network in plaintext — trivially interceptable on any shared or untrusted network path between client and server.

How to fix

Enable SSL and stop accepting plaintext connections on any non-loopback path:

  1. Provide a certificate/key pair (a CA-signed one for production, openssl req -x509 -new for a quick test) and point ssl_cert_file/ssl_key_file at them.
  2. Turn it on and restart (ssl is PGC_SIGHUP-only from the client-negotiation side but the listener itself needs a restart the first time it's enabled):
ALTER SYSTEM SET ssl = on;

pg_ctl restart
  1. In pg_hba.conf, change the relevant host lines to hostssl (or drop plain host entries entirely) so a client can't fall back to an unencrypted connection, then reload:
SELECT pg_reload_conf();

PGHF01-002 — PostgreSQL version

The running major version is the baseline every EOL/support-window and feature-availability judgment is made against (see pghf.pg_eol_dates) — a version past end-of-life receives no further security fixes from the PostgreSQL project.

The check's recorded value is the number of whole years the running major version is past its EOL date in pghf.pg_eol_dates (lower is better): negative means still supported, 0 means past EOL by under a year, 1 means one to two years past, and so on. The default threshold (info > -1, warning > 0, critical > 1) therefore never needs a hardcoded version number or date — keeping pghf.pg_eol_dates refreshed from the PostgreSQL versioning policy is the only maintenance. A major version newer than the reference data is assumed supported and reported as -99; the actual major version, full version string, and EOL date are kept in the result's detail.

How to fix

There's no in-place fix — this is a planning signal, not a config change:

  1. Check this server's major version against pghf.pg_eol_dates (or the PostgreSQL versioning policy) to see how much runway is actually left.
  2. Plan a major-version upgrade well before EOL — pg_upgrade for a maintenance-window upgrade, or logical replication into a new-version instance for a near-zero-downtime cutover.
  3. Before upgrading, run every PGHF10-* (pg_upgrade readiness) check in this framework and resolve what it flags.

PGHF01-003 — Connection saturation

Once active connections reach max_connections, every new connection attempt is refused outright — including the application's own retries and an operator's own psql session trying to diagnose the problem.

How to fix

Raising max_connections is usually the wrong first move — each backend costs real memory whether idle or busy, and PostgreSQL's own connection handling degrades well before the limit if it's pushed too high:

  1. Put a connection pooler (PgBouncer, PgCat, or the application's own pool) in front of the database in transaction-pooling mode, so hundreds of app-level connections share a much smaller number of real backends.
  2. If genuine concurrent-backend demand justifies it, raise the limit deliberately and re-check available RAM (work_mem-heavy queries multiply per backend):
ALTER SYSTEM SET max_connections = 300;

max_connections requires a full server restart to take effect, not just a reload.

PGHF01-004 — Oldest idle-in-transaction session age

A long-lived idle-in-transaction session holds its snapshot open, blocking autovacuum from reclaiming dead tuples and holding any locks it acquired — a single forgotten transaction can quietly stall vacuum and lock cleanup cluster-wide.

How to fix

For the immediate problem, find and end the offending session:

SELECT pid, usename, state, now() - xact_start AS age, query
  FROM pg_stat_activity
 WHERE state = 'idle in transaction'
 ORDER BY age DESC;

SELECT pg_terminate_backend(<pid>);

To prevent recurrence, set a ceiling so no session can hold a transaction open indefinitely again:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Then check the application for a code path that opens a transaction without a COMMIT/ROLLBACK on every exit path, including error handling.

PGHF01-005 — Per-database connection counts

A single noisy database can quietly monopolize most of the cluster's connection budget, starving every other database sharing the same instance — visible here well before any one of them hits PGHF01-003's cluster-wide saturation alert.

How to fix

Identify the outsized database from the observed breakdown, then either fix its connection usage at the source (pooling — see PGHF01-003) or cap it explicitly so it can't starve its neighbors:

ALTER DATABASE the_noisy_db CONNECTION LIMIT 100;

Existing connections aren't affected — this only blocks new ones once the limit is hit.

PGHF01-006 — pg_hba TRUST on non-loopback

A trust rule on a non-loopback address grants passwordless access to anyone who can reach that network path — replace it with scram-sha-256 immediately; this is one of the few findings in the whole framework worth treating as an active incident, not a backlog item.

How to fix

Treat this as urgent, not routine maintenance:

  1. In pg_hba.conf, change every trust entry on a non-loopback address (anything other than 127.0.0.1/32/::1/128, or an unmapped local socket) to scram-sha-256.
  2. Make sure every affected role actually has a password set first (ALTER ROLE ... PASSWORD ...), or those connections will simply start failing.
  3. Reload:
SELECT pg_reload_conf();
  1. Confirm the change took effect against pg_hba_file_rules before closing this out — a trust rule that's merely commented out or shadowed by an earlier matching line won't show here as fixed.

Continue to C02 — pgBackRest Configuration & WAL Archiving.