Where configuration comes from

“I edited postgresql.conf, reloaded, and the value did not change” is a complaint with about five possible causes, and all of them are diagnosable in one query. What they have in common is that the file you edited is not the only file, and it may not be the last one read.

The assembly order

flowchart TD
    A["postgresql.conf\nread top to bottom"] --> B["include / include_dir /\ninclude_if_exists\nread where the directive appears"]
    B --> C["postgresql.auto.conf\nread LAST, always"]
    C --> D["Effective server configuration"]
    D --> E["Per-database setting"]
    E --> F["Per-role setting"]
    F --> G["Per-session SET"]

The rule for the top three boxes is simply later wins. That has three consequences worth stating separately.

A duplicate setting later in a file beats the earlier one. A file that sets work_mem on line 120 and again on line 640 is running the line 640 value. This is how a carefully considered value at the top of a file gets silently replaced by a copy-pasted block at the bottom.

An included file beats the lines above the include directive, and loses to the lines below it. Position matters, and putting the include at the very end of postgresql.conf is what most people actually intend.

postgresql.auto.conf beats everything in the files. It is read last, unconditionally, and it cannot be reordered.

postgresql.auto.conf

This is the file ALTER SYSTEM writes. It lives in the data directory regardless of packaging — even on Debian, where everything else moved to /etc.

$ cat $PGDATA/postgresql.auto.conf

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

Include directives

postgresql.conf ships with three include forms, all commented out by default:

$ grep -nE '^#?include' $PGDATA/postgresql.conf

879:#include_dir = '...'			# include files ending in '.conf' from
881:#include_if_exists = '...'		# include file only if it exists
882:#include = '...'			# include file

include_dir is the one worth adopting, because it turns configuration into something a configuration-management tool can own without rewriting a file it does not fully control:

# at the very end of postgresql.conf
include_dir = 'conf.d'

conf.d/
├── 10-memory.conf
├── 20-wal.conf
├── 30-logging.conf
└── 90-local-overrides.conf

The numeric prefixes are load-bearing: files are read in name order, so 90-local-overrides.conf beats 10-memory.conf for any key they share. That is the same later-wins rule, applied to filenames.

Finding where a value came from

pg_settings carries the answer per parameter:

psql -U postgres -c \
  "SELECT name, setting, source, sourcefile, sourceline
     FROM pg_settings
    WHERE name IN ('work_mem','shared_buffers','log_min_duration_statement')"

pg_file_settings answers the complementary question — what do the files say, including entries that lost:

$ psql -U postgres -c 'SELECT sourcefile, sourceline, name, setting, applied FROM pg_file_settings ORDER BY sourcefile, sourceline LIMIT 8'

                  sourcefile                   | sourceline |            name            | setting | applied
-----------------------------------------------+------------+----------------------------+---------+---------
/var/lib/postgresql/18/docker/postgresql.conf |         60 | listen_addresses           | *       | t
/var/lib/postgresql/18/docker/postgresql.conf |         65 | max_connections            | 100     | t
/var/lib/postgresql/18/docker/postgresql.conf |        132 | shared_buffers             | 128MB   | t
/var/lib/postgresql/18/docker/postgresql.conf |        155 | dynamic_shared_memory_type | posix   | t
/var/lib/postgresql/18/docker/postgresql.conf |        271 | max_wal_size               | 1GB     | t
/var/lib/postgresql/18/docker/postgresql.conf |        272 | min_wal_size               | 80MB    | t
/var/lib/postgresql/18/docker/postgresql.conf |        645 | log_timezone               | Etc/UTC | t
/var/lib/postgresql/18/docker/postgresql.conf |        687 | autovacuum_worker_slots    | 16      | t
(8 rows)

The applied column is the useful one. A row with applied = false is a line somebody wrote that is having no effect, and the reason is almost always that a later line or a later file set the same key.

# Every configuration line that is being ignored, and why it exists
psql -U postgres -c \
  "SELECT sourcefile, sourceline, name, setting, error
     FROM pg_file_settings WHERE NOT applied OR error IS NOT NULL"

Running that after every configuration change is a two-second check that catches duplicate keys, typos and losing overrides before they become a puzzle.

Production discipline

1. Read postgresql.auto.conf before investigating any “my edit did nothing” report. It is read last and it wins.
2. Remove ALTER SYSTEM entries with ALTER SYSTEM RESET, never by editing the file, which the server rewrites wholesale.
3. Put include_dir at the end of postgresql.conf and use numeric filename prefixes, because both the directive position and the file order follow later-wins.
4. Use include_if_exists for anything conditional. A missing include target stops the server from starting, and a reload will not tell you.
5. Check pg_file_settings for applied = false and non-null error after every change. It catches duplicate keys and typos that the reload silently absorbed.

Cross-course references

• Ansible for Production Sysadmins — Part XVII (Templates) covers managing a conf.d directory as generated fragments, which is the pattern include_dir exists to enable.
• Git, CI/CD & GitOps — Part LXXV (Drift) covers detecting when the file on disk stops matching the file in the repository, which is the same gap this lesson’s Under the Hood section describes.
• Linux for Production Sysadmins — Part VII (systemd) covers reading the service log after a reload, which is where a rejected configuration reports itself.

Quiz