Lab 2: Explore the process model, shared memory and parameter contexts

Objective

By the end of this lab you will have watched a PostgreSQL backend process come into existence because a client connected, found the same process from two directions, and proved — rather than been told — that some configuration changes take effect on a reload and others do not.

That last item is the one worth the time. The difference between a sighup parameter and a postmaster parameter is the difference between a change you can make now and a change that needs an outage window, and getting it wrong means either an unnecessary restart or a change everybody believes is live when it is not. You will finish holding evidence of both cases from the same server, five seconds apart.

Everything runs inside one throwaway container. Nothing is installed on your host and nothing outside the container is read or modified.

Architecture

One PostgreSQL 18 container on a dedicated Docker network. All work happens inside it via docker exec, so no port is published to the host and no PostgreSQL client is needed on your machine.

flowchart LR
    H["Your host\ndocker exec"] --> C["rbpg-lab02\npostgres:18"]
    C --> P["postmaster PID 1"]
    P --> BG["background processes\ncheckpointer, walwriter,\nautovacuum launcher, io workers"]
    P --> B["client backend\nappears on connect"]
    P --> S["shared memory\nBuffer Blocks = shared_buffers"]

Requirements

• Docker with permission to run containers and create a user-defined network. The lab pulls postgres:18, roughly 650 MB.
• No published ports. The container is reached only through docker exec, so nothing binds a host port and no host firewall rule is needed.
• No host PostgreSQL required. psql runs inside the container.
• Names used: one container rbpg-lab02 and one network rbpg-net-02. Nothing else is created, read or removed.

Scenario

You have been handed responsibility for a PostgreSQL server. Before you change anything on it you want to be able to answer three questions that will come up in every future incident: which processes should be running, how a session maps to a process, and whether a given parameter can be changed without an outage.

Rather than reading the answers, you are going to establish them on a cluster you can safely break.

Tasks

Task 1 — Record the starting state and start the cluster

LAB="$HOME/rbpg-lab-02"
rm -rf "$LAB"
mkdir -p "$LAB"
cd "$LAB"

{
  echo "--- containers before the lab"
  docker ps -a --format '{{.Names}}'
  echo "--- networks before the lab"
  docker network ls --format '{{.Name}}'
} > "$LAB/state.pre-lab"

docker rm -f rbpg-lab02 2>/dev/null || true
docker network rm rbpg-net-02 2>/dev/null || true
docker network create rbpg-net-02

docker run -d --name rbpg-lab02 --network rbpg-net-02 \
  -e POSTGRES_PASSWORD=lab-password-not-a-real-secret \
  postgres:18

# Wait for the server to accept connections rather than guessing at a sleep.
for i in $(seq 1 40); do
  docker exec rbpg-lab02 pg_isready -U postgres >/dev/null 2>&1 && break
  sleep 1
done
docker exec rbpg-lab02 pg_isready -U postgres

The wait loop matters. The container is running some seconds before the database inside it is ready, because the entrypoint runs initdb, starts a temporary server to apply initialisation, shuts it down and starts the real one. pg_isready is the correct readiness test, and polling it beats guessing at a sleep value that is either too short on a slow host or wasted time on a fast one.

$ docker exec rbpg-lab02 pg_isready -U postgres

/var/run/postgresql:5432 - accepting connections

Task 2 — Read the process tree at rest

docker exec rbpg-lab02 ps -eo pid,ppid,args --sort=pid | tee "$LAB/process-tree.txt"

$ docker exec rbpg-lab02 ps -eo pid,ppid,args --sort=pid

    PID    PPID COMMAND
    1       0 postgres
   74       1 postgres: io worker 0
   75       1 postgres: io worker 1
   76       1 postgres: io worker 2
   77       1 postgres: checkpointer
   78       1 postgres: background writer
   80       1 postgres: walwriter
   81       1 postgres: autovacuum launcher
   82       1 postgres: logical replication launcher

Annotate your copy of process-tree.txt with what each process does. Two entries are version-specific and worth noting explicitly: the three io worker processes exist because this is PostgreSQL 18, and there is no stats collector, which was removed in PostgreSQL 15. An inventory written against an older release will look wrong in both directions.

Confirm which version you are actually running, so the annotation is grounded:

docker exec rbpg-lab02 psql -U postgres -tAc 'SELECT version()'
docker exec rbpg-lab02 psql -U postgres -c 'SHOW data_directory' -c 'SHOW port'

$ docker exec rbpg-lab02 psql -U postgres -tAc 'SELECT version()'

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

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

port
------
5432
(1 row)

Task 3 — Make a backend appear, and find it twice

Open a session that will sit still long enough to inspect, then look for it from inside the database and from the operating system.

# A session that holds itself open for 60 seconds, in the background.
docker exec -d rbpg-lab02 bash -c \
  "psql -U postgres -c \"SELECT pg_sleep(60)\" >/dev/null 2>&1"
sleep 3

docker exec rbpg-lab02 psql -U postgres -c \
  "SELECT pid, backend_type, state, wait_event_type, wait_event
     FROM pg_stat_activity ORDER BY backend_type, pid" \
  | tee "$LAB/session-trace.txt"

$ docker exec rbpg-lab02 psql -U postgres -c 'SELECT pid, backend_type, state, wait_event_type, wait_event FROM pg_stat_activity ORDER BY backend_type, pid'

 pid |         backend_type         | state  | wait_event_type |     wait_event
-----+------------------------------+--------+-----------------+---------------------
81 | autovacuum launcher          |        | Activity        | AutovacuumMain
78 | background writer            |        | Activity        | BgwriterMain
77 | checkpointer                 |        | Activity        | CheckpointerMain
124 | client backend               | active | Timeout         | PgSleep
131 | client backend               | active |                 |
74 | io worker                    |        | Activity        | IoWorkerMain
75 | io worker                    |        | Activity        | IoWorkerMain
76 | io worker                    |        | Activity        | IoWorkerMain
82 | logical replication launcher |        | Activity        | LogicalLauncherMain
80 | walwriter                    |        | Activity        | WalWriterMain
(10 rows)

Now find that same PID from the operating system side. The number in pg_stat_activity.pid is a real process ID.

SLEEPER=$(docker exec rbpg-lab02 psql -U postgres -tAc \
  "SELECT pid FROM pg_stat_activity
    WHERE query LIKE '%pg_sleep%' AND pid <> pg_backend_pid() LIMIT 1")
echo "sleeper backend pid = $SLEEPER"
docker exec rbpg-lab02 ps -o pid,ppid,etime,rss,args -p "$SLEEPER" \
  | tee -a "$LAB/session-trace.txt"

$ docker exec rbpg-lab02 ps -o pid,ppid,etime,rss,args -p 124

    PID    PPID     ELAPSED   RSS COMMAND
  124       1       00:03 15360 postgres: postgres postgres [local] SELECT

Three things to take from that single line. PPID 1 confirms the postmaster forked it. RSS 15360 is 15 MB of private memory for one idle-ish session, which is the number to multiply when someone proposes a large max_connections. And the process title is not postgres — it has been rewritten to show the user, the database, the client and the current command, which means ps alone tells you a great deal about what a PostgreSQL host is doing.

Wait for the sleep to finish, then confirm the process is gone:

sleep 60
docker exec rbpg-lab02 ps -o pid,args -p "$SLEEPER" 2>/dev/null \
  || echo "backend $SLEEPER no longer exists"

The process existed only for the life of the connection. That is the whole process model in one observation.

Task 4 — Read the shared memory segment

docker exec rbpg-lab02 psql -U postgres -c \
  "SELECT name, pg_size_pretty(allocated_size) AS size
     FROM pg_shmem_allocations ORDER BY allocated_size DESC LIMIT 8" \
  | tee "$LAB/shared-memory.txt"

docker exec rbpg-lab02 psql -U postgres -c \
  "SELECT pg_size_pretty(sum(allocated_size)) AS shared_total
     FROM pg_shmem_allocations" | tee -a "$LAB/shared-memory.txt"

docker exec rbpg-lab02 psql -U postgres -c 'SHOW shared_buffers' \
  | tee -a "$LAB/shared-memory.txt"

$ docker exec rbpg-lab02 psql -U postgres -c 'SELECT name, pg_size_pretty(allocated_size) FROM pg_shmem_allocations ORDER BY allocated_size DESC LIMIT 8'

        name        |  size
--------------------+---------
Buffer Blocks      | 128 MB
<anonymous>        | 4637 kB
XLOG Ctl           | 4110 kB
AioHandleIOV       | 2784 kB
                  | 2227 kB
AioHandle          | 1566 kB
AioHandleData      | 1392 kB
Buffer Descriptors | 1024 kB
(8 rows)

shared_total
--------------
150 MB
(1 row)

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

Buffer Blocks equals shared_buffers exactly. The 22 MB difference between that and the 150 MB total is the coordination overhead: WAL control, the asynchronous I/O handles that only exist from PostgreSQL 18, the buffer descriptors and the lock structures.

Task 5 — Prove which parameters a reload can apply

This is the task the lab exists for. Read the context of three parameters, change two of them, reload, and see what happened.

docker exec rbpg-lab02 psql -U postgres -c \
  "SELECT name, setting, unit, context
     FROM pg_settings
    WHERE name IN ('shared_buffers','work_mem','log_min_duration_statement')
    ORDER BY name" | tee "$LAB/context-proof.txt"

$ docker exec rbpg-lab02 psql -U postgres -c "SELECT name, setting, unit, context FROM pg_settings WHERE name IN ('shared_buffers','work_mem','log_min_duration_statement') ORDER BY name"

            name            | setting | unit |  context
----------------------------+---------+------+------------
log_min_duration_statement | -1      | ms   | superuser
shared_buffers             | 16384   | 8kB  | postmaster
work_mem                   | 4096    | kB   | user
(3 rows)

Note the units before going further. shared_buffers reads 16384 with a unit of 8kB, which is 128 MB, and work_mem reads 4096 with a unit of kB, which is 4 MB. Neither number means what it appears to mean read alone.

Now change both and reload.

docker exec rbpg-lab02 psql -U postgres \
  -c "ALTER SYSTEM SET work_mem = '8MB'" \
  -c "ALTER SYSTEM SET shared_buffers = '256MB'"

docker exec rbpg-lab02 psql -U postgres -tAc 'SELECT pg_reload_conf()'
sleep 1

docker exec rbpg-lab02 psql -U postgres -c \
  "SELECT name, setting, unit, context, pending_restart
     FROM pg_settings WHERE name IN ('work_mem','shared_buffers') ORDER BY name" \
  | tee -a "$LAB/context-proof.txt"

$ docker exec rbpg-lab02 psql -U postgres -c "SELECT name, setting, unit, context, pending_restart FROM pg_settings WHERE name IN ('work_mem','shared_buffers') ORDER BY name"

      name      | setting | unit |  context   | pending_restart
----------------+---------+------+------------+-----------------
shared_buffers | 16384   | 8kB  | postmaster | t
work_mem       | 8192    | kB   | user       | f
(2 rows)

This is the finding. Both ALTER SYSTEM commands succeeded. Both wrote to the same file. The reload applied one of them and could not apply the other, and the server tells you which is which in the pending_restart column rather than leaving you to infer it.

An operator who ran only the two ALTER SYSTEM commands and the reload — and did not check — would reasonably believe both changes were live. One is not, and will not be until the next restart.

See what ALTER SYSTEM actually wrote:

docker exec rbpg-lab02 bash -c 'cat "$PGDATA/postgresql.auto.conf"'

$ docker exec rbpg-lab02 cat /var/lib/postgresql/18/docker/postgresql.auto.conf

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

Task 6 — Revert and confirm

docker exec rbpg-lab02 psql -U postgres \
  -c "ALTER SYSTEM RESET work_mem" \
  -c "ALTER SYSTEM RESET shared_buffers"
docker exec rbpg-lab02 psql -U postgres -tAc 'SELECT pg_reload_conf()'

docker exec rbpg-lab02 psql -U postgres -c \
  "SELECT name, setting, pending_restart
     FROM pg_settings WHERE name IN ('work_mem','shared_buffers') ORDER BY name" \
  | tee -a "$LAB/context-proof.txt"

$ docker exec rbpg-lab02 psql -U postgres -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name IN ('work_mem','shared_buffers') ORDER BY name"

      name      | setting | pending_restart
----------------+---------+-----------------
shared_buffers | 16384   | f
work_mem       | 4096    | f
(2 rows)

pending_restart returning to f without any restart is worth a moment. It was never reporting “a restart is required”; it was reporting “the file and the running value disagree”. Withdrawing the change made them agree again.

Validation

• docker exec rbpg-lab02 pg_isready -U postgres reports accepting connections and exits 0.
• process-tree.txt lists exactly eight PostgreSQL processes with no client connected, all with PPID 1, including three io worker entries and no stats collector.
• session-trace.txt shows the same PID in a pg_stat_activity row with backend_type of client backend and in a ps line whose PPID is 1.
• After the sleeping session ends, ps -p for that PID reports no such process.
• shared-memory.txt shows Buffer Blocks equal to the value reported by SHOW shared_buffers, and a total larger than it.
• context-proof.txt contains a row where shared_buffers has pending_restart of t while work_mem has f, after a reload in which both were set.
• The final rows in context-proof.txt show pending_restart of f for both parameters after the reset.

A failed validation is specific. If pending_restart is f for shared_buffers immediately after the reload, the ALTER SYSTEM almost certainly failed — check its output rather than assuming the reload was at fault. If pg_isready never succeeds, read docker logs rbpg-lab02; an initdb failure is reported there in full.

Expected Outcome

$HOME/rbpg-lab-02/
├── context-proof.txt
├── process-tree.txt
├── session-trace.txt
├── shared-memory.txt
└── state.pre-lab

You can now answer, for any PostgreSQL server, whether a given parameter change requires an outage — from the server itself, in one query, rather than from documentation or memory. You can also map a session to a process in both directions, which is the prerequisite for every investigation in Part IX and Part XVI.

Troubleshooting

docker: Error response from daemon: Conflict. The container name "/rbpg-lab02" is already in use. A previous run of this lab is still present. Task 1 removes it; if you skipped that step, run docker rm -f rbpg-lab02 and start again.

pg_isready never reports accepting connections. Read docker logs rbpg-lab02. The commonest cause is that the container was started without POSTGRES_PASSWORD, which the official image refuses, and it says so explicitly in the log.

psql: error: connection to server ... failed from inside the container. The server is still initialising. This is what the wait loop in Task 1 is for; give it the full forty seconds.

ALTER SYSTEM cannot run inside a transaction block. Several statements passed in a single -c argument are wrapped in one transaction. Use a separate -c for each ALTER SYSTEM, exactly as the commands above do.

The sleeping backend does not appear in pg_stat_activity. The three-second wait was not enough, or the background docker exec failed. Re-run the docker exec -d command and check with docker exec rbpg-lab02 ps -ef | grep pg_sleep.

Cleanup

LAB="$HOME/rbpg-lab-02"

# 1. Remove the container and the network this lab created.
docker rm -f rbpg-lab02 2>/dev/null || true
docker network rm rbpg-net-02 2>/dev/null || true

# 2. Compare against the inventory recorded in Task 1.
cat "$LAB/state.pre-lab"
echo "--- containers now"
docker ps -a --format '{{.Names}}'
echo "--- networks now"
docker network ls --format '{{.Name}}'

# 3. Assert the resources are gone.
docker ps -a --format '{{.Names}}' | grep -c '^rbpg-lab02$' || echo "container gone"
docker network ls --format '{{.Name}}' | grep -c '^rbpg-net-02$' || echo "network gone"

# 4. The deliverables remain on the host.
ls -l "$LAB"

The two inventories printed in step 2 must differ only by the removal of rbpg-lab02 and rbpg-net-02. Anything else that appeared or disappeared happened outside this lab and is worth investigating before you close the session.

Production notes

• The pending_restart check in Task 5 belongs at the end of every configuration change procedure you write. It is one query and it converts “I made the change” into “the change is in effect”.
• The RSS figure in Task 3 is the honest input to a max_connections discussion. Measure it on a host running your workload rather than quoting this lab’s 15 MB, because a backend executing real queries with real work_mem allocations is substantially larger.
• postgresql.auto.conf overriding postgresql.conf is a frequent source of confusion in estates where several people administer the same server. Where configuration is managed by Ansible or a similar tool, decide deliberately whether ALTER SYSTEM is permitted at all, because the two mechanisms will otherwise silently contradict each other.
• Running the whole lab through docker exec rather than publishing a port is a habit worth keeping for throwaway databases. A published port on a development machine is a database reachable from the network, and the default credentials in a lab are exactly the ones worth not exposing.

What You Learned

• A backend process exists only while its connection does. You watched one appear, found it in ps and in pg_stat_activity by the same PID, and watched it disappear.
• ps output on a PostgreSQL host is informative. The process title carries the user, database, client and current command.
• Buffer Blocks in shared memory is shared_buffers, and the segment is roughly 20 MB larger for coordination structures, some of which exist only from PostgreSQL 18.
• pg_settings.context predicts whether a reload will work, and pending_restart confirms whether it did. Both ALTER SYSTEM commands succeeded; only one took effect.
• pending_restart means the file and the running value disagree, not that a restart is mandatory — withdrawing the change cleared it without one.
• postgresql.auto.conf is read last and overrides postgresql.conf, which explains a large share of “my edit had no effect” reports.