.. SPDX-FileCopyrightText: 2021 Dalibo .. .. SPDX-License-Identifier: GPL-3.0-or-later .. _pgconf: PostgreSQL configuration ======================== The PostgreSQL configuration of instances created by pglift is *managed* as explained in respective section about :ref:`site configuration `. This means that the overall configuration of an instance is built from several sources: - actual ``postgresql.conf`` present in instance data directory, - the site-wise ``postgresql.conf`` template file, - any configuration items that satellite components might define (e.g. pgBackRest would set ``archive_command`` etc.), - user-supplied configuration items. This process applies at instance creation, but also happens anytime the instance is updated through pglift's API (be it ``instance alter`` command, the ``pgconf`` command set described below, or the declarative Ansible interface). Accordingly: - when a change is passed through user-supplied configuration items (either from the declarative API or from ``pgconf set|edit`` commands), these take precedence; - when removing a configuration item (either through the declarative API or from ``pgconf remove`` command), this will apply only if the same setting is not defined in a previous source (e.g. ``postgresql.conf`` template); - so, to effectively remove a configuration item, one needs to *reset* it to its default value (e.g. if the site template sets ``work_mem`` to 32MB but you want to set it back to its default value of 4MB, then you would have to override the template value with ``pglift pgconf [...] set work_mem=4MB``). .. warning:: Editing ``postgresql.conf`` file in data directory directly might result in surprising effects upon further instance change: typically, a setting removed from the configuration file through direct edition might come back after applying back the instance state. .. warning:: Parameters change will have no effect for parameters if they are set by satellite plugins using templated configuration files. This is the case for example for the templated ``postgresql/pgbackrest.conf`` configuration file which goal is to set specific values for a given list of parameters such as ``wal_level``. ``pgconf`` commands ------------------- The ``pglift pgconf`` command line entry point exposes commands to manage configuration of a PostgreSQL instance. .. code-block:: console $ pglift pgconf --help Usage: pglift pgconf [OPTIONS] COMMAND [ARGS]... Manage configuration of a PostgreSQL instance. Options: -i, --instance / Instance identifier; the / prefix may be omitted if there's only one instance matching . Required if there is more than one instance on system. --help Show this message and exit. Commands: edit Edit managed configuration. remove Remove configuration items. set Set configuration items. show Show configuration (all parameters or specified ones). It operates only on configuration files and does not assume that the instance is started. To make changes effective, the user may need to restart or reload the instance, see :doc:`/user/ops/instance`. .. _warn-managed-parameters: .. warning:: Some configuration settings should not be modified through this command as they may be needed for other satellite services to work. Typically, the ``port`` setting is one of them. Similarly, the selected backup system may assume that some parameter are set to particular values. Show the configuration ^^^^^^^^^^^^^^^^^^^^^^ View parameters: .. code-block:: console $ pglift pgconf -i main show log_connections log_disconnections # log_connections = off # log_disconnections = off View all parameters: .. code-block:: console $ pglift pgconf -i main show archive_command = '/usr/bin/pgbackrest --config-path=/etc/pgbackrest --stanza=14-main archive-push %p' archive_mode = on wal_level = 'replica' cluster_name = 'main' shared_buffers = '128MB' effective_cache_size = '5 GB' unix_socket_directories = '/var/run/postgresql' log_destination = 'stderr' logging_collector = on port = 5454 max_connections = 100 dynamic_shared_memory_type = 'posix' max_wal_size = '1GB' min_wal_size = '80MB' log_timezone = 'Europe/Paris' datestyle = 'iso, mdy' timezone = 'Europe/Paris' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' Change the configuration ^^^^^^^^^^^^^^^^^^^^^^^^ .. code-block:: console $ pglift pgconf -i main set log_connections=on log_disconnections=on INFO configuring PostgreSQL INFO instance 14/main needs reload due to parameter changes: log_connections, log_disconnections INFO reloading PostgreSQL configuration for 14/main log_connections: None -> True log_disconnections: None -> True .. note:: To directly edit the configuration file, use: .. code-block:: console $ pglift pgconf -i main edit this will open your text editor with the configuration. Remove parameters configuration ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. code-block:: console $ pglift pgconf -i main remove log_connections log_disconnections INFO configuring PostgreSQL INFO instance 14/main needs reload due to parameter changes: log_connections, log_disconnections INFO reloading PostgreSQL configuration for 14/main log_connections: True -> None log_disconnections: True -> None .. _pgconf-managed-parameters: Changing managed configuration parameters ----------------------------------------- If, considering the :ref:`warning above `, a managed configuration setting is really meant to be overridden, using ``ALTER SYSTEM`` is probably the best option. For example, changing the ``archive_command`` setting is not possible when the pgBackRest component is enabled: .. code-block:: console $ pglift pgconf set archive_command=/bin/true INFO configuring PostgreSQL changes in 'archive_command' not applied hint: either these changes have no effect (values already set) or specified parameters are already defined in an un-managed file (e.g. 'postgresql.conf') $ pglift pgconf show archive_command archive_command = '/usr/bin/pgbackrest --config-path=/etc/pgbackrest --stanza=app --pg1-path=/srv/pgsql/16/main/data archive-push %p' but executing an ``ALTER SYSTEM`` statement (here through ``database run`` command) would work: .. code-block:: console $ pglift database run -d postgres "ALTER SYSTEM SET archive_command TO '/bin/true'" > Confirm execution of "ALTER SYSTEM SET archive_command TO '/bin/true'" on postgres of 16/main? [y/n] (y): y INFO running "ALTER SYSTEM SET archive_command TO '/bin/true'" on postgres database of 16/main INFO ALTER SYSTEM $ pglift pgconf show archive_command archive_command = '/bin/true' Finally, to restore the setting: .. code-block:: console $ pglift database run -d postgres "ALTER SYSTEM RESET archive_command" > Confirm execution of "ALTER SYSTEM RESET archive_command" on postgres of 16/main? [y/n] (y): y INFO running "ALTER SYSTEM RESET archive_command" on postgres database of 16/main INFO ALTER SYSTEM $ pglift pgconf show archive_command archive_command = '/usr/bin/pgbackrest --config-path=/etc/pgbackrest --stanza=app --pg1-path=/srv/pgsql/16/main/data archive-push %p' ``pghba`` commands ------------------ The ``pglift pghba`` command line entry point exposes commands to manage HBA records related to a PostgreSQL instance. This subcommand handles ``pg_hba.conf`` configuration file for standalone instances, and the Patroni YAML configuration for instances managed by Patroni. .. code-block:: console Usage: pglift pghba [OPTIONS] COMMAND [ARGS]... Manage entries in HBA configuration of a PostgreSQL instance. Options: -i, --instance / Instance identifier; the / prefix may be omitted if there's only one instance matching . Required if there is more than one instance on system. --help Show this message and exit. Commands: add Add a record in HBA configuration. edit Edit managed HBA records. remove Remove a record from HBA configuration. .. note:: To directly edit the HBA records, use: .. code-block:: console $ pglift pghba -i main edit this will open your text editor with the HBA records.