Product Documentation

Appearance

ALTER SYSTEM ​

Alter the system by setting or resetting the value of a specific configuration parameter.

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

You must be a superuser to run this command, and you cannot run it inside a transaction block.

ALTER SYSTEM modifies behavior for all future sessions on the system but not current sessions. See Precedence of Settings for Configuration Parameters.

Parameter changes made with ALTER SYSTEM do not take effect until the configuration is reloaded (via PG_RELOAD_CONF() or the database is stopped and restarted (via ybcli commands). See Configuration Parameters for more details.

Syntax ​

configuration_parameter
Specify a system configuration parameter that is modifiable. You can use the SHOW ALL command to return a list of parameters; however, not all of these are modifiable.
value
Specify a new value for the parameter, using a quoted string or a number, as appropriate for the parameter in question.
DEFAULT
Set the configuration parameter to its default value (equivalent to using RESET).
RESET | RESET ALL
Reset one or all configuration parameters to their default values. Reset values are subject to the rules under Precedence of Settings for Configuration Parameters.

Examples ​

The following ALTER SYSTEM command takes effect when the configuration has been reloaded. After the reload, the new setting applies to all sessions for all regular users. The changes will not apply to sessions run by yellowbrick or other superusers.

premdb=# show idle_session_timeout;
 idle_session_timeout 
----------------------
 1h
(1 row)
premdb=# alter system set idle_session_timeout to '10min';
WARNING:  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
ALTER SYSTEM

After a reload (or a server restart, though a restart is not needed in this specific case), user bobr and all other non-superusers will see and be subject to the new timeout setting:

premdb=# \c premdb bobr
Password for user bobr: 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "bobr".
premdb=> show idle_session_timeout;
 idle_session_timeout 
----------------------
 10min
(1 row)

To reset the idle_session_timeout parameter to its default value (1h), run the following command as a superuser:

premdb=# alter system reset idle_session_timeout;
WARNING:  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
ALTER SYSTEM

Precedence of Settings for Configuration Parameters ​

Note the following rules of precedence when you modify system configuration parameters:

  • The ALTER SYSTEM command sets values for parameters that are applied globally to all future sessions (assuming that a database restart is done for those parameters that require a restart to take effect).
  • You can override these global settings for specific users by using ALTER ROLE commands. An ALTER ROLE...SET command sets a parameter value for all sessions that are run by the specified user or role.
  • You can override global and user-specific parameters by using SET commands. A SET command sets a parameter value for the current session only. No other sessions are affected.

See also the SET, RESET, ALTER ROLE, and SHOW commands.

Parent topic:SQL Commands