ALTER SYSTEM
ALTER SYSTEM — change a server configuration parameter
Synopsis
ALTER SYSTEM SETconfiguration_parameter{ TO | = } {value| 'value' | DEFAULT }ALTER SYSTEM RESETconfiguration_parameterALTER SYSTEM RESET ALL
Description
ALTER SYSTEM is used for changing server configuration parameters across the entire database cluster. It can be more convenient than the traditional method of manually editing thepostgresql.conf file.ALTER SYSTEM writes the given parameter setting to thepostgresql.auto.conf file, which is read in addition topostgresql.conf. Setting a parameter toDEFAULT, or using theRESET variant, removes that configuration entry from thepostgresql.auto.conf file. UseRESET ALL to remove all such configuration entries.
Values set withALTER SYSTEM will be effective after the next server configuration reload, or after the next server restart in the case of parameters that can only be changed at server start. A server configuration reload can be commanded by calling the SQL functionpg_reload_conf(), runningpg_ctl reload, or sending aSIGHUP signal to the main server process.
Only superusers can useALTER SYSTEM. Also, since this command acts directly on the file system and cannot be rolled back, it is not allowed inside a transaction block or function.
Parameters
configuration_parameterName of a settable configuration parameter. Available parameters are documented inChapter 19.
valueNew value of the parameter. Values can be specified as string constants, identifiers, numbers, or comma-separated lists of these, as appropriate for the particular parameter.
DEFAULTcan be written to specify removing the parameter and its value frompostgresql.auto.conf.
Notes
This command can't be used to setdata_directory, nor parameters that are not allowed inpostgresql.conf (e.g.,preset options).
SeeSection 19.1 for other ways to set the parameters.
Examples
Set thewal_level:
ALTER SYSTEM SET wal_level = replica;
Undo that, restoring whatever setting was effective inpostgresql.conf:
ALTER SYSTEM RESET wal_level;
Compatibility
TheALTER SYSTEM statement is aPostgreSQL extension.