PostgreSQL is one of the most popular open-source databases, offering flexibility and powerful configuration options. As a PostgreSQL DBA or developer, understanding configuration files is essential for performance tuning, troubleshooting, and database administration.

In this article, we will explore PostgreSQL configuration files, learn how to modify parameters manually, use the ALTER SYSTEM command, and understand the role of the postgresql.auto.conf file.

Understanding PostgreSQL Configuration Files

PostgreSQL uses several configuration files to control database behavior. The most commonly used files are:

• postgresql.conf – Main configuration file
• postgresql.auto.conf – Automatically managed configuration file
• pg_hba.conf – Client authentication configuration file
• pg_ident.conf – User mapping configuration file

Finding PostgreSQL Configuration Files

Connect to PostgreSQL and execute:

SHOW config_file;

Example Output:

/etc/postgresql/16/main/postgresql.conf

To find the data directory:

SHOW data_directory;

Example Output:

/var/lib/postgresql/16/main

To locate the authentication file:

SHOW hba_file;

Viewing Current Configuration Parameters

PostgreSQL provides the SHOW command to display current parameter values.

SHOW max_connections;
SHOW work_mem;
SHOW shared_buffers;

Example Output:

max_connections
---------------
100
work_mem
--------
4MB
shared_buffers
--------------
128MB

Modifying Parameters in postgresql.conf

The postgresql.conf file is the primary configuration file used by PostgreSQL.

Open the file on Ubuntu:

sudo vi /etc/postgresql/16/main/postgresql.conf

Search for the parameter:

shared_buffers

Modify it:

shared_buffers = 512MB

Another example:

work_mem = 16MB

Save and exit the file.

Reloading Configuration Changes

After modifying reloadable parameters, reload the PostgreSQL configuration:

SELECT pg_reload_conf();

Verify the changes:

SHOW work_mem;
SHOW shared_buffers;

Some parameters require a database restart, while others can be applied using a reload.

Using ALTER SYSTEM

PostgreSQL provides the ALTER SYSTEM command to manage configuration settings without editing files manually.

Example 1: Change work_mem

ALTER SYSTEM SET work_mem='32MB';

Example 2: Enable Slow Query Logging

ALTER SYSTEM SET log_min_duration_statement='1000';

Reload the configuration:

SELECT pg_reload_conf();

Verify the changes:

SHOW work_mem;
SHOW log_min_duration_statement;

What is postgresql.auto.conf?

Whenever an ALTER SYSTEM command is executed, PostgreSQL automatically stores the configuration change in the postgresql.auto.conf file.

This file is typically located inside the data directory.

Example:

cd /var/lib/postgresql/16/main
cat postgresql.auto.conf

Example Output:

# Do not edit this file manually
work_mem = '32MB'
log_min_duration_statement = '1000'

Resetting ALTER SYSTEM Changes

If a parameter was modified using ALTER SYSTEM, it can be removed using the RESET option.

ALTER SYSTEM RESET work_mem;

Reload the configuration:

SELECT pg_reload_conf();

Verify the result:

SHOW work_mem;

Checking the Source of Configuration Parameters

PostgreSQL provides detailed information through the pg_settings catalog.

SELECT
name,
setting,
source
FROM pg_settings
WHERE name IN
(
'work_mem',
'shared_buffers',
'max_connections'
);

Example Output:

This helps DBAs identify where a parameter value originates.

Difference Between postgresql.conf and postgresql.auto.conf

Important Note

Values stored in postgresql.auto.conf take precedence over values defined in postgresql.conf.