PostgreSQL DBA Part 5: postgresql.conf, Reload vs Restart

This is Part 5 of the PostgreSQL DBA series on SQL Tips. postgresql.conf is the main configuration file of a PostgreSQL server: it decides how much memory the server uses, how many people can connect, what gets logged and hundreds of other things. By the end of this part you will know where the file lives on Ubuntu, how to read any setting with SHOW and the pg_settings view, and how to tell whether a change needs a simple reload or a full restart.

In Part 4 we covered psql essentials for DBAs, so you can already connect, run queries and read output. That article is not live yet; here we use psql to look at and change the server configuration. Every command was written for Ubuntu Server with PostgreSQL 18 from the official PGDG repository, cluster 18 main.

What you will learn

• Where postgresql.conf, conf.d and postgresql.auto.conf live on Ubuntu, and which file wins
• How to read a setting with SHOW, current_setting() and the pg_settings view
• What the context column means, and how it tells you reload or restart
• How to change a setting safely: check with pg_file_settings, reload, verify
• How ALTER SYSTEM, ALTER DATABASE ... SET, ALTER ROLE ... SET and SET fit together

Prerequisites

• Part 2 (installing PostgreSQL 18 on Ubuntu), Part 3 (the data directory) and Part 4 (psql essentials) of this series.
• One Ubuntu server with PostgreSQL 18 from the PGDG repository and the default cluster 18 main running (check with pg_lsclusters).
• A Linux user with sudo rights. Most commands run as the postgres operating system user through sudo -u postgres.
• A test server. Do not practise the restart steps on a production server during working hours.

How it works

Think of PostgreSQL configuration as a stack of notes, read from bottom to top. The server first reads its built-in defaults. Then it reads the configuration files, and each later file or line can overwrite an earlier one. Finally, a database, a role or a single session can add its own note on top for its own use.

Each setting is called a parameter (the documentation also says "configuration parameter" or GUC). Parameter names are case-insensitive. Every parameter has a type: boolean, string, integer, floating point or enum (a fixed list of allowed words). Memory and time parameters have a unit: work_mem is counted in kilobytes, shared_buffers in 8kB blocks, statement_timeout in milliseconds. You can write a unit yourself, such as '64MB' or '5s'. Memory units are B, kB, MB, GB and TB (multiplier 1024), time units are us, ms, s, min, h and d, and unit names are case-sensitive.

On Ubuntu the files are split across two folders, because the Debian/Ubuntu packaging keeps configuration in /etc and data in /var/lib:

The server reads postgresql.conf from top to bottom. When it meets the line include_dir = 'conf.d' near the end, it reads every file in that folder whose name ends in .conf, in file name order. After that it reads postgresql.auto.conf. If the same parameter appears more than once, the last value read wins. So the order is: postgresql.conf, then conf.d files, then postgresql.auto.conf.

The configuration files are read again whenever the main server process (the postmaster from Part 1) receives a SIGHUP signal, which is what a reload sends. A reload does not disconnect anyone. But some parameters, such as max_connections and shared_buffers, size shared memory when the server starts, so they can only change with a restart, which stops and starts the server and drops every connection. The context column of pg_settings tells you which group a parameter belongs to.

Finding and reading the configuration files

Step 1: Ask the server which files it is using

On the database server, as the postgres user. Never guess the path: ask the running server. This is the fastest way to find postgresql.conf on any machine you have never seen before.

sudo -u postgres psql -c "SHOW config_file;" -c "SHOW data_directory;" -c "SHOW hba_file;"

               config_file               
-----------------------------------------
 /etc/postgresql/18/main/postgresql.conf
(1 row)

       data_directory        
-----------------------------
 /var/lib/postgresql/18/main
(1 row)

              hba_file               
-------------------------------------
 /etc/postgresql/18/main/pg_hba.conf
(1 row)

The configuration file is in /etc, but the data directory is in /var/lib. That is why postgresql.auto.conf (which always sits in the data directory) is in a different folder from postgresql.conf. Ubuntu achieves this by starting the server with -c config_file=/etc/postgresql/18/main/postgresql.conf; you can see that option in ps -u postgres -o pid,args.

Check it worked: ls -l /etc/postgresql/18/main/ should list conf.d, environment, pg_ctl.conf, pg_hba.conf, pg_ident.conf, postgresql.conf and start.conf.

Step 2: See only the lines that are actually set

On the database server, as any user with sudo. postgresql.conf is about 890 lines long in PostgreSQL 18, and almost all of them are comments that show the default value. A line that starts with # is ignored. This grep prints only the lines that really set something.

sudo grep -v -E '^\s*(#|$)' /etc/postgresql/18/main/postgresql.conf

data_directory = '/var/lib/postgresql/18/main'		# use data in another directory
hba_file = '/etc/postgresql/18/main/pg_hba.conf'	# host-based authentication file
ident_file = '/etc/postgresql/18/main/pg_ident.conf'	# ident configuration file
external_pid_file = '/var/run/postgresql/18-main.pid'			# write an extra PID file
port = 5432				# (change requires restart)
max_connections = 100			# (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 128MB			# min 128kB
dynamic_shared_memory_type = posix	# the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_line_prefix = '%m [%p] %q%u@%d '		# special values:
log_timezone = 'Etc/UTC'
cluster_name = '18/main'			# added to process titles if nonempty
datestyle = 'iso, mdy'
timezone = 'Etc/UTC'
lc_messages = 'C.UTF-8'			# locale for system error message
lc_monetary = 'C.UTF-8'			# locale for monetary formatting
lc_numeric = 'C.UTF-8'			# locale for number formatting
lc_time = 'C.UTF-8'			# locale for time formatting
default_text_search_config = 'pg_catalog.english'
include_dir = 'conf.d'			# include files ending in '.conf' from

These are the values that Ubuntu's cluster tool and initdb wrote when the cluster was created. Your timezone and locale lines will match your server. Everything else uses the built-in default. Note the last line: anything you put in /etc/postgresql/18/main/conf.d/ is read after all of this.

Check it worked: the output ends with include_dir = 'conf.d'. If it does not, files you put in conf.d will be ignored.

Reading settings from SQL

Step 3: Read one value with SHOW and current_setting()

In psql, as the postgres user (sudo -u postgres psql). SHOW prints the value the current session is using, with a readable unit. current_setting() returns the same value as text, which is useful inside a query or a PL/pgSQL function.

SHOW work_mem;
SHOW shared_buffers;
SELECT current_setting('max_connections');

 work_mem 
----------
 4MB
(1 row)

 shared_buffers 
----------------
 128MB
(1 row)

 current_setting 
-----------------
 100
(1 row)

These are the documented defaults: work_mem 4MB, shared_buffers typically 128MB and max_connections typically 100. psql also has the meta-command \dconfig (from Part 4): \dconfig *work_mem* lists matching parameters, and \dconfig with no pattern lists only the parameters that are not at their default.

Step 4: Get the full picture from pg_settings

In psql, as the postgres user. pg_settings is a system view with one row per parameter. It shows far more than SHOW: the unit, the type, where the value came from and whether a change needs a restart. This is the query a DBA runs most often when checking configuration.

SELECT name, setting, unit, context, vartype, source
  FROM pg_settings
 WHERE name IN ('work_mem', 'shared_buffers', 'max_connections',
                'log_min_duration_statement', 'statement_timeout', 'port')
 ORDER BY name;

Read setting together with unit. shared_buffers shows 16384 with unit 8kB, which is 16384 × 8kB = 128MB. work_mem shows 4096 kB, which is 4MB. The source column tells you where the value came from: default (built in), configuration file, database, user, database user or session.

The other useful columns, with their meaning from the documentation:

Reload or restart: the context column

The context column answers the question every DBA asks before a change: can I apply this without downtime? The documentation lists seven values, from hardest to easiest to change:

The comments in postgresql.conf also say (change requires restart) next to the postmaster parameters. To list every restart-only parameter on your server:

SELECT name, setting, unit
  FROM pg_settings
 WHERE context = 'postmaster'
 ORDER BY name;

Keep this list in mind when planning: changes to max_connections, shared_buffers, wal_level, listen_addresses or shared_preload_libraries need a maintenance window.

Changing a setting with a reload

Step 5: Put your change in a conf.d file

On the database server, as the postgres user. Instead of editing the long postgresql.conf, put your own settings in a small file in conf.d. Your changes stay in one place, they are easy to review, and a package upgrade will not mix them up with the defaults. Here we log every statement that runs for 500 milliseconds or more, using log_min_duration_statement (default -1, which means disabled).

sudo -u postgres tee /etc/postgresql/18/main/conf.d/50-tuning.conf <<'EOF'
# Local overrides for the 18/main cluster
log_min_duration_statement = 500ms
EOF

Before (the default, nothing set):
#log_min_duration_statement = -1	# -1 is disabled, 0 logs all statements

After (/etc/postgresql/18/main/conf.d/50-tuning.conf):
log_min_duration_statement = 500ms

tee writes what it reads to the file and prints it back, so the output is simply the two lines you wrote. Running it as postgres keeps the file owned by postgres, like the rest of /etc/postgresql/18/main. The name starts with 50 so you can add files that load earlier (00-...) or later (90-...).

Step 6: Check the file before you reload

In psql, as the postgres user. pg_file_settings reads the current contents of the configuration files and reports, line by line, whether each value could be applied. It does not change anything, so it is a safe pre-flight check.

SELECT sourcefile, sourceline, name, setting, applied, error
  FROM pg_file_settings
 WHERE name = 'log_min_duration_statement';

applied is t (true) and error is empty, so the line is valid. To check the whole configuration at once, run SELECT * FROM pg_file_settings WHERE error IS NOT NULL;; zero rows means there are no errors.

Step 7: Reload the configuration

In psql, as the postgres user, or from the shell with sudo. All three commands send the same SIGHUP signal; use whichever fits your work. None of them disconnects users.

SELECT pg_reload_conf();

 pg_reload_conf 
----------------
 t
(1 row)

sudo systemctl reload postgresql@18-main
# or, with the Ubuntu cluster tool:
sudo pg_ctlcluster 18 main reload

t only means the signal was sent. The reload itself happens a moment later, and each existing session picks up the new value after it finishes its current command. So verify in a new session, and read the server log.

Check it worked:

sudo -u postgres psql -c "SHOW log_min_duration_statement;"
sudo tail -3 /var/log/postgresql/postgresql-18-main.log

 log_min_duration_statement 
----------------------------
 500ms
(1 row)

2026-10-23 12:52:53.359 UTC [1586] LOG:  received SIGHUP, reloading configuration files
2026-10-23 12:52:53.360 UTC [1586] LOG:  parameter "log_min_duration_statement" changed to "500ms"

The log line parameter ... changed to ... is your proof the server accepted the change. In pg_settings the row now shows source = configuration file, sourcefile = /etc/postgresql/18/main/conf.d/50-tuning.conf and sourceline = 2.

Undo: delete the line (or the whole file) and reload again; the value goes back to its default of -1.

Changing a setting that needs a restart

Step 8: Change max_connections and see pending_restart

On the database server, as the postgres user. max_connections has context postmaster, default typically 100, and the documentation says it can only be set at server start. Add it to the same conf.d file and reload, to see what happens.

echo "max_connections = 150" | sudo -u postgres tee -a /etc/postgresql/18/main/conf.d/50-tuning.conf

Before (postgresql.conf, line 65):
max_connections = 100			# (change requires restart)

After (added to conf.d/50-tuning.conf, read later, so it wins):
max_connections = 150

SELECT pg_reload_conf();

-- in a new session:
SHOW max_connections;
SELECT name, setting, pending_restart
  FROM pg_settings
 WHERE pending_restart;

 max_connections 
-----------------
 100
(1 row)

      name       | setting | pending_restart 
-----------------+---------+-----------------
 max_connections | 100     | t
(1 row)

The value is still 100, and pending_restart is true. The server log explains why:

LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "max_connections" cannot be changed without restarting the server
LOG:  configuration file "/etc/postgresql/18/main/conf.d/50-tuning.conf" contains errors; unaffected changes were applied

The word "errors" sounds alarming, but nothing is broken: every other change was applied, and only max_connections waits for a restart. Run the query SELECT name FROM pg_settings WHERE pending_restart; after every reload. If it returns rows, you have changes waiting for the next restart.

Step 9: Restart the cluster and verify

On the database server, as a user with sudo.

sudo systemctl restart postgresql@18-main
# or: sudo pg_ctlcluster 18 main restart
pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Check it worked:

sudo -u postgres psql -c "SHOW max_connections;" -c "SELECT count(*) FROM pg_settings WHERE pending_restart;"

 max_connections 
-----------------
 150
(1 row)

 count 
-------
     0
(1 row)

The new value is active and nothing is pending. Remember from the documentation that a higher max_connections also allocates more shared memory, and that a standby must use the same or a higher value than its primary. Part 10 covers connection limits in depth.

ALTER SYSTEM and postgresql.auto.conf

Step 10: Change a setting from SQL with ALTER SYSTEM

In psql, as the postgres user (or a role granted ALTER SYSTEM on that parameter). ALTER SYSTEM writes the setting into postgresql.auto.conf in the data directory. Because that file is read last, its values override postgresql.conf and conf.d. It is handy when you have SQL access but no shell access, which is common on managed services.

ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '16MB'

Like a file edit, ALTER SYSTEM takes effect only after a reload, or a restart for postmaster parameters. It cannot run inside a transaction block, because it writes a file that cannot be rolled back.

Check it worked: in a new session, SELECT name, setting, unit, source, sourcefile FROM pg_settings WHERE name = 'work_mem';

Undo: ALTER SYSTEM RESET work_mem; then SELECT pg_reload_conf();. RESET removes the line from postgresql.auto.conf, and the value from postgresql.conf (or the default) applies again. ALTER SYSTEM RESET ALL; removes every entry.

Per-database, per-role and per-session settings

Step 11: Override settings for one database, one role, or one session

In psql, as the postgres user. Not every setting must be cluster-wide. A reporting user may need more work_mem for big sorts, and an application database may need a statement_timeout so runaway queries stop. The documentation advises against setting statement_timeout in postgresql.conf, because it would affect all sessions, including maintenance jobs. These commands use a sample database shop and a role report_user:

CREATE DATABASE shop;
CREATE ROLE report_user LOGIN PASSWORD 'change-me';

ALTER DATABASE shop SET statement_timeout = '30s';
ALTER ROLE report_user SET work_mem = '128MB';
ALTER ROLE report_user IN DATABASE shop SET statement_timeout = '2min';

These values apply only when a new session starts. Connect as report_user to the shop database (for example psql -h localhost -U report_user -d shop) and check:

SELECT name, setting, unit, source
  FROM pg_settings
 WHERE name IN ('work_mem', 'statement_timeout')
 ORDER BY name;

source shows exactly which level won: database user (the role-in-database setting, 2 minutes) beat database (30 seconds), and user gave report_user 128MB of work_mem. psql's \drds lists all stored role and database settings.

Inside a session, SET changes a value for that session only, SET LOCAL for the current transaction only, and RESET goes back:

SET work_mem = '64MB';
SHOW work_mem;
RESET work_mem;

BEGIN;
SET LOCAL statement_timeout = '5s';
SHOW statement_timeout;
COMMIT;
SHOW statement_timeout;

 work_mem 
----------
 64MB
(1 row)

 statement_timeout 
-------------------
 5s
(1 row)

 statement_timeout 
-------------------
 0
(1 row)

After COMMIT the transaction-level value is gone and statement_timeout is back to 0 (disabled). A normal user can only SET parameters with context user. For superuser parameters such as log_min_duration_statement, a normal user gets an error (see below).

Undo: ALTER DATABASE shop RESET statement_timeout;, ALTER ROLE report_user RESET ALL; and ALTER ROLE report_user IN DATABASE shop RESET ALL;. Then \drds shows zero rows.

Putting it all together, from lowest to highest priority:

Common mistakes and how to fix them

Wrong unit case: invalid value for parameter "work_mem": "64mb"

ERROR:  invalid value for parameter "work_mem": "64mb"
HINT:  Valid units for this parameter are "B", "kB", "MB", "GB", and "TB".

Units are case-sensitive. Write '64MB', not 64mb. The same rule applies inside the configuration files.

A space inside an unquoted value: syntax error in file

A line like maintenance_work_mem = 256 MB in a conf.d file breaks the whole file. pg_file_settings shows a row with error syntax error, and after a reload the log says:

LOG:  syntax error in file "/etc/postgresql/18/main/conf.d/50-tuning.conf" line 4, near token "MB"
LOG:  configuration file "/etc/postgresql/18/main/postgresql.conf" contains errors; no changes were applied

No change from any file is applied, not even the valid lines. On Ubuntu the psql command itself is a wrapper (pg_wrapper) that reads the cluster configuration, so it may also refuse to start with Error: invalid line 4 in /etc/postgresql/18/main/conf.d/50-tuning.conf. Fix: write 256MB or '256 MB' with quotes, then reload. Until you fix it, /usr/lib/postgresql/18/bin/psql still connects.

A misspelled name: unrecognized configuration parameter

LOG:  unrecognized configuration parameter "maintenence_work_mem" in file "/etc/postgresql/18/main/conf.d/50-tuning.conf" line 5
LOG:  configuration file "/etc/postgresql/18/main/conf.d/50-tuning.conf" contains errors; no changes were applied

Again nothing is applied. With ALTER SYSTEM SET wrok_mem = '64MB'; you get ERROR: unrecognized configuration parameter "wrok_mem" straight away, which is one advantage of ALTER SYSTEM. Fix the spelling, check pg_file_settings for rows with an error, and reload.

SET on a restart-only parameter

ERROR:  parameter "max_connections" cannot be changed without restarting the server

postmaster parameters cannot be changed in a session. Put the value in a conf.d file (or use ALTER SYSTEM) and restart, as in Steps 8 and 9.

ALTER SYSTEM cannot run inside a transaction block

This happens when you run ALTER SYSTEM after BEGIN, or in a psql script started with -1 (single transaction, Part 4). Run it on its own, outside a transaction.

permission denied to set parameter "log_min_duration_statement"

A normal role tried SET on a superuser parameter. Either set it centrally, or grant the right to that role: GRANT SET ON PARAMETER log_min_duration_statement TO report_user; (available since PostgreSQL 15).

"I changed postgresql.conf and nothing happened"

Check three things in order: did you reload, is pending_restart true (restart needed), and does sourcefile in pg_settings point to postgresql.auto.conf or a conf.d file that overrides your line.

Practice exercise

1. Set log_min_duration_statement to 1s in /etc/postgresql/18/main/conf.d/50-tuning.conf, check it with pg_file_settings, reload, and prove in a new session that it changed.
2. Find out, without opening any documentation, whether autovacuum_max_workers and random_page_cost need a restart.

Quick reference

# Which files does the server use?
sudo -u postgres psql -c "SHOW config_file;" -c "SHOW data_directory;" -c "SHOW hba_file;"

# Active lines of postgresql.conf, without comments
sudo grep -v -E '^\s*(#|$)' /etc/postgresql/18/main/postgresql.conf

# Read values and their context
sudo -u postgres psql -c "SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'work_mem';"

# Put your own settings in conf.d
sudo -u postgres tee /etc/postgresql/18/main/conf.d/50-tuning.conf <<'EOF'
log_min_duration_statement = 500ms
EOF

# Pre-check the files for errors (zero rows = OK)
sudo -u postgres psql -c "SELECT sourcefile, sourceline, name, error FROM pg_file_settings WHERE error IS NOT NULL;"

# Reload (sighup, superuser, user parameters)
sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo systemctl reload postgresql@18-main

# Anything waiting for a restart?
sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE pending_restart;"

# Restart (postmaster parameters), then check the cluster
sudo systemctl restart postgresql@18-main
pg_lsclusters

# Read the log after a reload or restart
sudo tail -20 /var/log/postgresql/postgresql-18-main.log

# Cluster-wide change from SQL, and its undo
sudo -u postgres psql -c "ALTER SYSTEM SET work_mem = '16MB';" -c "SELECT pg_reload_conf();"
sudo -u postgres psql -c "ALTER SYSTEM RESET work_mem;" -c "SELECT pg_reload_conf();"

# Per-database and per-role defaults, and where they are stored
sudo -u postgres psql -c "ALTER DATABASE shop SET statement_timeout = '30s';"
sudo -u postgres psql -c "ALTER ROLE report_user SET work_mem = '128MB';"
sudo -u postgres psql -c "\drds"

Summary and what's next

• On Ubuntu, postgresql.conf and conf.d are in /etc/postgresql/18/main; postgresql.auto.conf is in /var/lib/postgresql/18/main
• The last value read wins: postgresql.conf, then conf.d files, then postgresql.auto.conf
• SHOW reads one value; pg_settings shows unit, context, source, sourcefile and pending_restart
• context postmaster means restart; sighup, superuser and user mean a reload is enough
• Check pg_file_settings before a reload, and the server log and pending_restart after it
• ALTER SYSTEM writes postgresql.auto.conf and also needs a reload; undo it with ALTER SYSTEM RESET
• Use ALTER DATABASE / ALTER ROLE ... SET for targeted defaults, and SET or SET LOCAL for one session or transaction

In Part 6 you will learn pg_hba.conf and client authentication with scram-sha-256: who may connect to which database, from where, and how they prove who they are.