Database configuration plays a crucial role in the performance, behavior, and stability of a PostgreSQL server. However, tracking configuration changes over time is not always straightforward, especially when multiple users or automated tools modify settings. The pg_track_settings extension solves this problem by providing detailed auditing and historical tracking of PostgreSQL configuration parameters.
This blog explains the purpose of the pg_track_settings extension, how to install it from source or as a regular PostgreSQL extension, and how to use its key functions with practical examples. All examples included below were executed on PostgreSQL 18, but the concepts apply to supported earlier versions as well.
Introduction to the pg_track_settings Extension
pg_track_settings is a PostgreSQL extension designed to keep a record of configuration changes. It maintains a history of settings modified at the system, database, and role levels. You can view previous values, compare configuration snapshots, and track changes made by ALTER SYSTEM, ALTER ROLE, ALTER DATABASE, or direct parameter updates in the configuration file.
The extension is extremely useful for DBAs who want to:
- Audit who changed what and when
- Compare the configuration before and after a deployment.
- Track changes across role-level and database-level parameters
- Capture system restart events.
- Maintain configuration history even after server restarts
Installing the pg_track_settings Extension
You can install this extension in two ways:
- Install from the source code
- Install normally using CREATE EXTENSION
Both methods are outlined below.
Installing pg_track_settings from Source Code
If you prefer working directly with the extension source code, follow these steps:
git clone https://github.com/rjuju/pg_track_settings.git
cd pg_track_settings
make
sudo make install
This builds the extension and installs it into your PostgreSQL extension directory.
Standard Installation Using SQL
After installation, enable the extension inside your database:
CREATE EXTENSION pg_track_settings;
Verify the installation:
SELECT *
FROM pg_available_extensions
WHERE name = 'pg_track_settings';
Expected output:
name | default_version | installed_version | comment
-------------------+-----------------+-------------------+------------------------
pg_track_settings | 2.1.2 | 2.1.2 | Track settings changes
To explore the objects that belong to the extension:
\dx+ pg_track_settings
You get results like this
Objects in extension "pg_track_settings"
Object description
----------------------------------------------------------------------------------------------------
function pg_track_db_role_settings_diff(timestamp with time zone,timestamp with time zone,integer)
function pg_track_db_role_settings_log(text,integer)
function pg_track_db_role_settings(timestamp with time zone,integer)
function pg_track_reboot_log(integer)
function pg_track_settings_diff(timestamp with time zone,timestamp with time zone,integer)
function pg_track_settings_log(text,integer)
function pg_track_settings_rds_src(integer)
function pg_track_settings_reboot_src(integer)
function pg_track_settings_reset(integer)
function pg_track_settings_settings_src(integer)
function pg_track_settings_snapshot()
function pg_track_settings_snapshot_rds(integer)
function pg_track_settings_snapshot_reboot(integer)
function pg_track_settings_snapshot_settings(integer)
function pg_track_settings(timestamp with time zone,integer)
Key Functions Provided by pg_track_settings
The extension provides several functions to help you track configuration changes. Below is a complete explanation with examples.
Tracking Role-Level Setting Changes
pg_track_db_role_settings_diff
This function compares role-level settings between two timestamps.
Example:
ALTER ROLE marc_demo SET work_mem = '64MB';
SELECT pg_reload_conf();
Select * from pg_track_settings_snapshot();
In PostgreSQL, it allows setting GUC (Grand Unified Configuration) parameters per role.
SELECT *
FROM pg_track_db_role_settings_diff(
now() - interval '5 minutes',
now(),
0
);
Sample output:
setdatabase | setrole | name | from_setting | from_exists | to_setting | to_exists
-------------+---------+----------+--------------+-------------+------------+-----------
0 | 10 | work_mem | | f | 64MB | t
This shows that work_mem was updated for that specific role.
pg_track_db_role_settings_log
Returns the log history for a specific role-level parameter.
Example:
SELECT * FROM pg_track_db_role_settings_log('work_mem');Output:
ts | setdatabase | setrole | name | setting_exists | setting
----------------------------------+-------------+---------+----------+----------------+---------
2025-11-23 13:17:17.283084+05:30 | 0 | 10 | work_mem | t | 32MB
2025-11-23 13:18:52.245579+05:30 | 0 | 2057953 | work_mem | t | 64MB
This shows when work_mem changed and what value it was assigned.
pg_track_db_role_settings
Shows the current settings stored for all roles.
SELECT * FROM pg_track_db_role_settings();
Output:
setdatabase | setrole | name | setting
-------------+---------+----------------------+---------
0 | 10 | maintenance_work_mem | 16MB
0 | 10 | work_mem | 32MB
0 | 2057953 | work_mem | 64MB
Tracking System Restart Events
pg_track_reboot_log
This records server restarts after snapshots.
SELECT * FROM pg_track_reboot_log();
Output:
ts
----------------------------------
2025-11-23 12:55:02.930824+05:30
Resetting Tracked Data
pg_track_settings_reset
Resets all tracking history.
SELECT * FROM pg_track_settings_reset();
This clears logs so you can start fresh.
Taking Snapshots of Current Settings
pg_track_settings_snapshot
Captures a snapshot of current PostgreSQL settings.
SELECT * FROM pg_track_settings_snapshot();
Output:
pg_track_settings_snapshot
----------------------------
t
This snapshot is used for future comparisons.
Comparing System-Wide Setting Changes
pg_track_settings_diff
This function compares all settings between two timestamps.
Example:
SELECT *
FROM pg_track_settings_diff(
now() - interval '5 minutes',
now(),
0
);
Sample partial output:
name | from_setting | from_exists | to_setting | to_exists
---------------------------------------------+--------------+-------------+------------+-----------
allow_alter_system | | f | on | t
application_name | | f | psql | t
archive_mode | | f | on | t
array_nulls | | f | on | t
This shows how settings evolved within the given time range.
pg_track_settings_log
Logs the change history for a specific configuration parameter.
Example:
SELECT * FROM pg_track_settings_log('work_mem');Output:
ts | name | setting_exists | setting | setting_pretty
---------------------------------+----------+----------------+---------+----------------
2025-11-23 13:28:50.39781+05:30 | work_mem | t | 131072 | 128MB
Functions Used for RDS Environments
pg_track_settings also includes functions built specifically for Amazon RDS environments. Since RDS hides direct access to configuration files, these functions read settings from RDS metadata instead of local PostgreSQL files.
pg_track_settings_rds_src(_srvid integer)
Fetches raw RDS parameter data for the given server ID. It returns timestamp, parameter name, value, and optional role/database information. This acts as the source for all RDS-based snapshots and diffs.
pg_track_settings_snapshot_rds(_srvid integer)
Creates a new snapshot of all RDS settings. This is similar to pg_track_settings_snapshot but collects data from RDS metadata, allowing you to track changes made through RDS parameter groups.
pg_track_settings_snapshot_settings(_srvid integer)
Stores only the current settings portion of an RDS snapshot. It captures the active RDS parameter values and saves them for comparison and historical tracking.
These functions behave like their normal counterparts but are adapted for the RDS environment. You can inspect their implementation in the extension’s source code for deeper understanding.
Example of checking the meta data of RDS source function:
\df+ pg_track_settings_rds_src
Viewing Complete Current Settings
pg_track_settings
This function displays the latest tracked value of every configuration parameter.
SELECT * FROM pg_track_settings();
Partial output:
name | setting | setting_pretty
---------------------------------------------+----------+---------------
allow_alter_system | on | on
allow_in_place_tablespaces | off | off
archive_mode | on | on
autovacuum | on | on
autovacuum_analyze_scale_factor | 0.1 | 0.1
This is one of the most useful views for auditing the current state of PostgreSQL configuration.
Conclusion
The pg_track_settings extension is an essential tool for PostgreSQL administrators who want to maintain complete visibility into configuration changes. Whether tracking role-level updates, system-level modifications, or comparing snapshots over time, this extension provides powerful insights that are not available through native PostgreSQL tools alone.
By using the examples provided in this blog, you can immediately integrate into your workflow, monitor configuration drift, and maintain greater control over your database environments. For RDS users, dedicated functions are available, and reviewing the extension source code will help you understand their specific behavior.
Using pg_track_settings enhances transparency, improves debugging capability, and ensures that configuration changes are tracked accurately across all environments.