Configuration & Extensions

Two files, sitting next to each other in the data directory, control almost everything about how a Postgres server behaves and who it will talk to.

postgresql.conf

Server-wide settings live here: memory (shared_buffers, work_mem), WAL behavior (wal_level), logging, connection limits, and hundreds more.

shared_buffers = 256MB
work_mem = 16MB
wal_level = replica
log_min_duration_statement = 500   # ms — log slow queries
max_connections = 100

Not every setting takes effect the same way once changed:

• Some (work_mem, log_min_duration_statement) apply on the next SIGHUP — SELECT pg_reload_conf(); or docker exec postgres pg_ctl reload, no downtime.
• Some (shared_buffers, max_connections) are fixed at process startup and need a full restart, because they size memory that's allocated once when the postmaster starts.

SHOW work_mem;
ALTER SYSTEM SET work_mem = '32MB';   -- writes to postgresql.auto.conf
SELECT pg_reload_conf();

ALTER SYSTEM is the SQL-native way to change settings without editing the file by hand — it writes to postgresql.auto.conf, which is read after (and overrides) postgresql.conf.

pg_hba.conf

This is the file that actually enforces the authentication methods discussed in Users, Roles & Security — "hba" stands for host-based authentication. Each line is a rule matched top-to-bottom against connection type, database, user, and source address; the first match wins.

# TYPE  DATABASE  USER   ADDRESS          METHOD
local   all       all                     peer
host    learning  admin  127.0.0.1/32     scram-sha-256
host    all       all    10.0.0.0/8       scram-sha-256
host    all       all    0.0.0.0/0        reject

• trust — no password at all. Fine for local connections in a throwaway container, never for anything reachable from a network.
• password — plaintext over the wire (only safe combined with sslmode=require or stronger).
• scram-sha-256 — the modern default: a salted-challenge password exchange that never sends the password itself, even over an unencrypted connection.
• cert — the client presents a TLS client certificate instead of a password.

Extensions

Postgres ships a small core and leans on extensions for almost everything beyond it. CREATE EXTENSION loads one into the current database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- query stats, see Monitoring
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";         -- uuid_generate_v4()
CREATE EXTENSION IF NOT EXISTS pgcrypto;            -- gen_random_uuid(), digest()

Some extensions (like pg_stat_statements) also need to be added to shared_preload_libraries in postgresql.conf and the server restarted, because they hook into query execution from the moment the server starts — CREATE EXTENSION alone isn't enough for those.

Major-version upgrades

Changing a Docker image tag from postgres:16 to postgres:17 does not upgrade a running cluster — the on-disk format isn't guaranteed compatible across major versions, so the new binary will refuse to start against old data files. A real upgrade uses pg_upgrade (which can do it in place, fast, via hard links) or a pg_dump/pg_restore round trip, or — the zero-downtime option — logical replication into a new cluster running the target version, followed by a cutover.

Configuration decides how the server runs and who's allowed to touch it. Next: Monitoring covers watching what it's actually doing while it runs.