postgresql: alter system

Time:2021-8-28

Alter system is used to modify the configuration of the whole server level. It is implemented by modifying the file. It can only be executed by superuser and cannot be executed in a transaction (the configuration file postgresql.auto.conf has been modified and cannot be rolled back)

After configuration, the parameters will not take effect immediately, and the system needs to reload the parameter (PG)_ reload_ conf(), pg_ CTL reload, or send a SIGHUP to postmaster). Some parameters take effect only when the server is started. You need to restart the server (PG)_ ctl restart)。

usage

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

Usage examples

alter system

ALTER SYSTEM set max_prepared_transactions to 10;

postgres=# show max_prepared_transactions;
 max_prepared_transactions
---------------------------
 0
(1 row)

[[email protected]]# cat /usr/local/pgsql/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_prepared_transactions = '10'

Non startup parameters take effect, and there is no need to restart the server
For example???

select pg_reload_conf();
pg_ctl xxx reload

The startup parameters take effect and the service needs to be restarted
Such as Max_ prepared_ transactions

pg_ctl xxx restart

reference material
https://www.postgresql.org/do…