Lab 1: Install PostgreSQL 18 from PGDG and inspect what the packaging built

Objective

By the end of this lab you will have installed PostgreSQL 18 the way most Debian and Ubuntu production hosts install it — from the PGDG apt repository — and then established, from the server itself rather than from documentation, where every file that matters lives.

The habit this lab builds is small and it pays for itself in the first incident you handle. When somebody says “the config file”, there are at least three plausible answers on a typical host, and the difference between them is the difference between editing a file that is read and editing a file that is ignored. You will finish knowing how to settle that question in one query, on any host, in any packaging.

The second half of the lab looks at what initdb decided permanently. Some cluster properties are chosen once, written into pg_control, and cannot be changed later without dumping and reloading the whole cluster. Knowing which ones those are is what stops a locale or block size decision from becoming a migration project two years later.

Everything runs inside one throwaway container. Nothing is installed on your host.

Architecture

One Debian 13 container in which you install PostgreSQL from PGDG, and one already-running postgres:18 container used purely as a contrast case in Task 6. No ports are published; all work happens through docker exec.

flowchart LR
    H["Your host\ndocker exec"] --> D["rbpg-lab01\ndebian:13"]
    H --> K["rbpg-lab01-docker\npostgres:18 official image"]
    D --> R["PGDG apt repo\napt.postgresql.org"]
    D --> C1["cluster 18/main\nport 5432"]
    D --> C2["cluster 18/reporting\nport 5433"]
    C1 --> E1["config: /etc/postgresql/18/main"]
    C1 --> E2["data: /var/lib/postgresql/18/main"]
    K --> E3["config AND data both in\n/var/lib/postgresql/18/docker"]

Requirements

• Docker with permission to run containers.
• Outbound HTTPS to apt.postgresql.org and deb.debian.org. The lab downloads roughly 120 MB of packages.
• No published ports. Both containers are reached only through docker exec.
• Names used: two containers, rbpg-lab01 and rbpg-lab01-docker. Nothing else on your host is created, read or removed.

Scenario

You have been asked to build a PostgreSQL 18 host. The team’s existing runbook says “install postgresql and configure it”, which is the level of detail that produces a server running a version nobody chose, with configuration in a directory nobody can find, and a data directory on whichever filesystem happened to be mounted at /var.

You are going to do it deliberately, and record what the packaging decided on your behalf so the next person does not have to guess.

Tasks

Task 1 — Record the starting state and create the container

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

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

docker rm -f rbpg-lab01 2>/dev/null || true
docker run -d --name rbpg-lab01 debian:13 sleep infinity

docker exec rbpg-lab01 cat /etc/os-release | head -3

$ docker exec rbpg-lab01 cat /etc/os-release | head -3

PRETTY_NAME="Debian GNU/Linux 13 (trixie)"
NAME="Debian GNU/Linux"
VERSION_ID="13"

The sleep infinity entrypoint is deliberate. It means the container stays up regardless of what PostgreSQL is doing, so you control the database lifecycle explicitly instead of having the container exit when a server stops. That will matter in later labs where you deliberately stop and start clusters.

Task 2 — Establish why the distribution repository is not enough

Install the prerequisites, then ask apt what it would give you if you simply typed apt install postgresql.

docker exec rbpg-lab01 bash -c '
  apt-get update -qq
  apt-get install -y -qq curl ca-certificates gnupg lsb-release procps
'

docker exec rbpg-lab01 apt-cache policy postgresql | tee "$LAB/repo-evidence.txt"

$ docker exec rbpg-lab01 apt-cache policy postgresql

postgresql:
Installed: (none)
Candidate: 17+278
Version table:
   17+278 500
      500 http://deb.debian.org/debian trixie/main amd64 Packages

This is the first decision the lab makes visible. A Debian 13 host that installs postgresql from the distribution gets PostgreSQL 17. That is a perfectly supportable choice — Debian maintains it for the life of the release — but it is a choice, and it is one that the shortest possible install command makes silently on your behalf.

Task 3 — Add the PGDG repository

The repository key goes into its own file and is referenced by signed-by in the source entry. Do not use apt-key; it has been deprecated for years and puts a key that signs one repository into a trust store consulted for all of them.

docker exec rbpg-lab01 bash -c '
  install -d /usr/share/postgresql-common/pgdg
  curl -fsS -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
    https://www.postgresql.org/media/keys/ACCC4CF8.asc

  echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt trixie-pgdg main 18" \
    > /etc/apt/sources.list.d/pgdg.list

  apt-get update -qq
'

docker exec rbpg-lab01 apt-cache policy postgresql-18 | tee -a "$LAB/repo-evidence.txt"

$ docker exec rbpg-lab01 apt-cache policy postgresql-18

postgresql-18:
Installed: (none)
Candidate: 18.6-1.pgdg13+2
Version table:
   18.6-1.pgdg13+2 500
      500 https://apt.postgresql.org/pub/repos/apt trixie-pgdg/main amd64 Packages
   18.4-1.pgdg13+1 500
      500 https://apt.postgresql.org/pub/repos/apt trixie-pgdg/main amd64 Packages

Two things in that output are worth pausing on.

The trailing 18 in the source line is a component selector. Writing main 18 subscribes the host to the PostgreSQL 18 packages only. Writing main alone subscribes it to every supported major version at once, which is convenient on a workstation and a liability on a production host, because it makes apt upgrade capable of pulling in a major version you did not plan for.

The version table lists more than one 18.x release. PGDG keeps previous minor versions available, which is what makes a pinned, reproducible build possible: apt-get install postgresql-18=18.6-1.pgdg13+2 installs exactly that minor version on every host, rather than “whatever was newest the day each host was built”.

Task 4 — Install and see what the package created

docker exec rbpg-lab01 bash -c '
  DEBIAN_FRONTEND=noninteractive apt-get install -y postgresql-18
'

docker exec rbpg-lab01 pg_lsclusters | tee "$LAB/cluster-registry.txt"

$ docker exec rbpg-lab01 pg_lsclusters

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

The Debian packaging did something the upstream tarball does not: it ran initdb for you and registered the result in a cluster registry. pg_lsclusters reads that registry, and it is the fastest way to answer “what is on this host” on any Debian or Ubuntu machine.

Status is down because the maintainer script tried to start the cluster through systemd, and there is no systemd in this container. On a real host that line would read online. Start it explicitly:

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

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

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

Task 5 — Ask the server where its own files are

This is the technique the whole lab exists to teach. Do not look for the configuration file. Ask the process that read it.

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting
  FROM pg_settings
  WHERE name IN ('data_directory','config_file','hba_file',
                 'ident_file','external_pid_file')
  ORDER BY name;" | tee "$LAB/file-locations.txt"

$ psql -X -c "SELECT name, setting FROM pg_settings WHERE name IN ('data_directory','config_file','hba_file','ident_file','external_pid_file') ORDER BY name;"

       name        |                  setting                  
-------------------+-------------------------------------------
config_file       | /etc/postgresql/18/main/postgresql.conf
data_directory    | /var/lib/postgresql/18/main
external_pid_file | /var/run/postgresql/18-main.pid
hba_file          | /etc/postgresql/18/main/pg_hba.conf
ident_file        | /etc/postgresql/18/main/pg_ident.conf
(5 rows)

These five settings are read-only in the sense that matters here: they report what this postmaster actually used at startup. If somebody edited a postgresql.conf that is not the one named in config_file, this query is how you find out, and it takes one round trip rather than an afternoon.

Now confirm the separation is real and not a set of symlinks:

docker exec rbpg-lab01 bash -c '
  echo -n "config files inside PGDATA: "
  find /var/lib/postgresql/18/main -maxdepth 1 \
       \( -name postgresql.conf -o -name pg_hba.conf \) | wc -l
  echo -n "symlinks inside PGDATA:     "
  find /var/lib/postgresql/18/main -maxdepth 1 -type l | wc -l
'

$ find /var/lib/postgresql/18/main -maxdepth 1 -type l | wc -l

config files inside PGDATA: 0
symlinks inside PGDATA:     0

Zero and zero. On a Debian-packaged cluster the configuration is not in the data directory and there is no link pointing at it. This matters more than it looks: a pg_basebackup of that data directory copies no configuration at all, because there is none there to copy.

Task 6 — Contrast with the Docker official image

Start a postgres:18 container and run the same query. Same PostgreSQL version, different packaging, different answer.

docker rm -f rbpg-lab01-docker 2>/dev/null || true
docker run -d --name rbpg-lab01-docker \
  -e POSTGRES_PASSWORD=lab-password-not-a-real-secret \
  postgres:18

for i in $(seq 1 40); do
  docker exec rbpg-lab01-docker pg_isready -U postgres >/dev/null 2>&1 && break
  sleep 1
done

docker exec rbpg-lab01-docker psql -U postgres -X -c "
  SELECT name, setting FROM pg_settings
  WHERE name IN ('data_directory','config_file','hba_file')
  ORDER BY name;" | tee -a "$LAB/file-locations.txt"

$ docker exec rbpg-lab01-docker psql -U postgres -X -c "SELECT name, setting FROM pg_settings WHERE name IN ('data_directory','config_file','hba_file') ORDER BY name;"

      name      |                    setting                    
----------------+-----------------------------------------------
config_file    | /var/lib/postgresql/18/docker/postgresql.conf
data_directory | /var/lib/postgresql/18/docker
hba_file       | /var/lib/postgresql/18/docker/pg_hba.conf
(3 rows)

Same version, same query, an entirely different set of paths. The official image runs initdb in the upstream way and leaves the configuration inside the data directory, and it names the cluster directory docker rather than main.

This is why “edit /etc/postgresql/18/main/postgresql.conf” is bad runbook text. It is correct on one of these two hosts and wrong on the other, and both are running PostgreSQL 18.6.

Task 7 — Read what initdb decided permanently

docker exec -u postgres rbpg-lab01 \
  /usr/lib/postgresql/18/bin/pg_controldata -D /var/lib/postgresql/18/main \
  | grep -iE "system identifier|cluster state|block size|checksum|alignment|Float8|identifiers" \
  | tee "$LAB/immutable-facts.txt"

$ pg_controldata -D /var/lib/postgresql/18/main

Database system identifier:           7678867844923653100
Database cluster state:               in production
Maximum data alignment:               8
Database block size:                  8192
WAL block size:                       8192
Maximum length of identifiers:        64
Float8 argument passing:              by value
Data page checksum version:           1

Everything in that list except Database cluster state was decided when initdb ran and cannot be changed on this cluster afterwards. Changing the block size means a new cluster and a dump-and-reload. So does changing the identifier length. These are compile-time and initdb-time properties, not parameters.

Data page checksum version: 1 is the one to notice. Data checksums are on, and that is new: PostgreSQL 18 enables them by default, where every earlier version defaulted to off. Confirm from the server:

docker exec -u postgres rbpg-lab01 psql -X -c "SHOW data_checksums;"
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate
  FROM pg_database ORDER BY datname;"

$ psql -X -c "SHOW data_checksums;" and a query over pg_database

 data_checksums 
----------------
on
(1 row)

datname  | encoding | datcollate 
-----------+----------+------------
postgres  | UTF8     | C.UTF-8
template0 | UTF8     | C.UTF-8
template1 | UTF8     | C.UTF-8
(3 rows)

The encoding and collation came from the container’s locale environment. On a host with a different locale you would get a different answer, silently, and comparison and sort order in every database created from template1 would follow it.

Task 8 — Prove the registry is not a singleton

One host can run several clusters, on different ports, with different data directories and completely separate contents.

docker exec rbpg-lab01 pg_createcluster 18 reporting
docker exec rbpg-lab01 pg_lsclusters | tee -a "$LAB/cluster-registry.txt"

$ docker exec rbpg-lab01 pg_createcluster 18 reporting && docker exec rbpg-lab01 pg_lsclusters

Ver Cluster   Port Status Owner    Data directory                   Log file
18  main      5432 online 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

pg_createcluster picked port 5433 by reading the registry and choosing the first free port. Now show that the two clusters are genuinely unrelated:

for C in main reporting; do
  docker exec -u postgres rbpg-lab01 \
    /usr/lib/postgresql/18/bin/pg_controldata -D /var/lib/postgresql/18/$C \
    | grep "system identifier"
done

$ pg_controldata on each data directory, filtered to the system identifier

Database system identifier:           7678867844923653100
Database system identifier:           7678868043681307841

Different system identifiers. That number is how PostgreSQL decides whether two data directories belong to the same cluster history, and it is checked on every attempt to build a standby or replay archived WAL. A standby whose identifier does not match its primary refuses to start, which is the correct behaviour and the reason you cannot assemble a replica out of an unrelated backup.

Finally, look at what governs automatic startup:

docker exec rbpg-lab01 grep -v '^#' /etc/postgresql/18/reporting/start.conf | grep .

$ grep -v '^#' /etc/postgresql/18/reporting/start.conf | grep .

auto

auto means the init system starts this cluster at boot. The other values are manual, which registers the cluster but leaves starting it to you, and disabled, which refuses even a manual pg_ctlcluster start. manual is the correct setting for a cluster that must not come up on its own — a standby managed by a failover tool being the obvious case, where an automatic start after a reboot is exactly how you get two servers each convinced they are the primary.

Task 9 — Check the listening surface before you finish

docker exec -u postgres rbpg-lab01 psql -X -c "SHOW listen_addresses;"
docker exec -u postgres rbpg-lab01 tail -5 /var/log/postgresql/postgresql-18-main.log

$ psql -X -c "SHOW listen_addresses;" then tail the cluster log

 listen_addresses 
------------------
localhost
(1 row)

2026-08-28 00:09:11.980 UTC [7239] LOG:  listening on IPv6 address "::1", port 5432
2026-08-28 00:09:11.980 UTC [7239] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-08-28 00:09:11.984 UTC [7239] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-08-28 00:09:11.989 UTC [7245] LOG:  database system was shut down at 2026-08-28 00:09:03 UTC
2026-08-28 00:09:11.994 UTC [7239] LOG:  database system is ready to accept connections

The Debian package ships listen_addresses = 'localhost'. A freshly installed PostgreSQL on Debian is not reachable from the network at all, which is a good default and a frequent source of “the install worked but I cannot connect” — the answer being that the install worked exactly as designed and the exposure is a separate, deliberate step.

Note the log lines: the server reports every address it bound, and it reports the shutdown time of the previous run. Those two lines are the cheapest possible confirmation that you restarted the server you thought you did.

Validation

Confirm each deliverable before you clean up.

test -s "$LAB/repo-evidence.txt"     && echo "OK repo-evidence"
test -s "$LAB/cluster-registry.txt"  && echo "OK cluster-registry"
test -s "$LAB/file-locations.txt"    && echo "OK file-locations"
test -s "$LAB/immutable-facts.txt"   && echo "OK immutable-facts"

grep -q "18.6" "$LAB/repo-evidence.txt"        && echo "OK PGDG candidate recorded"
grep -c "^18 " "$LAB/cluster-registry.txt"      # expect 3 across the two captures
grep -q "/etc/postgresql/18/main" "$LAB/file-locations.txt" && echo "OK Debian paths"
grep -q "/var/lib/postgresql/18/docker" "$LAB/file-locations.txt" && echo "OK Docker paths"
grep -q "checksum version:           1" "$LAB/immutable-facts.txt" && echo "OK checksums on"

You should be able to answer these without looking anything up:

1. Which repository supplied the installed server, and which major version would you have got without it?
2. On this host, which file does the running postmaster read as its configuration — and how did you establish that rather than assume it?
3. Name two properties in pg_controldata that cannot be changed without creating a new cluster.
4. Why does pg_lsclusters show a port of 5433 for the second cluster?
5. What would start.conf need to say for a standby that a failover tool manages, and why?

Expected Outcome

You have a Debian 13 host running PostgreSQL 18.6 installed from PGDG, with two independent clusters registered, and four files recording what the packaging chose on your behalf.

More usefully, you have a method. On any PostgreSQL host you now meet, SELECT name, setting FROM pg_settings WHERE name LIKE '%_file' OR name = 'data_directory' tells you where everything is, from the server, in one round trip — and pg_controldata tells you which of the remaining decisions are already permanent.

Troubleshooting

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

apt-get update reports NO_PUBKEY or signatures couldn't be verified for the PGDG repository. The key was not written to the path the signed-by option names, or was written as ASCII armour where a dearmoured key was expected. Re-run the key step and confirm with ls -l /usr/share/keyrings/, then apt-get update again.

apt-cache policy postgresql-18 shows no candidate. The architecture or the codename in the repository line does not match the container. dpkg --print-architecture and . /etc/os-release; echo $VERSION_CODENAME produce the two values the line must contain.

pg_lsclusters prints nothing, or the command is not found. That command belongs to postgresql-common, which the Debian packaging installs and the Docker official image does not. This is the difference Task 6 exists to show — on the official image, ask the server with SHOW data_directory instead.

pg_controldata reports could not open file "global/pg_control". It was pointed at a directory that is not a data directory, or run as a user that cannot read it. Take the path from SHOW data_directory rather than typing it, and run it as the postgres OS user.

The second cluster will not start after pg_createcluster. Read /var/log/postgresql/postgresql-18-second.log. The commonest cause is a port already in use, which the log names explicitly.

Cleanup

docker rm -f rbpg-lab01 rbpg-lab01-docker

# Confirm you removed only what the lab created.
diff <(docker ps -a --format '{{.Names}}' | sort) \
     <(grep -A100 'containers before' "$LAB/state.pre-lab" | tail -n +2 | sort)

The diff should be empty. If it is not, you removed something the lab did not create, or something the lab created is still running.

Production notes

• The one query in Task 5 is the whole method: SELECT name, setting FROM pg_settings WHERE name LIKE '%\_file' OR name = 'data_directory'. On an unfamiliar host it is faster and more reliable than looking for files, and it answers from the running server rather than from the filesystem’s opinion of what the server should be reading.
• pg_controldata is a pre-flight instrument, not a curiosity. Block size, WAL block size, locale provider, collation and checksum version are fixed at initdb and changing any of them means a dump and reload. Read them before an estate standardises on anything.
• The distribution repository and PGDG give different major versions on the same host. Whichever an estate chooses, choose it deliberately and record it, because the choice determines the patch cadence and the end-of-life date the estate inherits.
• Two clusters on one host is a supported and packaged arrangement on Debian, not a hack. start.conf is what decides whether one of them comes up at boot — which for a standby managed by a failover tool should usually be manual.

What You Learned

• The distribution repository and PGDG do not offer the same major version, and apt-cache policy is how you establish which one a host is actually installing from.
• The server can tell you where all its own files are in one query. There is no need to guess between /etc, /var/lib and a container’s own layout.
• Debian packaging and the Docker official image put things in different places, and only one of them ships pg_lsclusters. A runbook written against one is wrong against the other.
• Some initdb decisions are permanent. Block size, WAL block size, locale provider and checksum version live in pg_control and cannot be changed without recreating the cluster.
• PostgreSQL 18 turns data checksums on at initdb by default — Data page checksum version: 1 — which is a change from every earlier release and matters at upgrade time.
• A host can run several clusters, each with its own port, data directory, configuration and boot policy.