2.1.1 Setting Configuration Parameters

Table of Contents Previous Next


2 Database Administration : 2.1 Configuration Parameters : 2.1.1 Setting Configuration Parameters

Boolean. Acceptable values can be written as on, off, true, false, yes, no, 1, 0, or any unambiguous prefix of these.
Integer. Number without a fractional part.
Floating Point. Number with an optional fractional part separated by a decimal point.
String. Text value. Enclose in single quotes if the value is not a simple identifier or number (that is, the value contains special characters such as spaces or other punctuation marks).
Enum. Specific set of string values. The allowed values can be found in the system view pg_settings.enumvals. Enum values are case-insensitive.
Valid memory units are kB (kilobytes), MB (megabytes), and GB (gigabytes). Valid time units are ms (milliseconds), s (seconds), min (minutes), h (hours), and d (days). The multiplier for memory units is 1024.
The initial settings for almost all configurable parameters across the entire database cluster are listed in the configuration file, postgresql.conf. These settings are put into effect upon database server start or restart. Some of these initial parameter settings can be overridden as discussed in the following bullet points. All configuration parameters have built-in default settings that are in effect if not explicitly overridden.
The SQL commands ALTER DATABASE, ALTER ROLE, or ALTER ROLE IN DATABASE can be used to modify certain parameter settings. The modified parameter settings take effect for new sessions after the command is executed. ALTER DATABASE affects new sessions connecting to the specified database. ALTER ROLE affects new sessions started by the specified role. ALTER ROLE IN DATABASE affects new sessions started by the specified role connecting to the specified database. Parameter settings established by these SQL commands remain in effect indefinitely, across database server restarts, overriding settings established by the methods discussed in the second and third bullet points. Parameter settings established using the ALTER DATABASE, ALTER ROLE, or ALTER ROLE IN DATABASE commands can only be changed by: a) re-issuing these commands with a different parameter value, or b) issuing these commands using either of the SET parameter TO DEFAULT clause or the RESET parameter clause. These clauses change the parameter back to using the setting established by the methods set forth in the prior bullet points. See Section I, “SQL Commands” of Chapter VI “Reference” in the PostgreSQL Core Documentation for the exact syntax of these SQL commands.
Changes can be made for certain parameter settings for the duration of individual sessions using the PGOPTIONS environment variable or by using the SET command within the EDB-PSQL or PSQL command line terminal programs. Parameter settings made in this manner override settings established using any of the methods described by the second, third, and fourth bullet points, but only for the duration of the session.

2 Database Administration : 2.1 Configuration Parameters : 2.1.1 Setting Configuration Parameters

Table of Contents Previous Next