PostgreSQL config basics (postgresql.conf and pg_hba.conf)

Topic: Databases core

Configure PostgreSQL via postgresql.conf (memory, connections, WAL) and pg_hba.conf (who can connect and how). Reload or restart after changes. Use this when tuning performance, enabling remote access, or locking down authentication.

Intent: How-to

Quick answer

  • postgresql.conf: shared_buffers, work_mem, max_connections, listen_addresses. Location: data_dir/postgresql.conf or /etc/postgresql/VERSION/main. Reload with SELECT pg_reload_conf(); or systemctl reload postgresql.
  • pg_hba.conf: one line per rule (type, database, user, address, method). local for socket; host for TCP. Method: scram-sha-256 or md5 (prefer scram). Restart not needed for pg_hba; reload is enough.
  • Include files: use include_dir or include in postgresql.conf to split config. Show current: SHOW shared_buffers; SHOW max_connections;. Change only what you need; benchmark after tuning.

Prerequisites

Steps

  1. Locate and edit postgresql.conf

    Find data directory: sudo -u postgres psql -c SHOW data_directory. Edit postgresql.conf in that directory (or the included file). Set shared_buffers (e.g. 25% of RAM), max_connections, listen_addresses.

  2. Reload or restart

    Most changes need restart (shared_buffers, max_connections); some need only reload (work_mem, pg_hba). systemctl reload postgresql or SELECT pg_reload_conf(); for reload; systemctl restart postgresql for restart.

  3. Configure pg_hba.conf

    Add host mydb myuser 10.0.0.0/8 scram-sha-256 for TCP connections from 10.0.0.0/8. Order matters; first match wins. Reload after edit. Avoid trust for network connections.

  4. Verify settings

    SHOW shared_buffers; SHOW listen_addresses;. Connect from allowed client; confirm denied clients are rejected. Document changes for audit and disaster recovery.

Summary

Edit postgresql.conf for server parameters and pg_hba.conf for client authentication. Reload or restart as required; verify with SHOW and connection tests. Use this for tuning and access control.

Prerequisites

Steps

Step 1: Locate and edit postgresql.conf

Find the data directory and edit postgresql.conf (or an included file); set key parameters.

Step 2: Reload or restart

Use reload for parameters that support it; restart for shared_buffers and max_connections.

Step 3: Configure pg_hba.conf

Add host rules for allowed clients with scram-sha-256; reload after editing.

Step 4: Verify settings

Use SHOW to confirm parameters; test connections from allowed and disallowed clients.

Verification

  • Config changes are active (SHOW and logs); allowed clients connect; denied clients are rejected.

Troubleshooting

Change not applied — Restart may be required; check for typos and include order. Connection rejected — Check pg_hba order and client address; ensure reload was done.

Next steps

Continue to