In PostgreSQL, which configuration files set server parameters, which one wins a conflict, and how do you see where a value came from?
answer
- one main file, one machine-written file
- include_dir reads *.conf by name
- auto file read after main file
- last entry for a name wins
- pg_settings source, sourcefile, sourceline
basics
~20 spostgresql.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 sThe 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# 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 winsgo deeper
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.
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.
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.
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.