--- title: "08 — Configuring PostgreSQL: The Three Config Files" uid: configuration-extensions tags: ["logging", "postgresql-conf", "roadmap:postgresql-dba", "postgresql", "extensions", "systemd", "configuration", "pg-hba-conf"] excerpt: "Three config files, one job each: postgresql.conf (runtime knobs), pg_hba.conf (who can connect), pg_ident.conf (OS-user mapping) — plus a service manager that restarts to apply them." date: 2026-08-13T03:27:52+0000 source: https://www.aveshina.my.id/en/blog/configuration-extensions --- "Edit a file and hope" was my configuration strategy, which made every failed restart a scavenger hunt. The framing that organized it: **Postgres behavior is governed by three config files, each with a single job**, and a service manager restarts the process to apply them [1]. Once I knew which file owns which concern, configuration stopped being guesswork and became three focused checklists: runtime knobs (postgresql.conf), who-can-connect rules (pg_hba.conf), and OS-user mapping (pg_ident.conf). ## The three files, one job each ```figure ``` - **postgresql.conf** — the runtime knobs. Memory (shared_buffers, work_mem), connections (max_connections), WAL, logging, autovacuum, and the planner cost constants. The file is large, but most of it stays at sensible defaults; I touch maybe a dozen lines [1]. - **pg_hba.conf** (Host-Based Authentication) — who can connect, from where, to which database, as which user, and by which auth method [4]. The rules are matched top-down; the first matching line wins. - **pg_ident.conf** — maps operating-system usernames to database roles, used only when the auth method is peer or ident. ## Managing the process A config change doesn't take effect until the server reloads or restarts [2][3]. Three tools do this, depending on the install: - **systemd** — systemctl restart postgresql (or reload for non-restart-needed changes). The standard on modern Linux. - **pg_ctl** — the lower-level utility: pg_ctl restart -D /var/lib/postgresql/data. - **pg_ctlcluster** — Debian/Ubuntu's wrapper for managing multiple clusters side by side. The distinction between reload and restart matters: reload re-reads config without dropping connections (for most postgresql.conf changes); restart cycles the process (needed for a few settings like shared_buffers). Many settings can also be changed at runtime with ALTER SYSTEM SET ... without editing the file directly. ## The knobs worth knowing A handful of postgresql.conf settings cover most tuning needs: - **shared_buffers** — the shared memory cache, typically 25% of RAM. The most impactful single setting. - **work_mem** — per-sort, per-hash memory before spilling to disk. Small increases can hugely help big analytical queries. - **maintenance_work_mem** — memory for VACUUM and index builds; set higher than work_mem. - **max_connections** — raise carefully; each connection is a backend process. Better to pool with PgBouncer than to set this in the thousands. - **effective_cache_size** — tells the planner how much OS cache is available; set to ~50–75% of RAM so the planner picks index-friendly plans. These are starting points, not dogma. The roadmap's guidance — measure, change one knob at a time, measure again — is the whole method. ## Logging: the diagnostic feed Logging is configured in postgresql.conf, and it's the feed every analysis tool consumes [5]. The settings I make sure are on: - logging_collector = on — capture logs to files instead of stderr. - log_line_prefix — include timestamp, pid, database, user, and (critically) the query duration tag. - log_min_duration_statement — log any statement slower than N milliseconds (the "slow query log"). With those, the log files become the input for tools like PgBadger (which turns logs into HTML reports of slow queries, errors, and load patterns) and the basis for the performance work covered later. ## Extensions: Postgres as a platform The configuration chapter also includes **extensions** — loadable modules that add types, functions, or features [6]. Extensions are installed with CREATE EXTENSION and turn Postgres into a platform: - pg_stat_statements — query execution statistics (essential for finding slow queries). - pgcrypto — cryptographic functions. - uuid-ossp — UUID generation. - postgis — geographic/spatial types and indexing. - pg_buffercache, pgrowsecurity, the pg_trgm trigram index for fuzzy text search. The habit: before writing custom logic, check whether an extension already does it. The Postgres extension ecosystem is one of its real competitive advantages. ## How I use this The three-files model is the habit. When something behaves unexpectedly, I ask which file governs it: connection refused or wrong auth → pg_hba.conf; memory or planner behavior → postgresql.conf; OS-user mismatch → pg_ident.conf. I change one setting at a time and use ALTER SYSTEM SET for runtime changes to avoid manual file edits. Logging is the first thing I turn on with log_min_duration_statement set — without it, tuning is guesswork. And on any new cluster, I install pg_stat_statements immediately, because every later performance conversation depends on it. ## References [1] PostgreSQL Global Development Group, "Runtime Configuration," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/runtime-config.html](https://www.postgresql.org/docs/current/runtime-config.html) [2] DigitalOcean, "What is systemd?," 2023. [Online]. Available: [https://www.digitalocean.com/community/tutorials/what-is-systemd](https://www.digitalocean.com/community/tutorials/what-is-systemd) [3] PostgreSQL Global Development Group, "pg_ctl," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/app-pg-ctl.html](https://www.postgresql.org/docs/current/app-pg-ctl.html) [4] PostgreSQL Global Development Group, "The pg_hba.conf file," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/auth-pg-hba-conf.html](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html) [5] BetterStack, "PostgreSQL logging: everything you need to know," 2024. [Online]. Available: [https://betterstack.com/community/guides/logging/how-to-start-logging-with-postgresql/](https://betterstack.com/community/guides/logging/how-to-start-logging-with-postgresql/) [6] PostgreSQL Global Development Group, "CREATE EXTENSION," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/sql-createextension.html](https://www.postgresql.org/docs/current/sql-createextension.html) ```quiz Q: Which config file controls runtime behavior like shared_buffers and max_connections? - pg_hba.conf - postgresql.conf - pg_ident.conf correct: 1 explain: postgresql.conf holds the runtime knobs (memory, connections, WAL, logging, planner). pg_hba.conf controls who can connect; pg_ident.conf maps OS users to roles. Q: pg_hba.conf rules are evaluated in what order? - Random order - Top-down; the first matching rule wins correct: 1 explain: Rules are matched top-down. The first line whose host/database/user/address match the incoming connection determines the auth method, so ordering matters. Q: What is the difference between systemctl reload and systemctl restart for PostgreSQL? - They are identical - reload re-reads config without dropping connections; restart cycles the process (needed for a few settings like shared_buffers) correct: 1 explain: Most postgresql.conf changes apply on reload with no disconnection. A few parameters (shared_buffers, wal_level) require a full restart. Q: Which setting turns PostgreSQL into a slow-query logger? - log_line_prefix - log_min_duration_statement correct: 1 explain: log_min_duration_statement = 250 logs every statement taking longer than 250ms. log_line_prefix only formats each log line's metadata; it doesn't filter by duration. Q: Why install pg_stat_statements early on a new cluster? - It auto-tunes shared_buffers - It records per-query execution statistics, which every later performance investigation depends on correct: 1 explain: pg_stat_statements accumulates call counts, total time, and rows per query. It's the foundation of finding slow queries, so enabling it from day one preserves history. ```