skip to content

Parameter Files & Reloads

PostgreSQL reads settings from postgresql.conf, ALTER SYSTEM, roles, databases and sessions, and each has a context deciding reload or restart. Interviewers ask why a change did not apply.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, which configuration files set server parameters, which one wins a conflict, and how do you see where a value came from?

level: juniorimportance: must knowfreq 64%

answer

  1. one main file, one machine-written file
  2. include_dir reads *.conf by name
  3. auto file read after main file
  4. last entry for a name wins
  5. pg_settings source, sourcefile, sourceline

basics

~20 s

postgresql.conf, with any include or include_dir files, is read first, then postgresql.auto.conf, written by ALTER SYSTEM, whose entries win; the last entry for a parameter counts. SHOW gives the value; pg_settings adds source, sourcefile and sourceline.

solid answer

~40 s

The main file is `postgresql.conf`, normally in the data directory; `SHOW config_file` tells you where the running server really read it, because some packages keep it elsewhere. It can pull in other files with `include`, `include_if_exists` and `include_dir` (every `.conf` file in a directory, in file-name order), processed as if pasted at that line. After it the server reads `postgresql.auto.conf` in the data directory, which `ALTER SYSTEM` writes, so its entries override the main file. Across all of that the last entry for a parameter wins, and `-c` options on the `postgres` command line beat every file. `SHOW work_mem` gives the current value; the `pg_settings` view adds `source` (configuration file, command line, database, user, session…) plus `sourcefile` and `sourceline`, so you can see exactly which file and line set it.

code

ini · 6 lines
ini
# end of postgresql.conf
include_dir 'conf.d'          # reads conf.d/*.conf in file-name order
log_min_duration_statement = 1000   # below the include_dir: overrides conf.d

# conf.d/00shared.conf
log_min_duration_statement = 500    # ignored: a later entry wins

go deeper

for a junior

Know that postgresql.conf is the main file, that ALTER SYSTEM writes postgresql.auto.conf, and that SHOW prints the value your session is using right now.

for a middle

Explain the read order, the last-entry-wins rule, include_dir's .conf filter and file-name order, and how pg_settings.source and sourcefile reveal where a value came from.

for a senior

Show that you check SHOW config_file before editing anything, keep shared settings in ordered conf.d files under version control, and audit overrides in pg_settings instead of trusting the file you edited.

for a principal

Treat it as configuration ownership: one mechanism per parameter, files, ALTER SYSTEM or a management tool, so two writers never fight over the same value across a fleet.

## The main file: `postgresql.conf` PostgreSQL's **server parameters** (also called settings or GUCs) are read from plain-text files at startup and again at every reload. The primary one is `postgresql.conf`. `initdb` creates a default copy in the **data directory**, but some distribution packages keep it elsewhere, so never guess: `SHOW config_file;` prints the path the running server actually used, and `SHOW data_directory;` prints the data directory. The syntax is one `name = value` per line. The equals sign is optional, `#` starts a comment, names are case-insensitive, and values that are not simple identifiers or numbers need single quotes. Memory and time values can carry a unit inside quotes, such as `'250ms'` or `'64MB'`; a bare number uses the parameter's own unit, shown in `pg_settings.unit`. ## Include directives `postgresql.conf` can pull in other files, which are processed **as if pasted at that line**: - `include 'file'` reads one file; a missing file is an error. - `include_if_exists 'file'` does the same, but a missing file only logs a message. - `include_dir 'conf.d'` reads every file in the directory whose name ends in `.conf` and does not start with a dot, in C-locale file-name order (digits, then uppercase, then lowercase), which is why people name them `00shared.conf`, `01memory.conf`. - Relative paths resolve against the directory of the file that holds the directive. Position matters. The sample `postgresql.conf` puts its include section near the end, so a `conf.d` file overrides earlier lines of the main file, but any line written below the `include_dir` overrides the `conf.d` file. ## Which entry wins The server reads the files in a fixed sequence, and **the last entry it meets for a parameter wins**; earlier ones are ignored without any error: 1. `postgresql.conf`, top to bottom, with each include expanded in place. 2. `postgresql.auto.conf` in the data directory, the file `ALTER SYSTEM` writes. It is read after the main file, so its entries override `postgresql.conf`. 3. Above both files sit options given on the `postgres` command line (`-c name=value`), which no file edit can override without a restart. The `pg_file_settings` view makes the rule visible: it lists every entry in the files with its `sourcefile`, `sourceline` and `seqno` (the processing order), and marks entries that a later one overrides with `applied = false` and no error. Files are only the base layer. Values attached with `ALTER DATABASE ... SET` or `ALTER ROLE ... SET`, connection options, and a session's own `SET` all outrank the files for the sessions they apply to. | Layer | Written by | `pg_settings.source` | |---|---|---| | Built-in default | the server binary | `default` | | `postgresql.conf` and includes | you or a config tool | `configuration file` | | `postgresql.auto.conf` | `ALTER SYSTEM` | `configuration file` | | `postgres -c` at start | the service script | `command line` | | Database or role defaults | `ALTER DATABASE/ROLE ... SET` | `database`, `user`, `database user` | | Session | `SET` | `session` | ## Checking a value and its origin - `SHOW name;` prints the value in force for your session with a readable unit; `SHOW ALL;` lists everything. - `current_setting('name')` returns the same value as text, usable inside queries. - The **`pg_settings`** view adds what you need to debug: `setting` and `unit`, `context` (whether a reload or a restart applies a change), `source`, `sourcefile` and `sourceline`, `boot_val`, `reset_val` and `pending_restart`. `sourcefile` and `sourceline` are null unless you are a superuser or have the privileges of `pg_read_all_settings`. Note that `pg_settings.setting` is in the parameter's base unit: `shared_buffers` reads `16384` with unit `8kB`, the same 128MB that `SHOW shared_buffers` prints. ```sql SHOW config_file; SELECT name, setting, unit, source, sourcefile, sourceline FROM pg_settings WHERE name = 'log_min_duration_statement'; ``` ## Mistakes worth avoiding - Editing a `postgresql.conf` that is not the one the server reads. - Writing a parameter twice and forgetting that the later line silently wins. - Hand-editing `postgresql.auto.conf` on a running server; `ALTER SYSTEM` rewrites that file and can overwrite the edit. - Treating a file edit as live: nothing changes until a reload, and some parameters need a restart. - Forgetting that the service definition may start `postgres` with `-c` options, which no file edit can beat.

  • What happens when postgresql.conf has an include line naming a file that does not exist?
    With plain `include` it is a configuration-file error: at startup the server refuses to start, and on a reload it logs that the file contains errors and applies no changes. `include_if_exists` exists for optional files: it only logs a message and carries on reading the referencing file.
  • Nothing in either file overrides your postgresql.conf value, yet SHOW reports something else. What else could set it?
    Anything above the files: a `-c` option on the `postgres` command line (source `command line`), an `ALTER DATABASE ... SET` or `ALTER ROLE ... SET` default (`database`, `user`, `database user`), connection options such as `PGOPTIONS` (`client`), or a `SET` in your own session (`session`). `pg_settings.source` names which one.

  • Assumes postgresql.conf always sits in the data directory and never checks SHOW config_file.
  • Believes the first entry wins when a parameter appears twice in the files.
  • Thinks include_dir loads every file in the directory, whatever its name or suffix.
  • Believes postgresql.conf overrides postgresql.auto.conf because it is the main file.
  • Reads pg_settings.setting for shared_buffers as megabytes instead of 8kB pages.
open as a page

In PostgreSQL, after changing a parameter in postgresql.conf, how do you tell whether a reload applies it or a restart is needed?

level: middleimportance: must knowfreq 74%

basics

~10 s

Check pg_settings.context: postmaster parameters need a restart, while sighup and session-level ones apply on a reload. After pg_reload_conf(), pending_restart = true in pg_settings flags any value still waiting for a restart.

open as a page

In PostgreSQL, what does ALTER SYSTEM actually change, and why would a team set allow_alter_system = off?

level: middleimportance: should knowfreq 46%

basics

~20 s

ALTER SYSTEM writes the setting into postgresql.auto.conf, which overrides postgresql.conf once reloaded or restarted. allow_alter_system = off (PostgreSQL 17+) blocks the command so an external config tool stays the single source of truth; it is not security.

open as a page

On a PostgreSQL 18 ticketing site, ALTER SYSTEM plus pg_reload_conf() returned true, yet sessions still show the old value; how do you find out why?

level: seniorimportance: should knowfreq 52%

basics

~10 s

pg_reload_conf() returning true only proves the signal was sent. Check pg_settings (source, sourcefile, pending_restart), then pg_file_settings (applied, error), then role/database overrides, the parameter's context, and whether you changed the right node.

open as a page

In PostgreSQL, how do ALTER DATABASE SET, ALTER ROLE SET, SET and SET LOCAL rank against postgresql.conf, and when does each take effect?

level: seniorimportance: should knowfreq 40%

basics

~10 s

Each layer outranks the files: ALTER DATABASE SET, then ALTER ROLE SET, then ALTER ROLE IN DATABASE SET apply at login; SET lasts for the session; SET LOCAL lasts until the transaction ends.

open as a page