Detailed Explanation of PostgreSQL Configuration Files

PostgreSQL’s behavior is driven by a handful of plain‑text configuration files. Knowing where these files live, how they

PostgreSQL’s behavior is driven by a handful of plain‑text configuration files. Knowing where these files live, how they are structured, and which parameters matter most is essential for any DBA who wants to keep a server stable and performant.
By default PostgreSQL places all configuration files in the data directory ($PGDATA), e.g. /var/lib/postgresql/16/main. The location can be overridden by setting config_file, hba_file, or ident_file inside postgresql.conf. You can quickly verify the active paths with SQL:

SHOW config_file;
SHOW hba_file;
SHOW ident_file;

postgresql.conf – the master settings file

postgresql.conf is the central repository for almost every runtime option: listening addresses, memory allocation, logging, checkpoint behavior, and more. The syntax is simple – one parameter = value per line, with # introducing comments.

Frequently tuned parameters

listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 2GB
work_mem = 16MB
logging_collector = on
Parameter What it controls Typical guidance
listen_addresses IP addresses the server accepts connections on. The default is localhost only. Use '*' for all interfaces or list specific addresses for tighter security.
max_connections Upper limit of simultaneous client connections. Balance against available RAM; each connection consumes work memory and other overhead.
shared_buffers Size of the shared memory buffer pool. Roughly 20‑25 % of total system memory is a common starting point.
work_mem Memory available to each sort, hash, or aggregate operation. Larger values speed up complex queries but multiply by the number of concurrent operations.
logging_collector Enables a separate log‑writing process. Turn on for production environments to capture detailed logs.

Reload vs. restart

Not every change requires a full server restart. PostgreSQL classifies parameters by context:

  1. postmaster – changes need a restart (e.g., max_connections, shared_buffers).
  2. sighup – changes take effect after a configuration reload (e.g., log_min_duration_statement).

Reload the configuration with either of these commands:

pg_ctl reload          # command‑line
SELECT pg_reload_conf();  # inside psql

pg_hba.conf – host‑based authentication

pg_hba.conf decides who can connect, where they can connect from, and how they must authenticate. Each line is a rule composed of five fields:

# TYPE  DATABASE  USER    ADDRESS          METHOD
host    all       all     192.168.1.0/24   scram-sha-256
host    mydb      appuser 10.0.0.0/8       md5
local   all       postgres                peer
  • TYPE – local (Unix socket), host (TCP/IP), or hostssl (TCP/IP with mandatory SSL).
  • METHOD – authentication mechanism. Common choices:
    • trust – no password (useful only in isolated test setups).
    • scram-sha-256 – modern, salted password hashing (recommended).
    • md5 – legacy password hashing.
    • peer – matches the operating‑system user name to the database user.
    • cert – client‑certificate authentication.

Rules are evaluated top‑down; the first matching entry wins. Therefore, place the most specific rules near the top and keep broader catch‑alls at the bottom. After editing, reload the file (see the reload commands above) for the changes to take effect.

pg_ident.conf – mapping external identities

When you use authentication methods that expose an external identity (e.g., ident, peer, or cert), pg_ident.conf lets you map that identity to a PostgreSQL role.

# MAPNAME   SYSTEM-USERNAME   PG-USERNAME
adminmap    alice             dba
adminmap    bob               dba

In pg_hba.conf you reference the map with the map= option:

local   all   dba   peer   map=adminmap

This indirection is handy for granting a handful of OS users privileged database access without creating separate database roles for each.

Managing settings via SQL

Direct file edits are not the only way to tweak the server. PostgreSQL exposes several SQL interfaces:

  • Inspect a single parameter

    SHOW work_mem;

  • Browse all settings

    SELECT name, setting, unit, context
FROM pg_settings
WHERE name LIKE '%mem%';

  • Persist a change without touching the file

    ALTER SYSTEM SET work_mem = '32MB';

    ALTER SYSTEM writes the new value to postgresql.auto.conf, which overrides postgresql.conf. After issuing the command, reload the configuration (SELECT pg_reload_conf();) to apply it.

Practical recommendations

  1. Version‑control your configs – keep a copy of postgresql.conf, pg_hba.conf, and pg_ident.conf in a Git repository. This makes rollbacks painless and provides an audit trail.
  2. Prefer ALTER SYSTEM for routine tweaks – it reduces the risk of syntax errors and automatically respects the correct precedence.
  3. Mind the memory equation – the product of max_connections × work_mem should not exceed the RAM left after allocating shared_buffers. Over‑committing leads to swapping and severe performance degradation.
  4. Apply the principle of least privilege in pg_hba.conf – avoid trust in production, restrict CIDR ranges to only the networks that truly need access, and use scram-sha-256 wherever possible.
  5. Test changes on a staging instance – especially for parameters that trigger a restart. Verify that the server boots cleanly and that performance metrics improve as expected.
  6. Document any custom maps – if you rely on pg_ident.conf, keep a clear comment block explaining the purpose of each map name and the associated OS users.

Mastering these three configuration files—and the SQL tools that complement them—lays the groundwork for reliable PostgreSQL administration, smooth scaling, and effective performance tuning.