OnCallReady

Lesson 25.2 · PostgreSQL Operations · 44 min read

Install: clusters, files, the service and settings

In plain words

Installing PostgreSQL on Ubuntu is like moving into a furnished flat: the package does not just hand you the furniture, it sets up a first room (the 18/main cluster), plugs in the lights (the systemd unit) and leaves the keys with one person, the postgres user.

Changing settings is like changing house rules. Some you can announce at dinner and everyone follows at once (a reload); others only take effect after everybody leaves and comes back in (a restart). Knowing which is which saves you from wondering why nothing changed.

Why this lesson exists

Half of the PostgreSQL incidents an SRE handles are not about SQL at all: the service did not start, a setting change "did nothing", the log is somewhere unexpected, a config file was edited in the wrong place, a restart was done when a reload would have been enough (or the other way round). Ubuntu wraps PostgreSQL in its own tooling - postgresql-common - and you need to know exactly where everything is and which knob does what.

What you need to know already: apt install and package names, systemd units, template units ([email protected]) and journalctl -u (the systemd chapter), file permissions.

The words you need first

Installing

$ sudo apt install -y postgresql
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Installing:
  postgresql

Installing dependencies:
  libcommon-sense-perl  libpq5                    postgresql-client-18
  libio-pty-perl        libtime-duration-perl     postgresql-client-common
  libipc-run-perl       libtimedate-perl          postgresql-common
  libjson-perl          libtypes-serialiser-perl  postgresql-common-dev
  libjson-xs-perl       libxslt1.1                ssl-cert
  libllvm20             postgresql-18

Summary:
  Upgrading: 0, Installing: 18, Removing: 0, Not Upgrading: 7
  Download size: 17.9 MB
  Space needed: 73.4 MB / 10.7 GB available

Get:1 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 postgresql-18 arm64 18.6-0ubuntu0.26.04.1 [994 kB]
Get:2 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 postgresql-client-18 arm64 18.6-0ubuntu0.26.04.1 [994 kB]
Get:3 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 postgresql-client-common arm64 290ubuntu1 [994 kB]
Get:4 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libpq5 arm64 18.6-0ubuntu0.26.04.1 [994 kB]
Get:5 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 postgresql-common arm64 290ubuntu1 [994 kB]
Get:6 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 ssl-cert arm64 1.1.3ubuntu1 [994 kB]
Get:7 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libllvm20 arm64 1:20.1.8-0ubuntu4 [994 kB]
Get:8 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 postgresql-common-dev arm64 290ubuntu1 [994 kB]
Get:9 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libjson-perl arm64 4.10000-1 [994 kB]
Get:10 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libcommon-sense-perl arm64 3.75-3build3 [994 kB]
Get:11 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libjson-xs-perl arm64 4.040-1 [994 kB]
Get:12 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libtypes-serialiser-perl arm64 1.01-1 [994 kB]
Get:13 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libxslt1.1 arm64 1.1.43-0.1ubuntu1 [994 kB]
Get:14 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libio-pty-perl arm64 1:1.20-1build3 [994 kB]
Get:15 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libipc-run-perl arm64 20231003.0-2 [994 kB]
Get:16 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libtime-duration-perl arm64 1.21-2 [994 kB]
Get:17 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 libtimedate-perl arm64 2.3300-2 [994 kB]
Get:18 http://ports.ubuntu.com/ubuntu-ports resolute/main arm64 postgresql arm64 18+290ubuntu1 [995 kB]
Fetched 17.9 MB in 1s (14.3 MB/s)
(Reading database ... 118422 files and directories currently installed.)
Selecting previously unselected package postgresql-18:arm64.
Preparing to unpack .../postgresql-18_18.6-0ubuntu0.26.04.1_arm64.deb ...
Unpacking postgresql-18:arm64 (18.6-0ubuntu0.26.04.1) ...
Selecting previously unselected package postgresql-client-18:arm64.
Preparing to unpack .../postgresql-client-18_18.6-0ubuntu0.26.04.1_arm64.deb ...
Unpacking postgresql-client-18:arm64 (18.6-0ubuntu0.26.04.1) ...
Selecting previously unselected package postgresql-client-common:arm64.
Preparing to unpack .../postgresql-client-common_290ubuntu1_arm64.deb ...
Unpacking postgresql-client-common:arm64 (290ubuntu1) ...
Selecting previously unselected package libpq5:arm64.
Preparing to unpack .../libpq5_18.6-0ubuntu0.26.04.1_arm64.deb ...
Unpacking libpq5:arm64 (18.6-0ubuntu0.26.04.1) ...
Selecting previously unselected package postgresql-common:arm64.
Preparing to unpack .../postgresql-common_290ubuntu1_arm64.deb ...
Unpacking postgresql-common:arm64 (290ubuntu1) ...
Selecting previously unselected package ssl-cert:arm64.
Preparing to unpack .../ssl-cert_1.1.3ubuntu1_arm64.deb ...
Unpacking ssl-cert:arm64 (1.1.3ubuntu1) ...
Selecting previously unselected package libllvm20:arm64.
Preparing to unpack .../libllvm20_1:20.1.8-0ubuntu4_arm64.deb ...
Unpacking libllvm20:arm64 (1:20.1.8-0ubuntu4) ...
Selecting previously unselected package postgresql-common-dev:arm64.
Preparing to unpack .../postgresql-common-dev_290ubuntu1_arm64.deb ...
Unpacking postgresql-common-dev:arm64 (290ubuntu1) ...
Selecting previously unselected package libjson-perl:arm64.
Preparing to unpack .../libjson-perl_4.10000-1_arm64.deb ...
Unpacking libjson-perl:arm64 (4.10000-1) ...
Selecting previously unselected package libcommon-sense-perl:arm64.
Preparing to unpack .../libcommon-sense-perl_3.75-3build3_arm64.deb ...
Unpacking libcommon-sense-perl:arm64 (3.75-3build3) ...
Selecting previously unselected package libjson-xs-perl:arm64.
Preparing to unpack .../libjson-xs-perl_4.040-1_arm64.deb ...
Unpacking libjson-xs-perl:arm64 (4.040-1) ...
Selecting previously unselected package libtypes-serialiser-perl:arm64.
Preparing to unpack .../libtypes-serialiser-perl_1.01-1_arm64.deb ...
Unpacking libtypes-serialiser-perl:arm64 (1.01-1) ...
Selecting previously unselected package libxslt1.1:arm64.
Preparing to unpack .../libxslt1.1_1.1.43-0.1ubuntu1_arm64.deb ...
Unpacking libxslt1.1:arm64 (1.1.43-0.1ubuntu1) ...
Selecting previously unselected package libio-pty-perl:arm64.
Preparing to unpack .../libio-pty-perl_1:1.20-1build3_arm64.deb ...
Unpacking libio-pty-perl:arm64 (1:1.20-1build3) ...
Selecting previously unselected package libipc-run-perl:arm64.
Preparing to unpack .../libipc-run-perl_20231003.0-2_arm64.deb ...
Unpacking libipc-run-perl:arm64 (20231003.0-2) ...
Selecting previously unselected package libtime-duration-perl:arm64.
Preparing to unpack .../libtime-duration-perl_1.21-2_arm64.deb ...
Unpacking libtime-duration-perl:arm64 (1.21-2) ...
Selecting previously unselected package libtimedate-perl:arm64.
Preparing to unpack .../libtimedate-perl_2.3300-2_arm64.deb ...
Unpacking libtimedate-perl:arm64 (2.3300-2) ...
Selecting previously unselected package postgresql:arm64.
Preparing to unpack .../postgresql_18+290ubuntu1_arm64.deb ...
Unpacking postgresql:arm64 (18+290ubuntu1) ...
Setting up postgresql-18:arm64 (18.6-0ubuntu0.26.04.1) ...
Setting up postgresql-client-18:arm64 (18.6-0ubuntu0.26.04.1) ...
Setting up postgresql-client-common:arm64 (290ubuntu1) ...
Setting up libpq5:arm64 (18.6-0ubuntu0.26.04.1) ...
Setting up postgresql-common:arm64 (290ubuntu1) ...
Setting up ssl-cert:arm64 (1.1.3ubuntu1) ...
Setting up libllvm20:arm64 (1:20.1.8-0ubuntu4) ...
Setting up postgresql-common-dev:arm64 (290ubuntu1) ...
Setting up libjson-perl:arm64 (4.10000-1) ...
Setting up libcommon-sense-perl:arm64 (3.75-3build3) ...
Setting up libjson-xs-perl:arm64 (4.040-1) ...
Setting up libtypes-serialiser-perl:arm64 (1.01-1) ...
Setting up libxslt1.1:arm64 (1.1.43-0.1ubuntu1) ...
Setting up libio-pty-perl:arm64 (1:1.20-1build3) ...
Setting up libipc-run-perl:arm64 (20231003.0-2) ...
Setting up libtime-duration-perl:arm64 (1.21-2) ...
Setting up libtimedate-perl:arm64 (2.3300-2) ...
Setting up postgresql:arm64 (18+290ubuntu1) ...
Processing triggers for man-db (2.13.1-1) ...
Creating new PostgreSQL cluster 18/main ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

fixing permissions on existing directory /var/lib/postgresql/18/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Read the end of that output: the package did not just install binaries. Ubuntu's postgresql-common created a cluster called 18/main (it ran initdb for you - note --auth-local peer --auth-host scram-sha-256 and "Data page checksums are enabled", the 18 default) and started it. postgresql itself is a metapackage that pulls in the current version, postgresql-18, and the client tools, postgresql-client-18.

$ 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

That one line is the inventory: version, cluster name, port, status (online, down, online,recovery for a replica), the OS user that owns it, the data directory and the log file. A box can have 18/main on 5432 and 18/replica on 5433 or 17/main next to 18/main during an upgrade; each gets the next free port.

The service

$ systemctl status postgresql@18-main --no-pager
● [email protected] - PostgreSQL Cluster 18-main
     Loaded: loaded (/usr/lib/systemd/system/[email protected]; enabled; preset: enabled)
     Active: active (running) since Tue 2026-09-22 20:00:12 UTC; 500ms ago
 Invocation: 6371a34b7dd38089c96fec729d761603
   Main PID: 17387 (postgres)
      Tasks: 9 (limit: 4583)
     Memory: 90.5M (peak: 90.5M)
        CPU: 3ms
     CGroup: /system.slice/[email protected]
             ├─17387 /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main -c config_file=/etc/postgresql/…
             ├─17388 postgres: 18/main: io worker 0
             ├─17389 postgres: 18/main: io worker 1
             ├─17390 postgres: 18/main: io worker 2
             ├─17391 postgres: 18/main: checkpointer
             ├─17392 postgres: 18/main: background writer
             ├─17393 postgres: 18/main: walwriter
             ├─17394 postgres: 18/main: autovacuum launcher
             └─17395 postgres: 18/main: logical replication launcher

Sep 22 20:00:12 oncall-lab systemd[1]: Started [email protected] - PostgreSQL Cluster 18-main.

One unit per cluster, from the template [email protected]; the instance name is version-cluster. The cgroup shows the whole process family from the last lesson. There is also a plain postgresql.service:

$ systemctl status postgresql --no-pager
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Tue 2026-09-22 20:00:12 UTC; 750ms ago
 Invocation: 01d62eab291bd03b3db52fe6458435b8
    Process: 17396 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
        CPU: 3ms

Sep 22 20:00:12 oncall-lab systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
Sep 22 20:00:12 oncall-lab systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.

It is an umbrella (Type=oneshot, /bin/true): systemctl restart postgresql restarts every cluster on the box, because each postgresql@*.service is PartOf=postgresql.service. On a box with a primary and a replica that is rarely what you want - name the cluster.

$ systemctl cat postgresql@18-main --no-pager
# /usr/lib/systemd/system/[email protected]
# systemd service template for PostgreSQL clusters. The actual instances will
# be called "postgresql@version-cluster", e.g. "[email protected]". The
# variable %i expands to "version-cluster", %I expands to "version/cluster".
# (%I breaks for cluster names containing dashes.)

[Unit]
Description=PostgreSQL Cluster %i
AssertPathExists=/etc/postgresql/%I/postgresql.conf
RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I
PartOf=postgresql.service
ReloadPropagatedFrom=postgresql.service
Before=postgresql.service
# stop server before networking goes down on shutdown
After=network.target

[Service]
Type=forking
# -: ignore startup failure (recovery might take arbitrarily long)
# the actual pg_ctl timeout is configured in pg_ctl.conf
ExecStart=-/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start
# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0
ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop
TimeoutStopSec=1h
ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload
PIDFile=/run/postgresql/%i.pid
SyslogIdentifier=postgresql@%i
# prevent OOM killer from choosing the postmaster (individual backends will
# reset the score to 0)
OOMScoreAdjust=-900
# restarting automatically will prevent "pg_ctlcluster ... stop" from working,
# so we disable it here. Also, the postmaster will restart by itself on most
# problems anyway, so it is questionable if one wants to enable external
# automatic restarts.
#Restart=on-failure
# (This should make pg_ctlcluster stop work, but doesn't:)
#RestartPreventExitStatus=SIGINT SIGTERM

[Install]
WantedBy=multi-user.target

Points worth knowing:

The cluster's own commands do the same things and work as postgres too:

$ sudo pg_ctlcluster 18 main status
pg_ctl: server is running (PID: 17387)
/usr/lib/postgresql/18/bin/postgres "-D" "/var/lib/postgresql/18/main" "-c" "config_file=/etc/postgresql/18/main/postgresql.conf"

(As root, pg_ctlcluster 18 main restart hands over to systemctl, so the unit's state stays right.)

Where everything is

$ ls -l /etc/postgresql/18/main
total 44
drwxr-xr-x 2 postgres postgres  4096 Sep 22 20:00 conf.d
-rw-r--r-- 1 postgres postgres   315 Sep 22 20:00 environment
-rw-r--r-- 1 postgres postgres   143 Sep 22 20:00 pg_ctl.conf
-rw-r----- 1 postgres postgres  5934 Sep 22 20:00 pg_hba.conf
-rw-r----- 1 postgres postgres   857 Sep 22 20:00 pg_ident.conf
-rw-r--r-- 1 postgres postgres 14337 Sep 22 20:00 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Sep 22 20:00 start.conf
filewhat
postgresql.confthe main config file, ~800 lines, almost all commented defaults
conf.d/included at the end of postgresql.conf (include_dir = 'conf.d'): put your own settings in a file here and leave postgresql.conf as shipped
pg_hba.confwho may connect from where, and how they authenticate (lesson 4)
pg_ident.confmaps OS user names to database roles for peer/ident auth
start.confauto (start at boot), manual (only by hand), disabled
pg_ctl.confoptions pg_ctl gets from pg_ctlcluster
environmentenvironment variables for the server process
$ sudo ls -l /var/lib/postgresql/18/main
total 80
-rw------- 1 postgres postgres    3 Sep 22 20:00 PG_VERSION
drwx------ 5 postgres postgres 4096 Sep 22 20:00 base
drwx------ 2 postgres postgres 4096 Sep 22 20:00 global
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_commit_ts
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_dynshmem
drwx------ 4 postgres postgres 4096 Sep 22 20:00 pg_logical
drwx------ 4 postgres postgres 4096 Sep 22 20:00 pg_multixact
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_notify
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_replslot
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_serial
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_snapshots
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_stat
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_subtrans
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_tblspc
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_twophase
drwx------ 4 postgres postgres 4096 Sep 22 20:00 pg_wal
drwx------ 2 postgres postgres 4096 Sep 22 20:00 pg_xact
-rw------- 1 postgres postgres   88 Sep 22 20:00 postgresql.auto.conf
-rw------- 1 postgres postgres  103 Sep 22 20:00 postmaster.pid

The data directory. Never edit or delete anything in it by hand while the server runs - and never delete files in pg_wal/ to free space: they are the server's journal, and a missing segment can make the cluster unstartable. The other two things you look at in here: postgresql.auto.conf (written by ALTER SYSTEM, below) and postmaster.pid (the PID, data directory, port and socket of the running server).

The log:

$ sudo tail -n 6 /var/log/postgresql/postgresql-18-main.log
2026-09-22 20:00:10.730 UTC [17387] LOG:  starting PostgreSQL 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 15.2.0-4ubuntu4) 15.2.0, 64-bit
2026-09-22 20:00:10.730 UTC [17387] LOG:  listening on IPv6 address "::1", port 5432
2026-09-22 20:00:10.730 UTC [17387] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-09-22 20:00:10.730 UTC [17387] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-09-22 20:00:10.730 UTC [17387] LOG:  database system was shut down at 2026-09-22 20:00:10 UTC
2026-09-22 20:00:12.480 UTC [17387] LOG:  database system is ready to accept connections

Ubuntu sets log_line_prefix = '%m [%p] %q%u@%d ': timestamp with milliseconds (%m), PID (%p), and for client sessions user@database (%q hides the rest for background processes). Then the level - LOG, WARNING, ERROR, FATAL, PANIC - two spaces and the message. An ERROR ends one statement; a FATAL ends one session (failed logins are FATAL); a PANIC stops the whole server.

Ask the server itself where its files are - this works on any distribution:

$ sudo -u postgres psql -c 'show data_directory' -c 'show config_file' -c 'show hba_file'
       data_directory
-----------------------------
 /var/lib/postgresql/18/main
(1 row)

               config_file
-----------------------------------------
 /etc/postgresql/18/main/postgresql.conf
(1 row)

              hba_file
-------------------------------------
 /etc/postgresql/18/main/pg_hba.conf
(1 row)

Getting in: peer authentication

$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "learner" does not exist

Two things happened. psql with no options connects over the Unix socket (/var/run/postgresql/.s.PGSQL.5432) as a role named like your OS user, learner, to a database of the same name. And the first pg_hba.conf rule for local connections says peer: the server asked the kernel who you are (learner), and there is no role learner. The superuser role is postgres, and only the OS user postgres passes peer authentication as it:

$ sudo -u postgres psql -c 'select current_user, session_user'
 current_user | session_user
--------------+--------------
 postgres     | postgres
(1 row)
$ sudo grep -v '^\s*#' /etc/postgresql/18/main/pg_hba.conf | grep -v '^$'
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

sudo -u postgres psql is how you get a superuser shell on a server you administer. The postgres OS account has no password and no login shell use; you always go through sudo. TCP connections from 127.0.0.1 need a password (scram-sha-256) - lesson 4 builds proper roles for the apps.

Settings: where they come from and when they apply

Every setting has a value, a source and a context:

$ sudo -u postgres psql -c "select name, setting, unit, context from pg_settings where name in ('max_connections','shared_buffers','work_mem','listen_addresses','log_min_duration_statement') order by name"
            name            |  setting  | unit |  context
----------------------------+-----------+------+------------
 listen_addresses           | localhost |      | postmaster
 log_min_duration_statement | -1        | ms   | superuser
 max_connections            | 100       |      | postmaster
 shared_buffers             | 16384     | 8kB  | postmaster
 work_mem                   | 4096      | kB   | user
(5 rows)
contexttakes effectexamples
postmasteronly at server start: restartmax_connections, shared_buffers, listen_addresses, port, wal_level, archive_mode, shared_preload_libraries
sighupon reload, for every sessionpg_hba.conf, log_*, archive_command, autovacuum_*, max_wal_size
superuser / useron reload, and a session can also SET it for itselfwork_mem, statement_timeout, lock_timeout, log_min_duration_statement
internalnever: fixed when the server was builtblock_size, server_version

Note shared_buffers reads 16384 with unit 8kB: 16384 x 8 kB = 128 MB. Many memory settings are stored in pages; SHOW shared_buffers prints them human-readable.

Where a value comes from, later wins: built-in default -> postgresql.conf (and the conf.d/ files it includes, in name order) -> postgresql.auto.conf -> per database or per role (ALTER DATABASE ... SET, ALTER ROLE ... SET) -> the session's own SET.

ALTER SYSTEM writes postgresql.auto.conf for you, so you can change settings from SQL without editing files:

$ sudo -u postgres psql -c "alter system set work_mem = '16MB'"
ALTER SYSTEM
$ sudo -u postgres psql -c "select pg_reload_conf()"
 pg_reload_conf
----------------
 t
(1 row)
$ sudo tail -n 2 /var/log/postgresql/postgresql-18-main.log
2026-09-22 20:00:16.280 UTC [17387] LOG:  received SIGHUP, reloading configuration files
2026-09-22 20:00:16.280 UTC [17387] LOG:  parameter "work_mem" changed to "16MB"

Reloaded, applied, logged. Now a postmaster setting the same way:

$ sudo -u postgres psql -c "alter system set max_connections = 200"
ALTER SYSTEM
$ sudo systemctl reload postgresql@18-main
$ sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log
2026-09-22 20:00:17.030 UTC [17387] LOG:  received SIGHUP, reloading configuration files
2026-09-22 20:00:17.030 UTC [17387] LOG:  parameter "max_connections" cannot be changed without restarting the server
2026-09-22 20:00:17.030 UTC [17387] LOG:  configuration file "/var/lib/postgresql/18/main/postgresql.auto.conf" contains errors; unaffected changes were applied
$ sudo -u postgres psql -c "select name, setting, pending_restart from pg_settings where pending_restart"
      name       | setting | pending_restart
-----------------+---------+-----------------
 max_connections | 100     | t
(1 row)

The reload did not fail - it applied what it could and logged that max_connections "cannot be changed without restarting the server". The server keeps running with the old value, and pending_restart is true until you restart. This is the classic "I changed it and nothing happened". The check after any config change is always the same pair: the log lines of the reload, and pending_restart.

$ sudo systemctl restart postgresql@18-main
$ sudo -u postgres psql -Atc "show max_connections"
200

pg_file_settings shows what the files say, line by line, and whether each was applied - the fastest way to find a typo or a value overridden somewhere else:

$ sudo -u postgres psql -c "select sourcefile, sourceline, name, setting, applied from pg_file_settings where name in ('work_mem','max_connections')"
                    sourcefile                    | sourceline |      name       | setting | applied
--------------------------------------------------+------------+-----------------+---------+---------
 /etc/postgresql/18/main/postgresql.conf          |         65 | max_connections | 100     | f
 /var/lib/postgresql/18/main/postgresql.auto.conf |          3 | work_mem        | 16MB    | t
 /var/lib/postgresql/18/main/postgresql.auto.conf |          4 | max_connections | 200     | t
(3 rows)

The first max_connections (from postgresql.conf) is applied = f because a later file sets it again. Put things back the way they were:

$ sudo -u postgres psql -c "alter system reset all"
ALTER SYSTEM
$ sudo systemctl restart postgresql@18-main

ALTER SYSTEM RESET ALL empties postgresql.auto.conf. Which way to manage config on a real server is a team choice: files from configuration management (Ansible writes a file in conf.d/) or ALTER SYSTEM from SQL. Mixing both is how a setting "comes back" after someone fixes it in the other place - pg_file_settings shows you both.

In an interview: "You changed a PostgreSQL setting and nothing happened - why?" - its context may be postmaster (needs a restart, a reload only sets pending_restart and logs "cannot be changed without restarting the server"), or a later source overrides it (postgresql.auto.conf beats postgresql.conf; pg_file_settings shows which line won), or the reload never happened.

What you can do now

Why it helps

A large share of database pages are not about SQL: the service did not start, a setting change "did nothing", someone edited the wrong file, or a restart was done in the middle of the day when a reload would have been enough. Ubuntu's postgresql-common puts things in specific places with specific tools, and you need to know them cold.

The reload-versus-restart rule (a setting's context) and the check after every change - the reload's log lines and pending_restart - prevent a whole class of quiet failures.

Commands in this lesson

apt pg_lsclusters systemctl pg_ctlcluster ls tail psql grep

FAQ

Why are the config files in /etc and the data in /var/lib?

That is Debian and Ubuntu's layout, managed by postgresql-common: configuration in /etc/postgresql/18/main, data in /var/lib/postgresql/18/main, logs in /var/log/postgresql. Other distributions keep postgresql.conf inside the data directory. SHOW config_file and SHOW data_directory answer the question on any system.

What is the difference between postgresql.service and [email protected]?

postgresql@18-main is the real unit for one cluster, an instance of the [email protected] template. postgresql.service is an umbrella that starts and stops every cluster on the machine at once; restarting it restarts all of them. Name the cluster you mean, especially when a primary and a replica share a host.

Why does psql on its own say role "learner" does not exist?

With no options psql connects over the Unix socket as a role named like your OS user. The local rule in pg_hba.conf is peer authentication, which matches the OS user to the role of the same name, and there is no role learner. sudo -u postgres psql works because the OS user postgres matches the superuser role postgres.

Should I use ALTER SYSTEM or edit files?

Either works: ALTER SYSTEM writes postgresql.auto.conf, which is read last and wins, while config management usually writes a file in conf.d. Pick one per team. Mixing them is how a setting comes back after someone fixed it elsewhere; pg_file_settings shows every file and line and which one was applied.

How do I know whether a reload applied my change?

Read the log lines of the reload: "parameter ... changed to ..." for applied ones, "cannot be changed without restarting the server" for postmaster settings, and "contains errors" for typos. Then query pg_settings for pending_restart. Only a postmaster setting needs a restart, and a restart drops every connection.

In an interview Junior

You changed a PostgreSQL setting and nothing happened. Why might that be?

Check the setting's context in pg_settings: a postmaster setting such as max_connections or shared_buffers only applies after a restart - a reload logs "cannot be changed without restarting the server" and sets pending_restart. If it is a reloadable setting, something else may override it: postgresql.auto.conf (from ALTER SYSTEM) is read after postgresql.conf, a later file in conf.d wins, or a role or database has its own SET. pg_file_settings shows each file and line and whether it applied. Finally, confirm the reload actually happened by reading the server log.

Also asked: Where are the PostgreSQL configuration files and logs on Ubuntu? · What is peer authentication and why does sudo -u postgres psql work? · What is the difference between a fast and an immediate shutdown?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.