DocsICP FilingConsole
官方文档
首页

Parameter management

更新时间：
复制 MD 格式
产品详情
我的收藏

In PolarDB for PostgreSQL Lightweight Edition, you can adjust cluster parameter configurations by executing SQL commands or by modifying the postgresql.conf or config.yaml files.

Basic concepts

The parameter type determines the syntax used to set the parameter. The following five parameter types are supported:

  • Boolean: The value for this parameter type can be set to on/off, true/false, yes/no, 1/0, or any unambiguous prefix of these values.

  • String: The value for this parameter type must be enclosed in single quotation marks. If the value is a simple number or identifier, you can omit the quotation marks.

  • Integer and floating-point number:

    • For floating-point parameter values, only one decimal place is allowed.

    • Do not use thousand separators or quotation marks.

    • For numeric parameter values with implicit units, such as memory or time, the default unit is used if you omit the unit (see pg_settings.unit). For convenience, you can also specify a unit that is different from the default. For example, if you set a time value to 120 ms, ms becomes the actual unit for the parameter.

    Note

    • Units are case-sensitive. A space is required between the numeric value and the unit.

    • Available memory units include B (bytes), kB (kilobytes), MB (megabytes), and GB (gigabytes). The conversion factor between adjacent memory units is 1024.

    • Available time units include ms (milliseconds), s (seconds), min (minutes), h (hours), and d (days).

  • Enumeration: The value for this parameter type is specified in the same way as a string parameter, but the value is case-insensitive. You can find the available values for a specific parameter by querying pg_settings.enumvals.

Configure parameters using SQL

You can use the following SQL statements in PolarDB for PostgreSQL Lightweight Edition to modify cluster configurations.

Global configuration

  • ALTER SYSTEM: Updates global configurations, which is equivalent to editing the postgresql.conf file. For parameters that require a restart, run the pdbcli restart cluster command to restart the cluster. The new configuration then takes effect on all cluster nodes. For parameters that do not require a restart, run pg_ctl reload on each node or call the pg_reload_conf() SQL function to reload the new configuration file on the nodes.

  • ALTER DATABASE: Updates database-level configurations.

  • ALTER ROLE: Updates user-level configurations.

Session-level configuration

SET configuration_parameter TO value;: Modifies the value of a target parameter for the current session. This does not affect other sessions.

Run SHOW configuration_parameter; to view the current value of the target parameter. You can also use the pg_settings system view to view and change session-level parameter values.

Note

Using the UPDATE command on the setting column has the same effect as issuing a SET command. For example, SET configuration_parameter TO DEFAULT; is equivalent to UPDATE pg_settings SET setting = reset_val WHERE name= 'configuration_parameter'.

Modify parameters using pdbcli

To modify multiple parameters, add them to all.db.vars.polardb_custom_params in the config.yaml file. For more information about the contents of the configuration file, see Configuration file example.

After you modify the config.yaml file, run the pdbcli restart cluster command to restart the cluster. The new parameter configuration is applied to all cluster nodes after the restart. Each time you restart the cluster using pdbcli, the parameters in the postgresql.conf file on each node are updated to match the settings in the configuration template.

Note

The parameter names and units must be accurate. A cluster restart is also required, even for parameters that do not typically require one.

Modify parameters using the postgresql.conf file

Important

  • Some parameter modifications require a database restart to take effect.

  • Invalid parameter settings in the configuration file are ignored during SIGHUP processing, but related information is recorded in the logs.

After the cluster directory is initialized, a postgresql.conf file is created in the data directory by default. You can edit the postgresql.conf file to set default parameter values for the cluster. For example:

log_connections = yes
log_destination = 'syslog'
search_path ='"$user", public'
shared_buffers = 128MB

  • Each line consists of a parameter name (key) and its corresponding value.

  • The # symbol indicates that the line is a comment.

  • Parameter values that are not simple identifiers or numbers must be enclosed in single quotation marks. To include a single quotation mark in a value, precede it with a backslash.

  • If the same parameter appears multiple times with different values, the last value set is used.

After you edit the postgresql.conf file, you must restart the cluster for parameters that require a restart. To do this, run the pdbcli restart cluster command. The new parameter configuration is then applied to all cluster nodes. For parameters that do not require a restart, run pg_ctl reload on each node or call the pg_reload_conf() SQL function to reload the new configuration file on the nodes.

Note

In addition to postgresql.conf, the data directory of PolarDB for PostgreSQL Lightweight Edition also contains the postgresql.auto.conf file. This file stores global configurations set by the ALTER SYSTEM SET command. The file has the same format as postgresql.conf, but it cannot be manually edited. If a parameter is configured in both files, the setting in postgresql.auto.conf takes precedence.

Previous：Data securityNext：Import and migration