Postgresql 中文操作指南

ALTER SYSTEM

ALTER SYSTEM — 更改服务器配置参数

ALTER SYSTEM — change a server configuration parameter

Synopsis

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

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

Description

ALTER SYSTEM 用于更改整个数据库集群中的服务器配置参数。这比手动编辑 postgresql.conf 文件的传统方法更加方便。 ALTER SYSTEM 将给定的参数设置写入 postgresql.auto.conf 文件，该文件除 postgresql.conf 之外还会被读取。将参数设置为 DEFAULT ，或使用 RESET 变体，会从 postgresql.auto.conf 文件中删除该配置条目。使用 RESET ALL 删除所有此类配置条目。

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.

使用 ALTER SYSTEM 设置的值将在下次服务器配置重新加载后生效，或者在只能在服务器启动时更改的参数的情况下在下次服务器重新启动后生效。可以通过调用 SQL 函数 pg_reload_conf() 、运行 pg_ctl reload ，或向主服务器进程发送 SIGHUP 信号来命令服务器配置重新加载。

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.

只有对某个参数授予 ALTER SYSTEM 权限的超级用户和用户可以使用 ALTER SYSTEM 来更改它。此外，由于此命令直接作用于文件系统并且无法回滚，因此不允许在事务块或函数内使用它。

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 20.

  • 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

此命令不能用于设置 data_directory ，也不能用于不允许在 postgresql.conf 中设置的参数（例如 preset options ）。

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

请参阅 Section 20.1 了解设置参数的其他方法。

See Section 20.1 for other ways to set the parameters.

Examples

设置 wal_level ：

Set the wal_level:

ALTER SYSTEM SET wal_level = replica;

撤消此设置，恢复 postgresql.conf 中生效的任何设置：

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

ALTER SYSTEM RESET wal_level;

Compatibility

ALTER SYSTEM 语句是 PostgreSQL 扩展。

The ALTER SYSTEM statement is a PostgreSQL extension.

See Also

SET , SHOW

SET, SHOW