Query the PostgreSQL Configuration

calendar Apr 8, 2024 / · 4 min read · postgresql database administration  ·
Share on: linkedin copy

Your PostgreSQL Configuration

This blog post empowers you to gain valuable insights into your PostgreSQL database configuration. We'll delve into the provided SQL code, explain its purpose, and equip you with key points and insights to effectively manage your PostgreSQL instance.

Sample Code from Command Line

PostgreSQL >= 11.9

 1SELECT
 2  current_setting('config_file') AS "config_file",
 3  current_setting('hba_file') AS "hba_file",
 4  current_setting('ident_file') AS "ident_file";
 5
 6SELECT
 7  current_setting('data_directory') AS "data_directory",
 8  current_setting('external_pid_file') AS "external_pid_file";
 9
10SELECT
11  current_setting('unix_socket_directories') AS "unix_socket_directories",
12  current_setting('unix_socket_permissions') AS "unix_socket_permissions",
13  current_setting('unix_socket_group') AS "unix_socket_group";
14
15SELECT
16  -- not available on PostgreSQL < 10
17  pg_current_logfile(),
18  current_setting('log_directory') AS "log_directory",  -- log
19  current_setting('log_filename') AS "log_filename";    -- postgresql-%Y-%m-%d_%H%M%S.log
20
21SELECT
22  -- CASE WHEN pg_current_logfile() IS NOT NULL THEN pg_ls_logdir() END AS pg_ls_logdir,
23  pg_ls_waldir(),
24  -- not available on PostgreSQL <= 11.8
25  pg_ls_archive_statusdir(),
26  pg_ls_tmpdir();

Pre PosgreSQL 10

 1SELECT
 2  current_setting('config_file') AS "config_file",
 3  current_setting('hba_file') AS "hba_file",
 4  current_setting('ident_file') AS "ident_file";
 5
 6SELECT
 7  current_setting('data_directory') AS "data_directory",
 8  current_setting('external_pid_file') AS "external_pid_file";
 9
10SELECT
11-- not available on PostgreSQL < 9.3
12  --current_setting('unix_socket_directories') AS "unix_socket_directories",
13  current_setting('unix_socket_permissions') AS "unix_socket_permissions",
14  current_setting('unix_socket_group') AS "unix_socket_group";
15
16SELECT
17  -- not available on PostgreSQL < 10
18  --pg_current_logfile(),
19  current_setting('log_directory') AS "log_directory",  -- log
20  current_setting('log_filename') AS "log_filename";    -- postgresql-%Y-%m-%d_%H%M%S.log
21
22--SELECT
23  -- CASE WHEN pg_current_logfile() IS NOT NULL THEN pg_ls_logdir() END AS pg_ls_logdir,
24  -- not available on PostgreSQL < 10
25  --pg_ls_waldir()
26  -- not available on PostgreSQL <= 11.8
27  --pg_ls_archive_statusdir(),
28  --pg_ls_tmpdir();
29;

Pre Postgresql 11.9

 1SELECT
 2  current_setting('config_file') AS "config_file",
 3  current_setting('hba_file') AS "hba_file",
 4  current_setting('ident_file') AS "ident_file";
 5
 6SELECT
 7  current_setting('data_directory') AS "data_directory",
 8  current_setting('external_pid_file') AS "external_pid_file";
 9
10SELECT
11  current_setting('unix_socket_directories') AS "unix_socket_directories",
12  current_setting('unix_socket_permissions') AS "unix_socket_permissions",
13  current_setting('unix_socket_group') AS "unix_socket_group";
14
15SELECT
16  -- not available on PostgreSQL < 10
17  pg_current_logfile(),
18  current_setting('log_directory') AS "log_directory",  -- log
19  current_setting('log_filename') AS "log_filename";    -- postgresql-%Y-%m-%d_%H%M%S.log
20
21SELECT
22  -- CASE WHEN pg_current_logfile() IS NOT NULL THEN pg_ls_logdir() END AS pg_ls_logdir,
23  pg_ls_waldir()
24  -- not available on PostgreSQL <= 11.8
25  --pg_ls_archive_statusdir(),
26  --pg_ls_tmpdir();
27;

Notes: Useful PostgreSQL dir info. Requires PostgreSQL >= 11.9. Tested on PostgreSQL 11.9, 12.x, 13.0.

Purpose:

The presented code snippets act as a comprehensive tool for revealing critical directory and file locations within your PostgreSQL database server. Understanding these locations is essential for various tasks, including:

  • Backup and Recovery: Identifying data directory and archive locations facilitates efficient backups and recovery procedures.
  • Security Management: Knowing the configuration and log file locations empowers you to monitor security settings and troubleshoot potential issues.
  • Performance Optimization: Understanding log file locations can aid in analyzing performance bottlenecks.

Code Breakdown:

The code utilizes several current_setting functions to retrieve configuration parameter values and pg_* functions (available in specific PostgreSQL versions) to access directory listings. Here's a breakdown of each code block:

Block 1:

SQL

SELECT
 current_setting('config_file') AS "config_file",
 current_setting('hba_file') AS "hba_file",
 current_setting('ident_file') AS "ident_file";

This block retrieves the locations of key configuration files:

  • config_file: Path to the main PostgreSQL configuration file (postgresql.conf).
  • hba_file: Path to the host-based authentication configuration file (pg_hba.conf).
  • ident_file: Path to the file containing user identification information (if configured).

Block 2:

SQL

SELECT
 current_setting('data_directory') AS "data_directory",
 current_setting('external_pid_file') AS "external_pid_file";

This block reveals the location of:

  • data_directory: This directory stores all your database files, including tables, indexes, and logs.
  • external_pid_file (if configured): This file holds the process ID (PID) of the PostgreSQL server.

Block 3:

SQL

SELECT
 current_setting('unix_socket_directories') AS "unix_socket_directories",
 current_setting('unix_socket_permissions') AS "unix_socket_permissions",
 current_setting('unix_socket_group') AS "unix_socket_group";

This block retrieves information about Unix domain sockets used for local connections:

  • unix_socket_directories: Lists directories where Unix domain sockets are created.
  • unix_socket_permissions: Shows the file permissions for Unix domain sockets.
  • unix_socket_group: Indicates the group ownership of Unix domain sockets.

Block 4:

SQL

SELECT
 -- not available on PostgreSQL < 10
 pg_current_logfile(),
 current_setting('log_directory') AS "log_directory", -- log
 current_setting('log_filename') AS "log_filename";  -- postgresql-%Y-%m-%d_%H%M%S.log

This block retrieves information about logging:

  • pg_current_logfile() (PostgreSQL >= 10): Returns the path to the currently active log file.
  • log_directory: Shows the directory where PostgreSQL log files are stored.
  • log_filename: Indicates the naming pattern for PostgreSQL log files (e.g., postgresql-2024-04-08_151717.log).

Block 5:

SQL

 -- CASE WHEN pg_current_logfile() IS NOT NULL THEN pg_ls_logdir() END AS pg_ls_logdir,
 pg_ls_waldir(),
 -- not available on PostgreSQL <= 11.8
 pg_ls_archive_statusdir(),
 pg_ls_tmpdir();

This block retrieves directory listings for specific purposes (functions with version limitations noted):

  • pg_ls_waldir(): Lists files within the Write-Ahead Log (WAL) segment directory, crucial for crash recovery.
  • pg_ls_archive_statusdir() (PostgreSQL >= 11.8): Lists files within the archive status directory, relevant for archiving.
  • pg_ls_tmpdir(): Lists files within the temporary directory used by PostgreSQL.

Key Points and Insights:

  • This code offers a convenient way to discover crucial directory and file locations within your PostgreSQL instance.
  • Knowing these locations empowers you to manage backups, analyze logs, and perform other essential tasks.
  • Remember to check the PostgreSQL documentation for function availability based on your specific version.

Source:

PostgreSQL >= 11.9

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_dirs.sql

Pre Version 10

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_dirs_pre10.sql

Pre 11.9

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_dirs_pre11.9.sql