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---------------100work_mem--------4MBshared_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/maincat postgresql.auto.conf
Example Output:
# Do not edit this file manuallywork_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.
SELECTname,setting,sourceFROM pg_settingsWHERE name IN('work_mem','shared_buffers','max_connections');
Example Output:
| name | setting | source |
|---|---|---|
| work_mem | 32MB | configuration file |
| shared_buffers | 512MB | configuration file |
| max_connections | 100 | default |
This helps DBAs identify where a parameter value originates.
Difference Between postgresql.conf and postgresql.auto.conf
| Feature | postgresql.conf | postgresql.auto.conf |
|---|---|---|
| Managed By | DBA/Administrator | PostgreSQL |
| Edited Manually | Yes | No |
| Created Automatically | No | Yes |
| Used By ALTER SYSTEM | No | Yes |
| Overrides Other Settings | Lower Priority | Higher Priority |
Important Note
Values stored in postgresql.auto.conf take precedence over values defined in postgresql.conf.