Lab 3: Trace a parameter through every configuration source, then break a restart

Objective

There are seven places a PostgreSQL parameter can be set, and they do not all win. By the end of this lab you will have set work_mem in all seven on one running cluster and watched pg_settings.source name the winner at each step, so that the precedence order is something you have observed rather than memorised.

Then you will do the thing that actually costs people outages. You will write a configuration file containing an invalid value, reload, and find that the server tolerates it and keeps running. And then you will restart, and find that it does not — that the same file the reload shrugged off is a FATAL at startup, and the cluster stays down.

That asymmetry is why “the config change went in fine last week” and “the server will not come up after the reboot” are so often the same incident, six weeks apart.

Architecture

One cluster, seven configuration sources feeding into it. The lab changes only work_mem, shared_buffers, log_min_duration_statement and max_connections, and reverts everything at the end.

flowchart TD
    D["compiled default\nsource = default"] --> W
    P["postgresql.conf\nsource = configuration file"] --> W
    I["conf.d/*.conf via include_dir\nsource = configuration file"] --> W
    A["postgresql.auto.conf via ALTER SYSTEM\nsource = configuration file"] --> W
    DB["ALTER DATABASE ... SET\nsource = database"] --> W
    U["ALTER ROLE ... SET\nsource = user"] --> W
    DU["ALTER ROLE ... IN DATABASE ... SET\nsource = database user"] --> W
    S["SET in the session\nsource = session"] --> W
    W["the value your query actually uses"]

Requirements

• A PostgreSQL 18 cluster you can restart. The lab restarts it three times and deliberately fails one of those restarts. Do not use a cluster anybody depends on.
• Superuser access, because ALTER SYSTEM, ALTER DATABASE ... SET and ALTER ROLE ... SET all require it.
• Shell access as the data directory owner, to write files under the configuration directory and to run the restart.
• The lab as executed continues on the rbpg-lab01 container from Lab 1, which gives a Debian-packaged cluster with a conf.d directory already wired up. Any PostgreSQL 18 cluster works; the paths will differ.

Scenario

A colleague reports that they set work_mem in postgresql.conf, reloaded, and the server “ignored it”. Someone else says they always put overrides in conf.d because “drop-ins always win”. A third person insists ALTER SYSTEM is the only reliable method.

All three have seen real behaviour. None of them has the full rule. You are going to derive it.

Tasks

Task 1 — Establish the baseline and the include machinery

LAB="$HOME/rbpg-lab-03"
mkdir -p "$LAB"

# Where does this cluster include drop-in files from, and at what line?
docker exec rbpg-lab01 grep -nE "^include" /etc/postgresql/18/main/postgresql.conf

# What is work_mem now, and where did it come from?
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, unit, source, sourcefile, sourceline
  FROM pg_settings WHERE name = 'work_mem';" | tee "$LAB/precedence-chain.txt"

$ grep -nE '^include' postgresql.conf, then a pg_settings query for work_mem

879:include_dir = 'conf.d'			# include files ending in '.conf' from

 name   | setting | unit | source  | sourcefile | sourceline 
----------+---------+------+---------+------------+------------
work_mem | 4096    | kB   | default |            |           
(1 row)

source = default with no sourcefile means nothing on this host has set the parameter; the value is the one compiled into the server. Record the line number of include_dir — 879 on this cluster. You will need it in two tasks’ time.

Task 2 — Layer 1: postgresql.conf

docker exec rbpg-lab01 bash -c \
  "echo \"work_mem = '8MB'  # lab03 layer 1\" >> /etc/postgresql/18/main/postgresql.conf"

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT setting, source, sourcefile, sourceline
  FROM pg_settings WHERE name='work_mem';" | tee -a "$LAB/precedence-chain.txt"

$ psql -X -c "SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name='work_mem';"

 setting |       source       |               sourcefile                | sourceline 
---------+--------------------+-----------------------------------------+------------
8192    | configuration file | /etc/postgresql/18/main/postgresql.conf |        890
(1 row)

setting is reported in the parameter’s own unit — 8192 kB, which is the 8MB you asked for. sourceline is 890, and include_dir was at line 879. Hold on to that.

Task 3 — Layer 2: a conf.d drop-in that does not win

docker exec rbpg-lab01 bash -c \
  "echo \"work_mem = '16MB'  # lab03 layer 2\" > /etc/postgresql/18/main/conf.d/10-tuning.conf"

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT setting, source, sourcefile, sourceline
  FROM pg_settings WHERE name='work_mem';"

$ psql -X -c "SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name='work_mem';"

 setting |       source       |               sourcefile                | sourceline 
---------+--------------------+-----------------------------------------+------------
8192    | configuration file | /etc/postgresql/18/main/postgresql.conf |        890
(1 row)

The drop-in lost. This is the behaviour your colleague ran into, and it is not a bug.

include_dir = 'conf.d' is a directive at line 879 of postgresql.conf. When the parser reaches it, it reads the files in conf.d at that point, as if they were pasted in. Then it carries on reading postgresql.conf from line 880 — and hits the work_mem line at 890. Later assignment wins. The drop-in was read earlier than the setting that overrode it.

Use pg_file_settings to see the whole picture rather than just the winner:

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT sourcefile, sourceline, setting, applied, error
  FROM pg_file_settings WHERE name='work_mem' ORDER BY seqno;" \
  | tee "$LAB/file-settings.txt"

$ psql -X -c "SELECT sourcefile, sourceline, setting, applied, error FROM pg_file_settings WHERE name='work_mem' ORDER BY seqno;"

                  sourcefile                   | sourceline | setting | applied | error 
-----------------------------------------------+------------+---------+---------+-------
/etc/postgresql/18/main/conf.d/10-tuning.conf |          1 | 16MB    | f       | 
/etc/postgresql/18/main/postgresql.conf       |        890 | 8MB     | t       | 
(2 rows)

Now remove the appended line so the include really is the last word, and add a second drop-in to show ordering within the directory:

docker exec rbpg-lab01 sed -i "/# lab03 layer 1/d" /etc/postgresql/18/main/postgresql.conf
docker exec rbpg-lab01 bash -c \
  "echo \"work_mem = '32MB'  # lab03 layer 2b\" > /etc/postgresql/18/main/conf.d/20-override.conf"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT sourcefile, sourceline, setting, applied
  FROM pg_file_settings WHERE name='work_mem' ORDER BY seqno;" \
  | tee -a "$LAB/file-settings.txt"

$ psql -X -c "SELECT sourcefile, sourceline, setting, applied FROM pg_file_settings WHERE name='work_mem' ORDER BY seqno;"

                   sourcefile                    | sourceline | setting | applied 
-------------------------------------------------+------------+---------+---------
/etc/postgresql/18/main/conf.d/10-tuning.conf   |          1 | 16MB    | f
/etc/postgresql/18/main/conf.d/20-override.conf |          1 | 32MB    | t
(2 rows)

This is why drop-in files are conventionally numbered. The numbers are not decoration; they are the precedence order.

Task 4 — Layer 3: ALTER SYSTEM

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET work_mem = '64MB';"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT setting, source, sourcefile, sourceline
  FROM pg_settings WHERE name='work_mem';" | tee -a "$LAB/precedence-chain.txt"

docker exec rbpg-lab01 cat /var/lib/postgresql/18/main/postgresql.auto.conf

$ ALTER SYSTEM SET work_mem = '64MB'; then read pg_settings and cat postgresql.auto.conf

 setting |       source       |                    sourcefile                    | sourceline 
---------+--------------------+--------------------------------------------------+------------
65536   | configuration file | /var/lib/postgresql/18/main/postgresql.auto.conf |          3
(1 row)

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

Two things here are worth more than they look.

First, postgresql.auto.conf is included implicitly at the very end of the configuration read, after postgresql.conf and everything it included. That is why ALTER SYSTEM reliably wins against files: not because it is special-cased, but because it is last.

Second, look at the path. On this Debian cluster the configuration lives in /etc/postgresql/18/main, but postgresql.auto.conf is in /var/lib/postgresql/18/main — inside PGDATA. An operator who greps /etc for the effective configuration will not find it.

Task 5 — Layers 4 to 7: database, role, role-in-database, session

# Note the -i. Without it docker exec does not attach stdin, psql reads
# end-of-file immediately, and the heredoc silently does nothing.
docker exec -i -u postgres rbpg-lab01 psql -X <<'SQL'
CREATE DATABASE lab03;
CREATE ROLE reporting_user LOGIN PASSWORD 'lab03-not-a-real-secret';
ALTER DATABASE lab03 SET work_mem = '128MB';
ALTER ROLE reporting_user SET work_mem = '256MB';
ALTER ROLE reporting_user IN DATABASE lab03 SET work_mem = '512MB';
SQL

Now read the value back from four different connections. The role needs to connect over TCP, because Debian’s default pg_hba.conf uses peer for local socket connections and the operating system user is postgres, not reporting_user:

$ psql -X -U reporting_user -d postgres -c '...'

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "reporting_user"

PW="lab03-not-a-real-secret"

echo "a) postgres in postgres:"
docker exec -u postgres rbpg-lab01 psql -X -d postgres -c \
  "SELECT current_setting('work_mem') AS work_mem, source FROM pg_settings WHERE name='work_mem';"

echo "b) postgres in lab03:"
docker exec -u postgres rbpg-lab01 psql -X -d lab03 -c \
  "SELECT current_setting('work_mem') AS work_mem, source FROM pg_settings WHERE name='work_mem';"

echo "c) reporting_user in postgres:"
docker exec -u postgres -e PGPASSWORD="$PW" rbpg-lab01 \
  psql -X -h 127.0.0.1 -U reporting_user -d postgres -c \
  "SELECT current_setting('work_mem') AS work_mem, source FROM pg_settings WHERE name='work_mem';"

echo "d) reporting_user in lab03:"
docker exec -u postgres -e PGPASSWORD="$PW" rbpg-lab01 \
  psql -X -h 127.0.0.1 -U reporting_user -d lab03 -c \
  "SELECT current_setting('work_mem') AS work_mem, source FROM pg_settings WHERE name='work_mem';"

echo "e) session SET:"
docker exec -u postgres -e PGPASSWORD="$PW" rbpg-lab01 \
  psql -X -h 127.0.0.1 -U reporting_user -d lab03 -c \
  "SET work_mem='1GB'; SELECT current_setting('work_mem') AS work_mem, source FROM pg_settings WHERE name='work_mem';"

$ five connections differing only in role and database

a) postgres in postgres:
work_mem |       source       
----------+--------------------
64MB     | configuration file

b) postgres in lab03:
work_mem |  source  
----------+----------
128MB    | database

c) reporting_user in postgres:
work_mem | source 
----------+--------
256MB    | user

d) reporting_user in lab03:
work_mem |    source     
----------+---------------
512MB    | database user

e) session SET:
work_mem | source  
----------+---------
1GB      | session

Save that to your deliverable and read the source column as the answer. The chain, lowest to highest:

Task 6 — sighup versus postmaster

Not every parameter can be changed by a reload. pg_settings.context says which class a parameter is in.

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT context, count(*) FROM pg_settings GROUP BY context ORDER BY count(*) DESC;"

$ psql -X -c "SELECT context, count(*) FROM pg_settings GROUP BY context ORDER BY count(*) DESC;"

      context      | count 
-------------------+-------
user              |   151
sighup            |   104
postmaster        |    69
superuser         |    49
internal          |    20
superuser-backend |     4
backend           |     2
(7 rows)

Sixty-nine parameters need a restart. Change one of each class and reload:

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET shared_buffers = '256MB';"
docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET log_min_duration_statement = '250ms';"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, context, pending_restart FROM pg_settings
  WHERE name IN ('shared_buffers','log_min_duration_statement');" \
  | tee "$LAB/context-proof.txt"

$ psql -X -c "SELECT name, setting, context, pending_restart FROM pg_settings WHERE name IN ('shared_buffers','log_min_duration_statement');"

            name            | setting |  context   | pending_restart 
----------------------------+---------+------------+-----------------
log_min_duration_statement | 250     | superuser  | f
shared_buffers             | 16384   | postmaster | t
(2 rows)

log_min_duration_statement moved from -1 to 250 on the reload. shared_buffers did not move at all — but pending_restart is now true, which is the server telling you there is a value on disk it has not adopted.

docker exec rbpg-lab01 pg_ctlcluster 18 main restart

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, unit, context, pending_restart
  FROM pg_settings WHERE name='shared_buffers';" | tee -a "$LAB/context-proof.txt"

$ pg_ctlcluster 18 main restart, then psql -X -c "SELECT ... FROM pg_settings WHERE name='shared_buffers';"

      name      | setting | unit |  context   | pending_restart 
----------------+---------+------+------------+-----------------
shared_buffers | 32768   | 8kB  | postmaster | f
(1 row)

Task 7 — An invalid value that a reload tolerates

docker exec rbpg-lab01 bash -c \
  "echo \"log_min_duration_statement = 'never'\" > /etc/postgresql/18/main/conf.d/30-bad.conf"

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT sourcefile, sourceline, setting, applied, error
  FROM pg_file_settings WHERE name='log_min_duration_statement' ORDER BY seqno;"

The first time you run this, the bad line reports applied = f and no error, because postgresql.auto.conf sets the same parameter later and wins — the server never has to validate a value it is not going to use. Remove the winning assignment so the bad line becomes the one that counts:

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET log_min_duration_statement;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT sourcefile, sourceline, setting, applied, error
  FROM pg_file_settings WHERE name='log_min_duration_statement' ORDER BY seqno;"
docker exec rbpg-lab01 tail -3 /var/log/postgresql/postgresql-18-main.log

$ psql -X -c "SELECT ... FROM pg_file_settings ..." then tail the cluster log

                 sourcefile                 | sourceline | setting | applied |            error             
--------------------------------------------+------------+---------+---------+------------------------------
/etc/postgresql/18/main/conf.d/30-bad.conf |          1 | never   | f       | setting could not be applied
(1 row)

          name            | setting 
----------------------------+---------
log_min_duration_statement | 250
(1 row)

2026-08-28 00:14:40.765 UTC [7736] LOG:  received SIGHUP, reloading configuration files
2026-08-28 00:14:40.766 UTC [7736] LOG:  invalid value for parameter "log_min_duration_statement": "never"
2026-08-28 00:14:40.766 UTC [7736] LOG:  configuration file "/etc/postgresql/18/main/conf.d/30-bad.conf" contains errors; unaffected changes were applied

Note the exact wording: “contains errors; unaffected changes were applied”, at severity LOG. The server took the parts it understood, skipped the part it did not, kept its previous value for the broken parameter, and stayed up.

If nobody is reading the log, this is completely invisible. The configuration is now broken and the cluster is healthy.

Task 8 — The same file at startup

docker exec rbpg-lab01 pg_ctlcluster 18 main restart
docker exec rbpg-lab01 pg_lsclusters

$ docker exec rbpg-lab01 pg_ctlcluster 18 main restart && docker exec rbpg-lab01 pg_lsclusters

Error: /usr/lib/postgresql/18/bin/pg_ctl ... exited with status 1: 
2026-08-28 00:14:53.903 UTC [7855] LOG:  invalid value for parameter "log_min_duration_statement": "never"
2026-08-28 00:14:53.903 UTC [7855] FATAL:  configuration file "/etc/postgresql/18/main/conf.d/30-bad.conf" contains errors
pg_ctl: could not start server
Examine the log output.

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

Same file. Same parameter. Same invalid value. On reload it was LOG and the message ended “unaffected changes were applied”. At startup it is FATAL and the message ends at “contains errors”, with no server.

The restart stopped a perfectly healthy cluster and then declined to start it again. Status is down. Nothing will bring it back except fixing the file.

Task 9 — Fix it, and learn the pre-flight check

docker exec rbpg-lab01 rm -f /etc/postgresql/18/main/conf.d/30-bad.conf
docker exec rbpg-lab01 pg_ctlcluster 18 main start
docker exec rbpg-lab01 pg_lsclusters | head -2

Now the query that would have prevented all of it. Run this before every restart, and after every configuration change:

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT sourcefile, sourceline, name, setting, error
  FROM pg_file_settings WHERE error IS NOT NULL;" | tee "$LAB/restart-failure.txt"

An empty result means every assignment in every configuration file is valid and the server will start. Confirm it catches a real fault:

docker exec rbpg-lab01 bash -c \
  "echo \"max_connections = 'lots'\" > /etc/postgresql/18/main/conf.d/30-bad.conf"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT sourcefile, sourceline, name, setting, error
  FROM pg_file_settings WHERE error IS NOT NULL;" | tee -a "$LAB/restart-failure.txt"

docker exec rbpg-lab01 rm -f /etc/postgresql/18/main/conf.d/30-bad.conf
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

$ psql -X -c "SELECT sourcefile, sourceline, name, setting, error FROM pg_file_settings WHERE error IS NOT NULL;"

 sourcefile | sourceline | name | setting | error 
------------+------------+------+---------+-------
(0 rows)

               sourcefile                 | sourceline |      name       | setting |            error             
--------------------------------------------+------------+-----------------+---------+------------------------------
/etc/postgresql/18/main/conf.d/30-bad.conf |          1 | max_connections | lots    | setting could not be applied
(1 row)

Validation

test -s "$LAB/precedence-chain.txt" && echo "OK precedence-chain"
test -s "$LAB/file-settings.txt"    && echo "OK file-settings"
test -s "$LAB/context-proof.txt"    && echo "OK context-proof"
test -s "$LAB/restart-failure.txt"  && echo "OK restart-failure"

grep -q "database user" "$LAB/precedence-chain.txt" && echo "OK all seven layers captured"
grep -q "pending_restart" "$LAB/context-proof.txt"  && echo "OK context proof captured"
grep -q "setting could not be applied" "$LAB/restart-failure.txt" && echo "OK pre-flight demonstrated"

# The cluster must be up and clean before you call this finished.
docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT count(*) AS pending FROM pg_settings WHERE pending_restart;"
docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT count(*) AS broken FROM pg_file_settings WHERE error IS NOT NULL;"

Both counts should be zero.

Questions to answer without looking anything up:

1. A drop-in file in conf.d sets work_mem and postgresql.conf also sets it. Which wins, and what do you need to know to answer?
2. Which query tells you every parameter on this host that is not on its compiled default, regardless of where the files live?
3. A parameter shows pending_restart = true. What are your three options, and which is the worst?
4. Why did the invalid value in Task 7 produce no error the first time?
5. What is the single query you run before every planned restart, and what does an empty result mean?

Expected Outcome

You have observed all six distinct source values that a set parameter can report, used pg_file_settings to explain a drop-in that lost, and watched the same invalid configuration file be survivable on a reload and fatal at startup.

The two queries to carry away are small:

-- What is actually set on this host, and by what?
SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings WHERE source <> 'default' ORDER BY name;

-- Will this server start?
SELECT sourcefile, sourceline, name, setting, error
FROM pg_file_settings WHERE error IS NOT NULL;

The first replaces reading configuration files. The second replaces finding out during an outage.

Troubleshooting

Task 2’s change appears not to take effect. Confirm the reload actually happened: SELECT pg_reload_conf(); returns t whether or not the file parsed. Check pg_file_settings for an error, and check pg_settings.source — a later layer may simply be winning.

A conf.d drop-in is ignored entirely. include_dir is relative to the directory containing the file that declares it, and only files ending in .conf are read. SELECT sourcefile FROM pg_file_settings lists every file the server actually read; if yours is not there, the path or the suffix is wrong.

ALTER SYSTEM cannot run inside a transaction block. Several statements passed in one -c argument are wrapped in a single transaction. Give each ALTER SYSTEM its own -c.

ALTER SYSTEM succeeds and the value does not change. Either the parameter is postmaster context and needs a restart — check pending_restart — or a session-level SET is winning for your session. source names which.

The container will not restart in Task 8, which is the point. Read docker logs rbpg-lab01. You are looking for the FATAL: configuration file ... contains errors line and the LOG: ... setting could not be applied line above it. Task 9 removes the offending line.

pg_file_settings is empty after the failed start. It is a view on a running server. When the server will not start, the log is the only instrument — which is exactly why the pre-flight query in Task 9 must be run before the restart, not after.

Cleanup

docker exec -i -u postgres rbpg-lab01 psql -X <<'SQL'
ALTER SYSTEM RESET work_mem;
ALTER SYSTEM RESET shared_buffers;
ALTER SYSTEM RESET log_min_duration_statement;
ALTER ROLE reporting_user IN DATABASE lab03 RESET work_mem;
ALTER ROLE reporting_user RESET work_mem;
ALTER DATABASE lab03 RESET work_mem;
SQL

docker exec rbpg-lab01 rm -f /etc/postgresql/18/main/conf.d/10-tuning.conf \
                             /etc/postgresql/18/main/conf.d/20-override.conf \
                             /etc/postgresql/18/main/conf.d/30-bad.conf

docker exec rbpg-lab01 pg_ctlcluster 18 main restart

docker exec -i -u postgres rbpg-lab01 psql -X <<'SQL'
DROP DATABASE IF EXISTS lab03;
DROP ROLE IF EXISTS reporting_user;
SQL

# Confirm work_mem is back on its compiled default.
docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT setting, source FROM pg_settings WHERE name='work_mem';"

The final query should report 4096 and default. If it does not, one of the seven layers is still set, and finding which one is the last exercise of the lab.

Production notes

• Run SELECT sourcefile, sourceline, name, setting, error FROM pg_file_settings WHERE error IS NOT NULL; before every planned restart. An empty result is the only evidence that the server will come back up. It costs one query and it is the difference between a four-minute restart and an outage.
• A reload tolerating an invalid value is not leniency, it is deferral. The cluster is now carrying a change that will fail at the next restart — which will happen weeks later, under a different person, for an unrelated reason.
• Decide per estate whether ALTER SYSTEM is permitted at all. Where a configuration-management tool owns postgresql.conf, postgresql.auto.conf silently overrides it and the two mechanisms will contradict each other without either reporting a conflict.
• pg_settings.source and sourcefile belong in any configuration handover document. “The setting is 64MB” is not useful; “the setting is 64MB, from /etc/postgresql/18/main/conf.d/10-tuning.conf line 4” is.

What You Learned

• Seven layers, and the last assignment wins. You set the same parameter in all of them and watched source name the winner at each step.
• postgresql.auto.conf is read last and overrides postgresql.conf, which accounts for a large share of “my edit had no effect”.
• A drop-in that is not read is invisible, and pg_file_settings lists every file the server did read — which is how you tell “ignored” from “overridden”.
• context predicts whether a reload is enough; pending_restart confirms whether the running value and the file now disagree.
• A reload survives an invalid value and a restart does not. The same file, the same content, two completely different outcomes.
• pg_file_settings only exists while the server runs, so the pre-flight check has to happen before the restart, not after the failure.