Data Directory, Tablespaces, Configuration Files, Include Hierarchy, and Reload vs Restart

Learning outcomes

ServiceHub’s PostgreSQL process model is now observable. The next failure class is configuration drift: an operator edits one file, reloads the server, and nothing changes—or worse, the server fails to start after a restart. The cure is not memorizing “the PostgreSQL config file.” A real instance can read several files and sources with defined precedence, and some settings are reloadable while others require a restart.

1. PGDATA is a cluster data directory, not “the database file”

PGDATA is a common environment variable/name for the database cluster data directory. A PostgreSQL database cluster is the collection of databases managed by one server instance. The directory contains critical server-managed state: control files, relation storage, WAL-related directories, configuration files in many installations, visibility/status subdirectories, and other internal structures.

The correct operational rule is simple: observe and manage PGDATA through documented PostgreSQL interfaces and backup/recovery procedures. Do not open a relation file in an editor, rename files because a table “looks unused,” or copy a live directory and assume you have a consistent backup.

SHOW data_directory;SHOW config_file;SHOW hba_file;SHOW ident_file;SELECT name, setting, source, sourcefile, sourceline, context, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('data_directory','config_file','hba_file','ident_file')ORDER BY name;

The output is authoritative for the running server you actually connected to. Package layouts differ: some Linux distributions keep configuration under /etc while the data directory lives elsewhere; a Docker image often keeps configuration inside the volume/container unless you mount it explicitly; Windows installers choose their own locations.

2. Four files with different jobs

Authentication files are operationally distinct from ordinary GUC parameters. PostgreSQL exposes views such as pg_hba_file_rules and pg_ident_file_mappings to help inspect them, but Chapter 17 covers authentication/security in depth. Here we focus on safe observability.

SELECT line_number, type, database, user_name, address, auth_method, errorFROM pg_catalog.pg_hba_file_rulesORDER BY line_number;

3. Configuration-file precedence: location is not enough

Inside configuration files, PostgreSQL processes settings in order. If the same parameter appears multiple times, the last setting encountered wins. Include directives are processed as though the included content appeared at that point. include_dir loads eligible .conf files in filename order using C-locale rules, which is why names such as 00-shared.conf, 20-memory.conf, and 90-host.conf are useful.

# postgresql.confinclude 'conf.d/00-shared.conf'include 'conf.d/20-memory.conf'include_if_exists 'conf.d/50-local-optional.conf'include_dir 'conf.extra'# A later setting encountered for the same parameter winslog_min_duration_statement = '750ms'

After postgresql.conf processing, postgresql.auto.conf is also read and values there override the same parameters from postgresql.conf. Server command-line -c name=value settings can override configuration-file values again. Session/database/role overrides create another layer. Lesson 4 builds the full source/scope model.

4. Use pg_file_settings before blaming reload

pg_settings describes effective runtime settings. pg_file_settings describes entries found in the current configuration files, including whether they can be applied and parse errors. That means the two views intentionally answer different questions.

SELECT name, setting, unit, source, sourcefile, sourceline, context, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('log_min_duration_statement','shared_buffers','max_connections')ORDER BY name;SELECT sourcefile, sourceline, name, setting, applied, errorFROM pg_catalog.pg_file_settingsWHERE name IN ('log_min_duration_statement','shared_buffers','max_connections')   OR error IS NOT NULLORDER BY sourcefile, sourceline;

If the file says one value but pg_settings says another, investigate source precedence and whether the parameter needs restart. If pg_file_settings.error is populated, fix the file first rather than repeatedly reloading.

5. Reload versus restart: read the parameter context

PostgreSQL configuration parameters have a context that describes when and by whom they can change. The two most important operational cases here are sighup and postmaster. A sighup setting can be applied after a configuration reload. A postmaster setting only takes effect when the server starts, so a change requires restart.

SELECT name, setting, context, source, pending_restartFROM pg_catalog.pg_settingsWHERE name IN (  'log_min_duration_statement',  'log_connections',  'shared_buffers',  'max_connections',  'port')ORDER BY context, name;

On a typical PostgreSQL 18 instance, settings such as shared_buffers, max_connections, and port are server-start settings. Logging thresholds often have more flexible contexts. Do not memorize this list forever; query pg_settings.context on the target major.

A safe reload can be requested through:

SELECT pg_reload_conf();

Or from a server shell using pg_ctl reload for a cluster you own. Package-managed services may provide a service-manager reload command. The important idea is the PostgreSQL reload event, not one universal OS command.

6. Tablespaces: a controlled exception to “everything under PGDATA”

A tablespace lets PostgreSQL place database objects in an alternate filesystem location. This can support storage-management goals, but it does not turn the alternate directory into application-owned files. PostgreSQL tracks tablespace metadata and creates symlinks/links from the cluster’s pg_tblspc area as appropriate for the platform.

Creating a tablespace requires high privilege and a directory owned/accessible as required by the PostgreSQL server account. For this course, tablespaces are conceptual unless you have a disposable native installation where you understand filesystem ownership. They are not required for Docker Desktop learners.

SELECT oid, spcname, pg_catalog.pg_get_userbyid(spcowner) AS owner,       pg_catalog.pg_tablespace_location(oid) AS locationFROM pg_catalog.pg_tablespaceORDER BY spcname;

Do not create a tablespace inside PGDATA and do not delete a tablespace directory manually. Use CREATE TABLESPACE/DROP TABLESPACE and documented storage/backup procedures.

7. Deliberately wrong approach: edit a restart-only setting, reload, assume success

Suppose an operator edits shared_buffers, runs SELECT pg_reload_conf();, sees true, and announces that the new buffer size is active. The boolean only means the reload signal was sent successfully. It does not mean every changed setting could take effect.

SELECT name, setting, source, context, pending_restart,       sourcefile, sourcelineFROM pg_catalog.pg_settingsWHERE name = 'shared_buffers';

If the configuration file contains a different valid value but the running value cannot change until server startup, pending_restart becomes your evidence. The safe repair is to plan a restart window, verify the config first, understand workload/availability consequences, restart the disposable lab, then re-query the setting. Never bounce production simply because a tutorial says “restart now.”

8. Hands-on lab: one reloadable change, one restart-only observation

1. Record SHOW config_file, SHOW data_directory, and the relevant rows from pg_settings.
2. In the disposable cluster, add a low-risk logging setting such as log_min_duration_statement = '750ms' using your chosen configuration-management method.
3. Use pg_file_settings to check that the file parses and the intended value is the last applicable entry.
4. Call pg_reload_conf(), open a fresh session if needed, and verify the effective value/source.
5. For shared_buffers, do not change it unless you want to practice a restart. Instead inspect its context and explain why reload is insufficient.
6. Revert the logging change, reload again, and verify the original/reset effective state.

SELECT name, setting, unit, context, source, sourcefile, sourceline, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('log_min_duration_statement','shared_buffers')ORDER BY name;SELECT sourcefile, sourceline, name, setting, applied, errorFROM pg_catalog.pg_file_settingsWHERE name = 'log_min_duration_statement' OR error IS NOT NULLORDER BY sourcefile, sourceline;

9. Production judgment and next bridge

Version-control intentional configuration, keep secrets out of inappropriate files, validate before reload/restart, and record whether each change is dynamic, reloadable, or restart-only. Treat ALTER SYSTEM as one configuration-management path—not magic—and avoid mixing several uncontrolled writers of the same parameters.

Lesson 3 now uses the same pg_settings evidence to explain PostgreSQL memory: what is shared across the instance, what belongs to individual backends/operations, and why connection count multiplies risk.

Authoritative references

• PostgreSQL 18 — Setting Parameters
• PostgreSQL 18 — File Locations
• PostgreSQL 18 — pg_settings
• PostgreSQL 18 — pg_file_settings
• PostgreSQL 18 — pg_hba.conf
• PostgreSQL 18 — Tablespaces