pg_profile

pig build pkg pg_profile;		# build deb

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

pig install pg_profile;		# install via package name, for the active PG version

pig install pg_profile -v 18;   # install for PG 18
pig install pg_profile -v 17;   # install for PG 17
pig install pg_profile -v 16;   # install for PG 16
pig install pg_profile -v 15;   # install for PG 15
pig install pg_profile -v 14;   # install for PG 14

CREATE EXTENSION pg_profile CASCADE; -- requires dblink, plpgsql

SELECT profile.take_sample();

-- Regular report between samples 1 and 2
SELECT profile.get_report(1, 2);

-- Differential report comparing two intervals
SELECT profile.get_diffreport(1, 2, 3, 4);

-- Define a remote server
SELECT profile.create_server('remote', 'host=remote_host dbname=postgres');

-- List defined servers
SELECT * FROM profile.show_servers();

-- Enable/disable a server
SELECT profile.enable_server('remote');
SELECT profile.disable_server('remote');

-- Create a baseline preserving samples 10 through 20
SELECT profile.create_baseline('incident_2024', 10, 20);

-- List baselines
SELECT * FROM profile.show_baselines();

-- Drop a baseline
SELECT profile.drop_baseline('incident_2024');

-- Set retention to 7 days for the local server
SELECT profile.set_server_max_sample_age('local', 7);

-- Show available samples
SELECT * FROM profile.show_samples();

-- Show time spent taking samples (requires pg_profile.track_sample_timings = on)
SELECT * FROM v_sample_timings;