pg_roast

Overview

Upstream has no release tag; package pins main commit ccbf012. Manual audits work normally; the periodic background worker requires shared_preload_libraries=pg_roast.

Version

Build

You can build the RPM / DEB packages for pg_roast using pig build:

pig build pkg pg_roast         # build RPM / DEB packages

Install

You can install pg_roast directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_roast;          # Install for current active PG version

pig ext install -y pg_roast -v 18  # PG 18
pig ext install -y pg_roast -v 17  # PG 17
pig ext install -y pg_roast -v 16  # PG 16
pig ext install -y pg_roast -v 15  # PG 15
pig ext install -y pg_roast -v 14  # PG 14

dnf install -y pg_roast_18       # PG 18
dnf install -y pg_roast_17       # PG 17
dnf install -y pg_roast_16       # PG 16
dnf install -y pg_roast_15       # PG 15
dnf install -y pg_roast_14       # PG 14

apt install -y postgresql-18-pg-roast   # PG 18
apt install -y postgresql-17-pg-roast   # PG 17
apt install -y postgresql-16-pg-roast   # PG 16
apt install -y postgresql-15-pg-roast   # PG 15
apt install -y postgresql-14-pg-roast   # PG 14

Preload:

shared_preload_libraries = 'pg_roast';

Create Extension:

CREATE EXTENSION pg_roast;

Usage

Sources:

• Upstream README
• Version 1.0 install SQL
• Background-worker implementation

pg_roast runs opinionated PostgreSQL health checks and stores findings in its fixed roast schema. It checks configuration, schema design, indexes, vacuum and bloat indicators, security posture, replication, connections, and workload signals. Version 1.0 targets PostgreSQL 14 and later.

Manual audits

Manual mode does not require preloading:

CREATE EXTENSION pg_roast;

SELECT * FROM roast.run();
SELECT severity, check_name, object_name, roast
FROM roast.latest
ORDER BY severity, check_name;

SELECT * FROM roast.summary;

Each run persists audit history and findings. Use the latest view for the newest run and the summary view for grouped results.

Scheduled audits

The optional background worker requires preload configuration and a restart:

shared_preload_libraries = 'pg_roast'
pg_roast.database = 'mydb'
pg_roast.interval = 3600

The database setting is fixed at server start. Review the upstream settings before enabling automatic audits across a production workload.

Caveats

• Findings are heuristic advice, not automatic proof of a defect. Review workload context, maintenance windows, and PostgreSQL documentation before applying any recommendation.
• Audits execute catalog and statistics queries and write history tables. Measure overhead on large catalogs and protect the roast schema from untrusted users.
• Results can expose object names, configuration, security findings, and query-related operational details. Apply least privilege and an explicit retention policy.
• Preloading is unnecessary for manual runs but mandatory for the background worker; changing startup-only settings requires a restart.