2.4 Server Configuration Parameters

2.4 Server Configuration Parameters

[!IMPORTANT] Configuration Precedence Hierarchy: PostgreSQL evaluates configuration settings through a clearly defined priority cascade. When determining the active value for a parameter, the engine follows this precedence order (highest to lowest):

1. Session-level overrides (SET parameter = value; or client connection string parameters)
2. Role-level overrides (ALTER ROLE username SET parameter = value;)
3. Database-level overrides (ALTER DATABASE dbname SET parameter = value;)
4. postgresql.auto.conf (Managed dynamically via ALTER SYSTEM)
5. postgresql.conf (Primary cluster configuration file)

Mastering server configuration is one of the most heavily tested competencies on the PostgreSQL Associate certification exam. Administrators must know not only what a parameter does, but where it is configured, who can alter it, and whether changing it requires a full cluster reboot.

---

Server Configuration File Architecture

A standard PostgreSQL cluster maintains its configuration across distinct configuration files within $PGDATA:

1. postgresql.conf

• The primary, human-readable configuration file initialized by initdb.
• Settings are written as simple key-value pairs: parameter_name = value.
• Lines beginning with # represent comments or commented-out system defaults.
• Parameter values accept intuitive memory and time units (e.g., KB, MB, GB, ms, s, min, h, d).

Modular Configuration Directives

To avoid maintaining a monolithic, thousands-of-lines postgresql.conf file, PostgreSQL provides modular inclusion directives:

• include = 'filename.conf': Loads an external configuration file. If the file does not exist, the server throws a fatal startup error.
• include_if_exists = 'optional.conf': Includes an external file only if it is physically present on disk; does not fail if missing.
• include_dir = 'conf.d': Reads and loads all files ending in .conf located within a specified subfolder, processed in alphabetical order. This pattern is universal in enterprise environments where automation tools (Ansible, Puppet) drop dedicated configuration fragments (e.g., conf.d/01-memory.conf, conf.d/02-logging.conf).

2. postgresql.auto.conf and ALTER SYSTEM

• Purpose: PostgreSQL allows administrators to change configuration settings remotely via SQL using the ALTER SYSTEM command:

  ALTER SYSTEM SET work_mem = '64MB';
  ALTER SYSTEM SET log_min_duration_statement = 250;
  

• Physical Storage: When ALTER SYSTEM is executed, PostgreSQL serializes the parameter into a dedicated file named $PGDATA/postgresql.auto.conf.
• Precedence Rule: During startup or configuration reloads, PostgreSQL reads postgresql.conf first, and then reads postgresql.auto.conf. As a result, settings in postgresql.auto.conf automatically overwrite and supersede any values declared in postgresql.conf!
• Administrative Rule: Administrators should never manually edit postgresql.auto.conf with a text editor. To remove a parameter set via ALTER SYSTEM, use the corresponding SQL reset command:

  ALTER SYSTEM RESET work_mem;
  -- Or reset all auto parameters back to postgresql.conf defaults:
  ALTER SYSTEM RESET ALL;
  

---

Parameter Contexts: The Five Tiers

Every parameter in PostgreSQL is assigned a specific context that dictates when and how changes can be applied. Attempting to modify a parameter using an unsupported mechanism (e.g., calling SET shared_buffers = '4GB' inside a query session) raises an immediate error — ERROR: parameter "shared_buffers" cannot be changed without restarting the server. It is a runtime rejection, not a syntax error.

+-----------------------------------------------------------------------------------+
|                         PostgreSQL Parameter Context Tiers                        |
+-----------------------------------------------------------------------------------+
| 1. internal   | Compile-time constants. Strictly read-only. Cannot be altered.     |
| 2. postmaster | Requires FULL SERVER RESTART (allocates shared memory/sockets).    |
| 3. sighup     | Applied ONLINE without downtime via pg_ctl reload or SIGHUP signal. |
| 4. superuser  | Modifiable in session by SUPERUSER only, or via config files.      |
| 5. user       | Modifiable dynamically by ANY USER within an active session (SET). |
+-----------------------------------------------------------------------------------+

Detailed Breakdown of the Context Tiers

1. internal

• Mechanism: Set at engine compile-time or cluster initialization.
• Behavior: Completely read-only; cannot be altered by configuration files, SQL commands, or restarts.
• Examples: block_size (8192 bytes), wal_block_size (8192 bytes), server_version_num, data_checksums.

2. postmaster

• Mechanism: Requires a complete service restart (pg_ctl restart or systemctl restart postgresql).
• Rationale: Parameters in this context define the structure and boundaries of centralized shared memory, IPC semaphores, and network listeners. Changing them requires re-allocating the shared memory segment, which cannot be done while backends are running.
• Examples: shared_buffers, max_connections, port, listen_addresses, wal_level, huge_pages.

3. sighup

• Mechanism: Online reloadable without downtime. Changes are applied by sending the SIGHUP signal to the postmaster daemon, either via CLI (pg_ctl reload) or SQL (SELECT pg_reload_conf();).
• Rationale: The postmaster receives SIGHUP, re-reads postgresql.conf and postgresql.auto.conf, and signals all running backend processes to adopt the new configuration on their next instruction cycle without terminating active connections.
• Examples: autovacuum, checkpoint_timeout, archive_command, max_standby_streaming_delay, hot_standby_feedback.

  [!WARNING] Two parameters are commonly mis-filed here. logging_collector is postmaster-context — turning it on requires a full restart, not a reload. log_min_duration_statement is superuser-context — it is reloadable, but it can also be changed inside a session by a superuser (or a role granted SET on it), which a pure sighup parameter cannot.

4. superuser

• Mechanism: Can be modified in configuration files (with SIGHUP reload) or dynamically altered inside a specific connection session using SET parameter = value;, but only by a database superuser.
• Examples: log_min_duration_statement, log_statement, session_preload_libraries, log_statement_stats, track_io_timing.

5. user

• Mechanism: Fully dynamic. Can be configured at the cluster level, or modified at runtime by any unprivileged user within their individual session via SET parameter = value;.
• Scope: Changes made via SET affect only the current session and are discarded immediately upon disconnection.
• Examples: work_mem, statement_timeout, search_path, timezone, client_encoding.

---

Reloading Configuration Online

When a sighup-context parameter is modified in postgresql.conf or set via ALTER SYSTEM, the change does not take effect immediately. The postmaster must be instructed to reload its configuration files.

Method 1: The SQL Function pg_reload_conf()

Administrators connected via psql or an administration GUI can trigger an immediate online reload without shell access:

SELECT pg_reload_conf();

This function sends a SIGHUP signal directly to the postmaster process and returns true upon successful signal delivery.

Method 2: The Command-Line pg_ctl reload

From the Linux terminal, the operating system administrator can execute:

pg_ctl -D /var/lib/pgsql/16/data reload
# Or using systemd:
sudo systemctl reload postgresql-16

---

Inspecting Server Settings with SQL and pg_settings

PostgreSQL exposes several methods to inspect active parameters, identify pending restart requirements, and audit configuration states.

1. The SHOW Meta-Command

SHOW shared_buffers;
SHOW work_mem;
SHOW ALL; -- Displays all configuration parameters and descriptions

2. The current_setting() Function

Useful for embedding parameter inspection inside application queries:

SELECT current_setting('max_connections');

3. The Authoritative pg_settings System Catalog View

The pg_settings view provides complete administrative metadata for every parameter in the engine. Key columns include:

• name: Parameter name
• setting: Current active value
• unit: Measurement unit (e.g., 8kB, MB, ms)
• category: Functional grouping (e.g., Resource Usage / Memory)
• context: The context tier (internal, postmaster, sighup, superuser, user)
• pending_restart: Boolean flag indicating whether a new value has been set that cannot take effect until the cluster is restarted!

-- Querying parameters to check for pending restarts
SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE pending_restart = true;

If this query returns rows, it indicates that a postmaster-context parameter has been altered in configuration (or via ALTER SYSTEM), but the server is still running with the old in-memory allocation pending a service restart.

---

Hands-On Workflow: Changing Settings Safely

Scenario A: Modifying an Online Parameter (sighup)

-- 1. Modify the parameter via SQL
ALTER SYSTEM SET log_min_duration_statement = '500ms';

-- 2. Signal the postmaster to reload configuration files
SELECT pg_reload_conf();

-- 3. Confirm the new setting is actively applied
SHOW log_min_duration_statement;

Scenario B: Modifying a Restart Parameter (postmaster)

-- 1. Modify the parameter via SQL
ALTER SYSTEM SET shared_buffers = '4GB';

-- 2. Verify that the change is recorded but pending a restart
SELECT name, setting, pending_restart 
FROM pg_settings 
WHERE name = 'shared_buffers';
-- Result: pending_restart = true

-- 3. In the operating system shell, restart the service
-- $ pg_ctl -D /var/lib/pgsql/16/data -m fast restart

-- 4. Reconnect and verify pending_restart is now false
SELECT name, setting, pending_restart 
FROM pg_settings 
WHERE name = 'shared_buffers';
-- Result: pending_restart = false, setting reflects 4GB

---

Exam Tips and Common Pitfalls

• Exam Trap: Where ALTER SYSTEM Writes: ALTER SYSTEM SET parameter = value; does NOT edit postgresql.conf! It writes exclusively to postgresql.auto.conf. If an exam question asks which file is modified when ALTER SYSTEM is executed, the only correct answer is postgresql.auto.conf.
• Exam Trap: Postmaster Parameter Behavior: If you execute ALTER SYSTEM SET max_connections = 200; followed by SELECT pg_reload_conf();, does max_connections change immediately? No! Because max_connections is in the postmaster context, it requires a full cluster restart; pg_reload_conf() has zero effect on it, and pending_restart will display true in pg_settings.
• Exam Trap: Session Scope with SET: Settings applied using SET work_mem = '64MB'; only apply to the current client session. As soon as that connection closes, the setting vanishes and subsequent sessions revert to the cluster or role default.
• Exam Trap: Logging Parameters Are Not All the Same Context: logging_collector is postmaster-context and needs a restart, while log_min_duration_statement, log_statement, log_line_prefix, and log_destination all apply on reload. A question that offers "reload to enable the logging collector" is offering a wrong answer.