Question: Which changes to postgresql.conf require a restart?

Answer

PostgreSQL has various configuration parameters that can be set in the postgresql.conf file. These parameters control different aspects of the PostgreSQL server's behavior. When you make changes to this configuration file, the effect of these changes depends on how the parameter is classified: some changes take effect immediately, some take effect upon reloading the configuration, and some require a complete server restart.

Changes That Require a Server Restart

Changes to parameters that require a server restart are typically those that affect core server processes or memory allocation. Here are some examples:

• listen_addresses: This parameter determines the IP address(es) that the server listens on for connections from client applications.
• port: This sets the TCP port the server listens on; changing it requires a restart.
• max_connections: The maximum number of concurrent connections to the PostgreSQL database. Increasing this number may require additional system resources.
• shared_buffers: This allocates memory for caching data directly by PostgreSQL, affecting performance.
• wal_level: Determines the level of details written in the WAL (Write-Ahead Logging). Higher levels increase the amount of log data generated.

Reloading vs. Restarting

For parameters that don't require a full restart, PostgreSQL allows you to reload the configuration file without restarting the server. You can do this with the following SQL command:

SELECT pg_reload_conf();

This will apply changes for parameters where the context is sighup (which stands for "signal hang up"). Examples include log_directory, log_filename, and many others related to logging and runtime monitoring.

Conclusion

Understanding which parameters require a server restart is crucial for minimizing downtime, especially in production environments. When in doubt, refer to the official PostgreSQL documentation about specific parameters to see their context and whether they need a reload or a restart.