pgfr_record

Overview

Package normalizes the upstream 0.0.0 control version to 2.29.2; run SELECT pgfr_record.enable() after CREATE EXTENSION. The downstream install patch defers scheduling until the CREATE transaction commits and guards optional pg_stat_statements.

Version

Build

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

pig build pkg pg_flight_recorder         # build RPM / DEB packages

Install

You can install pg_flight_recorder 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:

• Install
• pig
• dnf
• apt

pig install pg_flight_recorder;          # Install for current active PG version

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

dnf install -y pg_flight_recorder_18       # PG 18
dnf install -y pg_flight_recorder_17       # PG 17
dnf install -y pg_flight_recorder_16       # PG 16
dnf install -y pg_flight_recorder_15       # PG 15

apt install -y postgresql-18-pg-flight-recorder   # PG 18
apt install -y postgresql-17-pg-flight-recorder   # PG 17
apt install -y postgresql-16-pg-flight-recorder   # PG 16
apt install -y postgresql-15-pg-flight-recorder   # PG 15

Create Extension:

CREATE EXTENSION pgfr_record CASCADE;  -- requires: pg_cron

Usage

Sources:

• pgfr_record v2.29.2 README
• pgfr_record control file
• pg_flight_recorder v2.29.2 reference
• v2.29.2 release notes

pgfr_record is the collection half of PostgreSQL Flight Recorder. It periodically samples PostgreSQL activity, waits, locks, replication, vacuum, and related health data into bounded in-database buffers, then retains snapshots for incident analysis. Use it when short-lived database conditions must survive long enough for later diagnosis.

Install and Enable Recording

pgfr_record requires pg_cron:

CREATE EXTENSION pg_cron;
CREATE EXTENSION pgfr_record;
SELECT pgfr_record.enable();

enable() installs and schedules the collector jobs. It also reports configuration warnings; review them rather than treating a successful call as proof that every metric is being collected.

Inspect Recorder Health

SELECT * FROM pgfr_record.health_check();
SELECT * FROM pgfr_record.ring_buffer_health();
SELECT * FROM pgfr_record.list_profiles();

Use set_mode or apply_profile to select the intended collection profile before an incident:

SELECT pgfr_record.set_mode('normal');

The available collection modes are normal, light, emergency, and kill. Profile names and sampling intervals can evolve, so list the installed profiles rather than hard-coding an undocumented name.

Recorded Data Index

• deltas: interval changes for cumulative PostgreSQL counters.
• recent_activity and recent_waits: sampled sessions and wait events.
• recent_locks and recent_idle: lock and idle-session observations.
• recent_replication and recent_vacuum: replication and maintenance state.
• archiver_status: WAL archive health.
• snapshot and ring-buffer tables: retained history used by pgfr_analyze.

Many working buffers are UNLOGGED to reduce write amplification. They are not crash-durable and are not replicated like ordinary logged tables; durable snapshots provide the longer-lived analysis surface.

Administration Functions

• pgfr_record.enable(): create or activate scheduled collectors.
• pgfr_record.disable(): stop scheduled collection.
• pgfr_record.health_check(): report collector and configuration health.
• pgfr_record.set_mode(…): change collection mode.
• pgfr_record.apply_profile(…): apply a predefined profile.
• pgfr_record.list_profiles(): enumerate available profiles.
• pgfr_record.ring_buffer_health(): inspect capacity and retention pressure.
• pgfr_record.cleanup(…): remove retained history according to the API.

Retention and Overhead

The default design keeps short ring-buffer history and longer durable snapshots, commonly around 7 and 30 days depending on the installed profile. Verify actual table sizes, job schedules, and retention settings in the installed version.

The recorder creates roughly ten pg_cron jobs. pg_cron.log_run can generate thousands of rows per day; disable that logging or purge cron history when the extra audit trail is unnecessary. Sampling also adds SQL, storage, and catalog traffic, so measure overhead on the target workload.

Version 2.29.2 handles managed-service roles that cannot UPDATE cron.job: jobs can still be scheduled, while the optional nodename normalization is skipped with a warning.

Caveats

• pg_stat_statements enriches several analyses but is optional; enable and size it separately when needed.
• Collection cannot reconstruct time periods that were never sampled. Enable and validate the recorder before an incident.
• UNLOGGED buffers can be truncated after crash recovery.
• Recorder tables can contain query text, role names, client data, and operational details. Apply appropriate privileges and retention controls.