Study guide
2.4 Server Configuration Parameters
PostgreSQL Associate Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & PostgreSQL Foundation
1.1 EDB PostgreSQL Associate Certification Overview 1.2 The PostgreSQL Relational Model & Architecture Principles 1.3 Study Strategies, Lab Setup & Exam-Day Execution
2 Chapter 2: PostgreSQL Architecture & Installation
2.1 The PostgreSQL Server Process Model 2.2 Database Clusters & Cluster Initialization (initdb) 2.3 Filesystem Layout, Service Control & Startup/Shutdown 2.4 Server Configuration Parameters
3 Chapter 3: Demonstrating SQL Knowledge
3.1 Relational Data Types & Table Design 3.2 Integrity Constraints & Data Validation 3.3 Querying, Joining & Aggregating Data 3.4 DML Operations, Transaction Control & MVCC Mechanics
4 Chapter 4: PostgreSQL Administrative Tools
4.1 Interactive Command-Line Mastery with psql 4.2 GUI Administration with pgAdmin 4.3 Client & Server Command-Line Utilities 4.4 System Catalogs & Information Schema
5 Chapter 5: Core Database Administration & Security
5.1 Managing Databases, Schemas & Tablespaces 5.2 Roles, Privileges & Object Ownership 5.3 Client Authentication & pg_hba.conf Architecture
6 Chapter 6: Routine Maintenance & Data Operations
6.1 The PostgreSQL Vacuuming Engine & MVCC Space Reclamation 6.2 Analyzing Query Statistics & Reindexing 6.3 High-Performance Data Loading & Data Movement
7 Chapter 7: Backup and Recovery Strategies
7.1 Logical Backups with pg_dump & pg_dumpall 7.2 Restoring Logical Backups 7.3 Physical Base Backups with pg_basebackup 7.4 Write-Ahead Logging & Point-in-Time Recovery
8 Chapter 8: High Availability & Replication
8.1 Physical Streaming Replication Architecture 8.2 Synchronous vs. Asynchronous Replication 8.3 Replication Slots & Standby Management 8.4 Failover, Switchover & High-Availability Design
9 Chapter 9: Monitoring, Diagnostics & Performance Tuning
9.1 Cumulative Statistics & Activity Monitoring 9.2 Query Execution Plans & Index Optimization 9.3 Server Logging Configuration & Autovacuum Tuning 9.4 Server Memory Sizing & Core Performance Parameters
2.4 Server Configuration Parameters
Key Takeaways Explain This Topic AI
PostgreSQL configuration parameters are governed primarily through postgresql.conf and augmented dynamically via ALTER SYSTEM into postgresql.auto.conf.
Configuration parameters are strictly categorized into context tiers: internal, postmaster, sighup, superuser, and user.
Parameters with context 'postmaster' (such as shared_buffers, max_connections, port, and wal_level) require a complete server shutdown and restart to modify because they dictate shared memory allocations.
Parameters with context 'sighup' (including work_mem defaults, log_min_duration_statement, and autovacuum controls) can be reloaded online without server downtime using pg_ctl reload or SELECT pg_reload_conf();.
The pg_settings system catalog view provides comprehensive operational metadata on all parameters, including their runtime values, default units, context tiers, and whether a restart is pending.
Last updated: September 2026
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):
- Session-level overrides ( SET parameter = value; or client connection string parameters)
- Role-level overrides ( ALTER ROLE username SET parameter = value; )
- Database-level overrides ( ALTER DATABASE dbname SET parameter = value; )
- postgresql.auto.conf (Managed dynamically via ALTER SYSTEM )
- 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 .
Parameter Name Context Tier Requires Restart? Reloadable via SIGHUP? Modifiable via SET in Session?
- shared_buffers postmaster Yes (Fatal otherwise) No No
- max_connections postmaster Yes No No
- port postmaster Yes No No
- wal_level postmaster Yes No No
- log_min_duration_statement superuser No Yes ( pg_reload_conf() ) Yes, but superusers only
- checkpoint_timeout sighup No Yes No
- logging_collector postmaster Yes No No
- autovacuum sighup No Yes No
- work_mem user No Yes (default) Yes (Any session)
- search_path user No Yes (default) Yes (Any session)
- statement_timeout user No Yes (default) Yes (Any session)
- 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.