↑↓ select↵ open⌫ change scopeOpen full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Wiki / SQL Commands

ALTER SYSTEM

change a server configuration parameter

English manual coverage begins with PostgreSQL 6.4. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

9.49.59.61011121314151617181920
anchor
SQL-ALTERSYSTEM
file
sql-altersystem.html
lang
en
name
ALTER SYSTEM
purpose
change a server configuration parameter
purpose zh
更改服务器配置参数
sections same as
Unknown
slug
18
synopsis text
ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT } ALTER SYSTEM RESET configuration_parameter ALTER SYSTEM RESET ALL

Syntax

ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
Syntax diagrams

Read left to right. Branches are choices, bypasses are optional, loops repeat. Select an underlined parameter to open its rule.

ALTER SYSTEM SET configuration_parameter TO = value , DEFAULT
ALTER SYSTEM · Syntax 2
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM · Syntax 3
ALTER SYSTEM RESET ALL

Definition PG 18

ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER 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 the postgresql.conf file. ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf. Setting a parameter to DEFAULT, or using the RESET variant, removes that configuration entry from the postgresql.auto.conf file. Use RESET ALL to remove all such configuration entries.

Values set with ALTER 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 function pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal to the main server process.

Only superusers and users granted ALTER SYSTEM privilege on a parameter can change it using ALTER 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_parameter

Name of a settable configuration parameter. Available parameters are documented in Chapter 19.

value

New 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. Values that are neither numbers nor valid identifiers must be quoted. DEFAULT can be written to specify removing the parameter and its value from postgresql.auto.conf.

For some list-accepting parameters, quoted values will produce double-quoted output to preserve whitespace and commas; for others, double-quotes must be used inside single-quoted strings to get this effect.

Notes

This command can't be used to set data_directory, allow_alter_system, nor parameters that are not allowed in postgresql.conf (e.g., preset options).

See Section 19.1 for other ways to set the parameters.

ALTER SYSTEM can be disabled by setting allow_alter_system to off, but this is not a security mechanism (as explained in detail in the documentation for this parameter).

Examples

Set the wal_level:

ALTER SYSTEM SET wal_level = replica;

Undo that, restoring whatever setting was effective in postgresql.conf:

ALTER SYSTEM RESET wal_level;

Compatibility

The ALTER SYSTEM statement is a PostgreSQL extension.

See Also

SET, SHOW

Compare versions

Complete source facts

synopsis html

ALTER SYSTEM SET <em class="replaceable"><code>configuration_parameter</code></em> { TO | = } { <em class="replaceable"><code>value</code></em> [, ...] | DEFAULT }

ALTER SYSTEM RESET <em class="replaceable"><code>configuration_parameter</code></em>
ALTER SYSTEM RESET ALL

Source references

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · wiki-repair-20261023:7908127cf36dba8d1e3cb0cc1e4c1fd8cf98da2f5d903dc679740fead419dd86

Text language: en · wiki-repair-20261023:7908127cf36dba8d1e3cb0cc1e4c1fd8cf98da2f5d903dc679740fead419dd86