PostgreSQL File Location Settings Query via pg_settings

calendar Sep 7, 2025 / · 3 min read · postgresql database administration sql queries configuration file management system views database tuning postgres dba pg_settings  ·
Share on: linkedin copy

PostgreSQL File Location Settings Query Guide

This PostgreSQL query helps database administrators examine file location configuration settings by querying the pg_settings system view. It retrieves essential information about where PostgreSQL stores its files and directories.

Purpose and Overview

The query targets PostgreSQL's file location settings to help administrators understand where the database stores critical files like data directories, configuration files, and log files. This information is vital for backup procedures, maintenance tasks, and system monitoring.

Code Breakdown

Query Structure

 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    -- File Locations
19    category ILIKE '%File Locations%';

Selected Columns

name: The configuration parameter name (like data_directory or config_file)

setting: The current value of the parameter showing actual file paths and directories

vartype: The data type of the setting (string, integer, boolean, etc.)

short_desc: A brief description explaining what each setting controls

source: Where the setting value comes from (configuration file, command line, default, etc.)

min_val and max_val: Minimum and maximum allowed values for numeric parameters

boot_val: The default value when PostgreSQL starts

reset_val: The value that would be used if the parameter is reset

pending_restart: Shows if a server restart is needed for changes to take effect

Filter Condition

The WHERE clause uses category ILIKE '%File Locations%' to filter results to only file location-related settings. The ILIKE operator performs case-insensitive pattern matching.

Key Configuration Parameters

Common File Location Settings

data_directory: The main data directory where PostgreSQL stores database files

config_file: Location of the main postgresql.conf configuration file

hba_file: Path to the pg_hba.conf authentication configuration file

ident_file: Location of the pg_ident.conf user mapping file

external_pid_file: Path for the external process ID file

Practical Applications

System Administration

Database administrators use this query to verify file locations before performing maintenance tasks, setting up backups, or troubleshooting configuration issues.

Security Audits

Security teams can review file locations to ensure proper permissions and access controls are in place for critical database files.

Migration Planning

When moving PostgreSQL installations, this query helps identify all file locations that need to be considered during the migration process.

Usage Examples

Basic Query Execution

Run this query in psql or any PostgreSQL client to see current file location settings.

Filtering Specific Settings

Add additional WHERE conditions to focus on specific parameters like WHERE name = 'data_directory'.

Monitoring Changes

Regular execution helps track configuration changes and ensure consistency across database environments.

Version Compatibility

The pending_restart column is available in PostgreSQL 9.5 and later versions. For earlier versions, this column should be removed from the SELECT list or the query will fail.

Best Practices

Regular Monitoring

Execute this query regularly to verify that file locations remain consistent and accessible.

Documentation

Keep query results documented as part of your database administration procedures.

Change Management

Use this information when planning configuration changes or system updates.

References

Posts in this series