Query the PostgreSQL Configuration

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

Your PostgreSQL Configuration

PostgreSQL 12 finished a set of SQL functions that list the contents of the server's own directories: pg_ls_waldir() and pg_ls_logdir() arrived in version 10, and pg_ls_archive_statusdir() and pg_ls_tmpdir() followed in 12. Combined with current_setting() for the paths themselves, a single session can report where the configuration files, data directory, sockets, logs and WAL live, and what is inside them, without shell access to the host.

Purpose and Overview

On a server you did not build, the first questions are usually about location. Which postgresql.conf is the running server reading? Where is pg_hba.conf? Is the data directory on the volume that backups cover? Where do the logs go, and what is the name of the log file being written right now? Packaged installs, containers and managed clusters each put these in different places, and a path remembered from another machine is often wrong.

The server already knows the answers. Every file location is a configuration parameter, so current_setting('config_file') returns the main configuration file the running server was started with, and the other parameters work the same way. The pg_ls_* functions then list the files in the WAL, log, archive status and temporary directories with their size and last modification time.

The queries below are grouped the way a DBA works through an unfamiliar server: configuration files, then the data directory, sockets, logging, and finally the contents of the working directories. The same parameters are also available as rows in pg_settings, which is covered in Understanding PostgreSQL pg_settings and pg_file_settings.

Sample Code

PostgreSQL 12 and later:

 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  pg_current_logfile()                 AS current_logfile,
17  current_setting('log_directory')     AS log_directory,
18  current_setting('log_filename')      AS log_filename;
19
20SELECT * FROM pg_ls_waldir()            ORDER BY modification DESC LIMIT 5;
21SELECT * FROM pg_ls_archive_statusdir() ORDER BY modification DESC LIMIT 5;
22SELECT count(*) AS temp_files, pg_size_pretty(sum(size)) AS temp_size
23FROM pg_ls_tmpdir();

PostgreSQL 10 and 11 run the same first four statements. The last three differ: keep pg_ls_waldir() and drop the pg_ls_archive_statusdir() and pg_ls_tmpdir() statements, which do not exist before 12. pg_ls_logdir() is also available from version 10:

1SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;
2SELECT * FROM pg_ls_logdir() ORDER BY modification DESC LIMIT 5;

Before PostgreSQL 10, only the parameter lookups are available. Remove pg_current_logfile() from the logging statement and use no pg_ls_* function:

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  current_setting('data_directory')         AS data_directory,
6  current_setting('log_directory')          AS log_directory,
7  current_setting('log_filename')           AS log_filename,
8  current_setting('unix_socket_permissions') AS unix_socket_permissions;

The same parameters can be read from pg_settings, with the source of each value:

1SELECT name, setting, source
2FROM pg_settings
3WHERE name IN ('config_file', 'hba_file', 'ident_file', 'data_directory',
4               'log_directory', 'log_filename')
5ORDER BY name;

Notes: unix_socket_directories exists from PostgreSQL 9.3, where it replaced the single-directory unix_socket_directory; on 9.2 and earlier query the old name. Some configuration parameters are normally visible only to superusers. A role that holds pg_read_all_settings can read them, and the pg_ls_* functions and pg_current_logfile() are restricted to superusers and members of pg_monitor unless EXECUTE is granted.

Code Breakdown

The first group of statements reads configuration parameters, and the second group lists directories.

Configuration files

config_file is the main server configuration file, customarily called postgresql.conf, and it can only be set on the postgres command line. hba_file is the host-based authentication file, customarily pg_hba.conf. ident_file is the user name mapping file, customarily pg_ident.conf. In a default installation none of the three is set explicitly: the files are found inside the data directory, so read the returned path instead of assuming one.

data_directory and external_pid_file

data_directory is where the cluster stores its data. If the configuration files were moved outside it, data_directory in postgresql.conf points at the real data location, and it overrides -D and PGDATA for that purpose but not for the configuration files themselves. external_pid_file names an additional process-ID file that the server creates for administration programs. It is only populated when it has been configured.

Unix-domain sockets

unix_socket_directories lists the directories where the server creates its sockets; several can be given, separated by commas. An empty value means the server does not listen on any Unix-domain socket and only TCP/IP connections work. unix_socket_permissions is a numeric mode with a default of 0777, and unix_socket_group sets the owning group; an empty string means the default group of the server user. Together they act as a second layer of access control for local connections.

Logging

log_directory and log_filename only apply when logging_collector is enabled. The directory defaults to log, relative to the data directory, and the file name is a strftime pattern whose default is postgresql-%Y-%m-%d_%H%M%S.log. pg_current_logfile() returns the path of the file in use, including the directory, and returns NULL when the logging collector is disabled. With the collector off, expect NULL: the server logs somewhere else.

The pg_ls functions

pg_ls_waldir(), pg_ls_logdir(), pg_ls_archive_statusdir() and pg_ls_tmpdir() each return name, size and modification time for every ordinary file in their directory. Dot-files, subdirectories and special files are skipped. pg_ls_archive_statusdir() reads pg_wal/archive_status. pg_ls_tmpdir() takes an optional tablespace OID and defaults to pg_default. Ordering by modification puts the newest files first, which is usually what you want.

Key File Location Parameters

A handful of parameters decide where everything lives, and the order in which they interact explains the output.

-D, PGDATA and data_directory

Without any explicit setting, the data directory comes from the -D option or the PGDATA environment variable, and the configuration files are expected inside it. Setting data_directory lets the configuration files live elsewhere, for example under /etc, with -D or PGDATA pointing at the directory that holds them. When all three of config_file, hba_file and ident_file plus data_directory are set, neither -D nor PGDATA is needed.

Relative paths

A relative path in data_directory, config_file, hba_file or ident_file is interpreted against the directory in which postgres was started. log_directory is different: a relative value, including the default log, is relative to the data directory, so combine it with data_directory before looking for log files on disk.

Parameters that need a restart

data_directory, hba_file and ident_file can only be set at server start, and config_file only on the command line. Changing them is a restart-level operation, which is why checking the current values first is part of any migration plan.

Practical Applications

These lookups answer concrete questions during routine administration.

Taking over an unfamiliar server

Run the first two statements to find the configuration files and the data directory, then read pg_hba.conf and postgresql.conf from the paths returned instead of guessing. On hosts with several PostgreSQL versions installed, the paths also confirm which cluster you are actually connected to.

Backup and restore planning

The data directory and the WAL listing show what a file-level backup has to cover and how much WAL is currently retained. If pg_ls_waldir() returns far more files than expected, find out why before scheduling a backup that depends on archiving.

Finding the log file during an incident

During an outage the question is which log file holds the error. pg_current_logfile() returns the path directly, and on version 10 or later pg_ls_logdir() lists the recent files by modification time, so there is no need to read the naming pattern by hand.

Checking local connection access

When a local client cannot connect through a socket, the three socket parameters show the directory to point psql at, the mode, and the owning group. A restrictive mode such as 0770 with a group the client does not belong to explains the failure.

Version Compatibility

The current_setting() lookups of the file-location parameters work on all supported versions. The differences are in the functions around them. PostgreSQL 9.3 replaced unix_socket_directory with unix_socket_directories. PostgreSQL 10 added pg_current_logfile(), pg_ls_logdir() and pg_ls_waldir(), so earlier servers have no dedicated function for listing the WAL or log directory.

PostgreSQL 12 added pg_ls_archive_statusdir() and pg_ls_tmpdir(); neither exists in 10 or 11. A statement that calls one of them fails with an undefined function error on an older server, which is why the sample code is split by version instead of being one script. Access to the pg_ls_* functions and pg_current_logfile() is limited to superusers and pg_monitor members by default; a monitoring account that should run them without full privileges needs EXECUTE granted on each function.

Best Practices

  • Record the paths in your runbook — store the output of the first two statements with each cluster's documentation so incident responders do not guess.
  • Use pg_monitor for monitoring accounts — it covers the pg_ls_* functions and pg_current_logfile() without superuser rights.
  • Match the script to the version — keep a separate statement set for servers older than 12 so a single copy-paste does not fail halfway.
  • Check logging_collector first — a NULL from pg_current_logfile() is expected when it is off, and the logs are elsewhere.
  • Confirm where a value came from — the sourcefile and sourceline columns of pg_settings show which file set it before you edit anything.
  • Watch the WAL and temporary directories over time — a one-time listing says little; repeat it and compare.

References

Posts in this series