Creating a cluster: initdb, settings, ALTER SYSTEM

initdb_session.py

"""Creating a PostgreSQL 18.6 cluster with initdb, starting it with pg_ctl, and a second cluster that takes its
collation from PostgreSQL's builtin locale provider instead of the operating system."""

from pg_session import Server

with Server() as pg:
    pg.initdb(show=True)
    pg.start(show=True)
    pg.psql("checks.sql")

with Server(initdb=["--locale-provider=builtin", "--builtin-locale=C.UTF-8"]) as builtin:
    builtin.initdb(show=True)

$ initdb -D data -U postgres --auth=scram-sha-256 --pwfile=pwfile
The files belonging to this database system will be owned by user "learner".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.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.

creating directory data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D data -l logfile start

$ pg_ctl -D data -l data.log -w -t 60 start
waiting for server to start... done
server started

$ psql -X -a -f checks.sql
-- What did initdb choose? Checksums, and the locale of the databases it created.
SHOW data_checksums;
 data_checksums 
----------------
 on
(1 row)

SELECT datname, datlocprovider, datcollate, pg_encoding_to_char(encoding) AS encoding
FROM pg_database ORDER BY datname;
  datname  | datlocprovider | datcollate  | encoding 
-----------+----------------+-------------+----------
 postgres  | c              | en_US.UTF-8 | UTF8
 template0 | c              | en_US.UTF-8 | UTF8
 template1 | c              | en_US.UTF-8 | UTF8
(3 rows)

$ initdb -D data -U postgres --auth=scram-sha-256 --pwfile=pwfile --locale-provider=builtin --builtin-locale=C.UTF-8
The files belonging to this database system will be owned by user "learner".
This user must also own the server process.

The database cluster will be initialized with this locale configuration:
  locale provider:   builtin
  default collation: C.UTF-8
  LC_COLLATE:  en_US.UTF-8
  LC_CTYPE:    en_US.UTF-8
  LC_MESSAGES: en_US.UTF-8
  LC_MONETARY: en_US.UTF-8
  LC_NUMERIC:  en_US.UTF-8
  LC_TIME:     en_US.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.

creating directory data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D data -l logfile start

checks.sql

-- What did initdb choose? Checksums, and the locale of the databases it created.
SHOW data_checksums;
SELECT datname, datlocprovider, datcollate, pg_encoding_to_char(encoding) AS encoding
FROM pg_database ORDER BY datname;

pg_session.py

"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.

Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
learn:verify's run sandbox cannot read the home folder, where mise keeps PostgreSQL, so these sessions are recorded
with LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. The sessions never print the port. A session stops with status 1 when
a command fails that should have worked, or reports a different number of errors than the session expects, so a run
that went wrong is never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""

    def __init__(self, settings=None, initdb=(), name="data"):
        self.settings = dict(settings or {})
        self.initdb_args = list(initdb)
        self.name = name
        self.running = []

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        (self.base / "pwfile").write_text(self.password + "\n")
        self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
                    "PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "10"}
        for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
            self.env.pop(key, None)
        for script in Path(".").glob("*.sql"):
            shutil.copy(script, self.base / script.name)
        for data in Path(".").glob("*.csv"):
            shutil.copy(data, self.base / data.name)
        return self

    def __exit__(self, *exc):
        for folder, _port in reversed(self.running):
            self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
        self.running = []
        shutil.rmtree(self.base, ignore_errors=True)

    # ---- the cluster ---------------------------------------------------------------------------------------------
    def initdb(self, show=False):
        args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
                *self.initdb_args]
        if not show:
            args.append("--no-instructions")
        self._expect(args, show=show)
        with open(self.base / self.name / "postgresql.conf", "a") as conf:
            conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
            conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
            for key, value in self.settings.items():
                conf.write(f"{key} = {value}\n")

    def start(self, show=False, folder=None, port=None):
        folder = folder or self.name
        self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
        self.running.append((folder, port or self.port))

    def up(self):
        """initdb and start, quietly."""
        self.initdb()
        self.start()
        return self

    def standby(self, folder="standby"):
        """A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
        self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
        port = free_port()
        with open(self.base / folder / "postgresql.conf", "a") as conf:
            conf.write(f"port = {port}\n")
        self.start(folder=folder, port=port)
        for _ in range(300):
            if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
                return port
            time.sleep(0.1)
        sys.exit("the standby did not start streaming")

    # ---- running things ------------------------------------------------------------------------------------------
    def _call(self, args, env=None):
        args = [str(a) for a in args]
        done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
                              text=True, cwd=self.base, env=env or self.env)
        return done.returncode, self.clean(done.stdout)

    def _expect(self, args, show=False, status=0):
        code, out = self._call(args)
        if show:
            _show(args, out)
        if code != status:
            if not show:
                print(out)
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
        text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
        text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
        return text.replace(f'"{OS_USER}"', '"learner"')

    def wait_for(self, sql, value):
        """Waits (unprinted) until a query returns the value, for example after a reload."""
        for _ in range(300):
            if self.query(sql) == value:
                return
            time.sleep(0.1)
        sys.exit(f"{sql} never returned {value}")

    def show_file(self, path):
        """Prints a text file of the cluster, as `cat` would."""
        print(f"$ cat {path}")
        print((self.base / path).read_text().rstrip("\n"))
        print()

    def query(self, sql, port=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only to give a role the run's password)."""
        self.query(sql)

    def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None):
        """Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
        `errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        if user:
            env["PGUSER"] = user
        if db:
            env["PGDATABASE"] = db
        args = ["psql", "-X", *flags, "-f", script]
        code, out = self._call(args, env=env)
        shown = ["psql", "-X", *flags, "-f", script]
        if user:
            shown[1:1] = ["-U", user]
        if db:
            shown[1:1] = ["-d", db]
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
        expected_status = 0 if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")

    def sessions(self, count):
        """Opens `count` idle psql connections (each one a client backend) until the with block ends."""
        return _Idle(self, count)

    def shell(self, *args, status=0):
        """Runs one of PostgreSQL's programs and prints the command and its output."""
        return self._expect(list(args), show=True, status=status)

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
        print(textwrap.fill("  ".join(names), width=78, initial_indent="   ", subsequent_indent="   ",
                            break_on_hyphens=False))
        print()
        return names


def _arg(a):
    """A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()


class _Idle:
    """Idle psql connections, closed again when the with block ends."""

    def __init__(self, server, count):
        self.server, self.count, self.procs = server, count, []

    def __enter__(self):
        before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
        for _ in range(self.count):
            self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
                                               stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
                                               env=self.server.env, cwd=self.server.base))
        sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
        for _ in range(300):
            # the query's own connection is one more client backend
            if int(self.server.query(sql)) == before + self.count:
                return self
            time.sleep(0.1)
        sys.exit("the idle connections did not open")

    def __exit__(self, *exc):
        for p in self.procs:
            p.stdin.close()
            p.wait(timeout=30)

settings_session.py

"""Reading pg_settings, and setting a value for one transaction and for one session."""

from pg_session import Server

with Server() as pg:
    pg.up()
    pg.psql("settings.sql", errors=1)

$ psql -X -a -f settings.sql
-- Where does each value come from, and when can it change? context answers the second question.
SELECT name, setting, unit, context, source FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'listen_addresses', 'log_min_duration_statement',
               'work_mem', 'lock_timeout')
ORDER BY context, name;
            name            |  setting  | unit |  context   |       source       
----------------------------+-----------+------+------------+--------------------
 listen_addresses           | 127.0.0.1 |      | postmaster | configuration file
 max_connections            | 100       |      | postmaster | configuration file
 shared_buffers             | 16384     | 8kB  | postmaster | configuration file
 log_min_duration_statement | -1        | ms   | superuser  | default
 lock_timeout               | 0         | ms   | user       | default
 work_mem                   | 4096      | kB   | user       | default
(6 rows)

SELECT context, count(*) FROM pg_settings GROUP BY context ORDER BY count(*) DESC, context;
      context      | count 
-------------------+-------
 user              |   151
 sighup            |   104
 postmaster        |    69
 superuser         |    49
 internal          |    20
 superuser-backend |     4
 backend           |     2
(7 rows)

-- SET LOCAL lasts until the transaction ends; SET until the session ends or RESET.
BEGIN;
BEGIN
SET LOCAL work_mem = '64MB';
SET
SHOW work_mem;
 work_mem 
----------
 64MB
(1 row)

COMMIT;
COMMIT
SHOW work_mem;
 work_mem 
----------
 4MB
(1 row)

SET work_mem = '16MB';
SET
SELECT setting, unit, source FROM pg_settings WHERE name = 'work_mem';
 setting | unit | source  
---------+------+---------
 16384   | kB   | session
(1 row)

RESET work_mem;
RESET
SHOW work_mem;
 work_mem 
----------
 4MB
(1 row)

-- A postmaster setting cannot change inside a running server.
SET shared_buffers = '256MB';
psql:settings.sql:18: ERROR:  parameter "shared_buffers" cannot be changed without restarting the server

settings.sql

-- Where does each value come from, and when can it change? context answers the second question.
SELECT name, setting, unit, context, source FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'listen_addresses', 'log_min_duration_statement',
               'work_mem', 'lock_timeout')
ORDER BY context, name;
SELECT context, count(*) FROM pg_settings GROUP BY context ORDER BY count(*) DESC, context;
-- SET LOCAL lasts until the transaction ends; SET until the session ends or RESET.
BEGIN;
SET LOCAL work_mem = '64MB';
SHOW work_mem;
COMMIT;
SHOW work_mem;
SET work_mem = '16MB';
SELECT setting, unit, source FROM pg_settings WHERE name = 'work_mem';
RESET work_mem;
SHOW work_mem;
-- A postmaster setting cannot change inside a running server.
SET shared_buffers = '256MB';

levels_session.py

"""Defaults stored for a role, for a database and for the pair, and which one a login receives."""

from pg_session import Server

with Server() as pg:
    pg.up()
    pg.psql("levels.sql")
    pg.silent(f"ALTER ROLE reporting PASSWORD '{pg.password}'")
    pg.psql("whoami.sql", user="reporting", db="shop")
    pg.psql("whoami.sql", user="reporting", db="postgres")
    pg.psql("set_role.sql", db="shop")

$ psql -X -a -f levels.sql
-- A role and a database, each with stored defaults, and one default for the pair.
CREATE ROLE reporting LOGIN;
CREATE ROLE
CREATE DATABASE shop;
CREATE DATABASE
ALTER ROLE reporting SET work_mem = '64MB';
ALTER ROLE
ALTER DATABASE shop SET lock_timeout = '2s';
ALTER DATABASE
ALTER ROLE reporting IN DATABASE shop SET work_mem = '32MB';
ALTER ROLE
SELECT coalesce(r.rolname, '(every role)') AS role, coalesce(d.datname, '(every database)') AS database,
       s.setconfig
FROM pg_db_role_setting AS s
LEFT JOIN pg_roles AS r ON r.oid = s.setrole
LEFT JOIN pg_database AS d ON d.oid = s.setdatabase
ORDER BY 1, 2;
     role     |     database     |     setconfig     
--------------+------------------+-------------------
 (every role) | shop             | {lock_timeout=2s}
 reporting    | (every database) | {work_mem=64MB}
 reporting    | shop             | {work_mem=32MB}
(3 rows)

-- The session now gives reporting a password made up for this run, and never prints it.

$ psql -d shop -U reporting -X -a -f whoami.sql
-- Which defaults did this login receive, and from which level?
SELECT current_user, current_database(), current_setting('work_mem') AS work_mem,
       current_setting('lock_timeout') AS lock_timeout;
 current_user | current_database | work_mem | lock_timeout 
--------------+------------------+----------+--------------
 reporting    | shop             | 32MB     | 2s
(1 row)

SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('lock_timeout', 'work_mem') ORDER BY name;
     name     | setting | unit |    source     
--------------+---------+------+---------------
 lock_timeout | 2000    | ms   | database
 work_mem     | 32768   | kB   | database user
(2 rows)

$ psql -d postgres -U reporting -X -a -f whoami.sql
-- Which defaults did this login receive, and from which level?
SELECT current_user, current_database(), current_setting('work_mem') AS work_mem,
       current_setting('lock_timeout') AS lock_timeout;
 current_user | current_database | work_mem | lock_timeout 
--------------+------------------+----------+--------------
 reporting    | postgres         | 64MB     | 0
(1 row)

SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('lock_timeout', 'work_mem') ORDER BY name;
     name     | setting | unit | source  
--------------+---------+------+---------
 lock_timeout | 0       | ms   | default
 work_mem     | 65536   | kB   | user
(2 rows)

$ psql -d shop -X -a -f set_role.sql
-- SET ROLE changes the role's privileges, not its stored defaults: those apply only at login.
SET ROLE reporting;
SET
SELECT current_user, current_database(), current_setting('work_mem') AS work_mem;
 current_user | current_database | work_mem 
--------------+------------------+----------
 reporting    | shop             | 4MB
(1 row)

levels.sql

-- A role and a database, each with stored defaults, and one default for the pair.
CREATE ROLE reporting LOGIN;
CREATE DATABASE shop;
ALTER ROLE reporting SET work_mem = '64MB';
ALTER DATABASE shop SET lock_timeout = '2s';
ALTER ROLE reporting IN DATABASE shop SET work_mem = '32MB';
SELECT coalesce(r.rolname, '(every role)') AS role, coalesce(d.datname, '(every database)') AS database,
       s.setconfig
FROM pg_db_role_setting AS s
LEFT JOIN pg_roles AS r ON r.oid = s.setrole
LEFT JOIN pg_database AS d ON d.oid = s.setdatabase
ORDER BY 1, 2;
-- The session now gives reporting a password made up for this run, and never prints it.

whoami.sql

-- Which defaults did this login receive, and from which level?
SELECT current_user, current_database(), current_setting('work_mem') AS work_mem,
       current_setting('lock_timeout') AS lock_timeout;
SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('lock_timeout', 'work_mem') ORDER BY name;

set_role.sql

-- SET ROLE changes the role's privileges, not its stored defaults: those apply only at login.
SET ROLE reporting;
SELECT current_user, current_database(), current_setting('work_mem') AS work_mem;