postgres configuration auto.conf - ghdrako/doc_snipets GitHub Wiki

Every configuration parameter is associated with a context, and depending on the context, you can apply changes with or without a cluster restart. Available contexts are as follows:

  • internal: A group of parameters that are set at compile time and therefore cannot be changed at runtime.
  • postmaster: All the parameters that require the cluster to be restarted (that is, to kill the postmaster process and start it again) to activate them.
  • sighup: All the configuration parameters that can be applied with a SIGHUP signal sent to the postmaster process, which is equivalent to issuing a reload signal in the operating system service manager.
  • backend and superuser-backend: All the parameters that can be set at runtime but will be applied to the next normal or administrative connection.
  • user and superuser: A group of settings that can be changed at runtime and are immediately active for normal and administrative connection.

The postgresql.auto.conf file has the very same syntax as the main postgresql.conf file but is automatically overwritten by PostgreSQL when the configuration is changed at runtime directly within the system, by means of specific administrative statements such as ALTER SYSTEM. The postgresql.auto.conf file is always loaded at the very last moment, therefore overwriting other settings. In a fresh installation, this file is empty, meaning it will not overwrite any other custom setting.

PostgreSQL configuration files are stored in the /etc/postgresql/<version>/main directory. For example, if you install PostgreSQL 12, the configuration files are stored in the /etc/postgresql/12/main directory.

The configuration file is reread whenever the main server process receives a SIGHUP signal; this signal is most easily sent by running pg_ctl reload from the command line or by calling the SQL function pg_reload_conf(). The main server process also propagates this signal to all currently running server processes, so that existing sessions also adopt the new values (this will happen after they complete any currently-executing client command). Alternatively, you can send the signal to a single server process directly.

PostgreSQL data directory contains a file postgresql.auto.conf , which has the same format as postgresql.conf but is intended to be edited automatically, not manually.This file holds settings provided through the ALTER SYSTEM command. This file is read whenever postgresql.conf is, and its settings take effect in the same way. Settings in postgresql.auto.conf override those in postgresql.conf.

The system view pg_file_settings can be helpful for pre-testing changes to the configuration files, or for diagnosing problems if a SIGHUP signal did not have the desired effects.

  • ALTER SYSTEM command provides an SQL-accessible means of changing global defaults; it is functionally equivalent to editing postgresql.conf
  • ALTER DATABASE command allows global settings to be overridden on a per-database basis
  • ALTER ROLE command allows both global and per-database settings to be overridden with user-specific values.

System view pg_settings can be used to view and change session-local values.

  • select * from pg_settings
  • SHOW ALL
  • SET configuration_parameter TO DEFAULT;
  • UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';
⚠️ **GitHub.com Fallback** ⚠️