PostgreSQL Miscellaneous Settings Query Guide

calendar Mar 22, 2026 / · 5 min read · postgresql database administration sql queries configuration system views pg_settings postgres dba  ·
Share on: linkedin copy

PostgreSQL Miscellaneous Settings Query Guide

This PostgreSQL query retrieves configuration settings that fall outside all standard category groups — including customized options, process title settings, and any settings added by extensions or custom builds. It works as a catch-all complement to the other category-specific settings queries.

Purpose and Overview

The PostgreSQL pg_settings view groups every configuration parameter into a category such as Memory, Logging, or Replication. Most settings scripts target one specific category. This script takes the opposite approach: it excludes all known categories and returns only what remains.

This is particularly useful for catching settings introduced by extensions, third-party modules, or newer PostgreSQL versions that don't fit neatly into existing categories.

SQL Script

 1SELECT
 2    name,
 3    setting,
 4    category,
 5    vartype,
 6    short_desc,
 7    enumvals,
 8    source,
 9    min_val,
10    max_val,
11    boot_val,
12    reset_val,
13    -- not available on PostgreSQL < 9.5
14    pending_restart
15FROM
16    pg_settings
17WHERE
18    category NOT ILIKE '% / Authentication%'
19        AND
20    category NOT ILIKE '%Autovacuum%'
21        AND
22    category NOT ILIKE '%Client Connection Defaults%'
23        AND
24    category NOT ILIKE '%Compatibility%'
25        AND
26    category NOT ILIKE '% / Connection Settings%'
27        AND
28    category NOT ILIKE '%Developer%'
29        AND
30    category NOT ILIKE '%Error%'
31        AND
32    category NOT ILIKE '%File Locations%'
33        AND
34    category NOT ILIKE '%Lock%'
35        AND
36    category NOT ILIKE '%Logging%'
37        AND
38    category NOT ILIKE '%Memory%'
39        AND
40    category NOT ILIKE '%Preset Options%'
41        AND
42    category NOT ILIKE '%Query%'
43        AND
44    category NOT ILIKE '%Replication%'
45        AND
46    category NOT ILIKE '%Resource%'
47        AND
48    name NOT ILIKE '%ssl%'
49        AND
50    category NOT ILIKE '%SSL%'
51        AND
52    short_desc NOT ILIKE '%SSL%'
53        AND
54    category NOT ILIKE '%Statistics%'
55        AND
56    category NOT ILIKE '%Write-Ahead Log%'
57;

Code Breakdown

Inverse Filter Approach

Unlike the other settings scripts that filter for a specific category with category ILIKE '%Something%', this script uses a chain of NOT ILIKE conditions to exclude every known category. Whatever remains after all exclusions are applied represents settings not covered by any other category-specific script.

The category column is included in the SELECT list here (unlike the other settings scripts) so you can see which category each remaining setting belongs to and identify whether any new categories have appeared.

enumvals Column

This script includes enumvals in the SELECT list, which lists the valid values for enum-type parameters. This is useful for miscellaneous settings that may include extension-added parameters with constrained value sets.

NOT ILIKE Pattern

Each exclusion uses ILIKE for case-insensitive matching with % wildcards to match category names regardless of subcategory suffix. For example, category NOT ILIKE '%Resource%' excludes Resource Usage / Memory, Resource Usage / Disk, and all other Resource subcategories in one condition.

What This Query Returns

Common settings that appear in the miscellaneous results include:

Customized Options — parameters set by extensions or custom configurations that PostgreSQL places in a Customized Options category.

Process Title — settings that control what appears in the operating system process list for PostgreSQL backends, such as update_process_title and cluster_name.

Extension-Added Settings — parameters registered by loaded extensions via pg_settings that don't map to a standard category.

Selected Columns

name: The configuration parameter name.

setting: The current active value of the parameter.

category: Included here to identify which category any newly discovered settings belong to.

vartype: The data type (bool, integer, real, string, enum).

short_desc: A brief description of what the parameter controls.

enumvals: Valid values for enum-type parameters; NULL for non-enum types.

source: Where the current value originates (default, configuration file, command line, etc.).

min_val / max_val: Allowed range for numeric parameters.

boot_val: Compiled-in default value.

reset_val: Value used if the parameter is reset.

pending_restart: Whether a server restart is needed for a pending change to take effect.

Practical Applications

Extension Auditing

After installing a PostgreSQL extension, run this query to see what new settings it has registered that are not covered by standard category queries.

Version Upgrade Review

When upgrading PostgreSQL major versions, new settings may appear in unfamiliar categories. This query surfaces them before they are accounted for in monitoring scripts.

Complete Configuration Inventory

Used alongside all other postgres_settings_*.sql scripts, this query completes a full inventory of every configuration parameter on the server with no gaps.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Run alongside the full settings suite: This query is most valuable when paired with all the other category-specific settings scripts to produce a complete configuration audit.

Watch the category column: If a setting appears with a category you do not recognize, research it — it may indicate an extension is active or a new PostgreSQL feature has been enabled.

Include in change audits: Run before and after major configuration changes or extension installs to diff what has changed.

References

Posts in this series