Setting Configuration Parameters v10
This section provides an overview of how configuration parameters are specified and set.
Each configuration parameter is set using a
name/value pair. Parameter names are case-insensitive. The parameter name is typically separated from its value by an optional equals sign
The following is an example of some configuration parameter settings in the
Parameter values are specified as one of five types:
- 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.
Some settings specify a memory or time value. Each of these has an implicit unit, which is kilobytes, blocks (typically 8 kilobytes), milliseconds, seconds, or minutes. Default units can be found by referencing the system view
pg_settings.unit. A different unit can be specified explicitly.
Valid memory units are
MB (megabytes), and
GB (gigabytes). Valid time units are
h (hours), and
d (days). The multiplier for memory units is 1024.
The configuration parameter settings can be established in a number of different ways:
There is a number of parameter settings that are established when the Advanced Server database product is built. These are read-only parameters, and their values cannot be changed. There are also a couple of parameters that are permanently set for each database when the database is created. These parameters are read-only as well and cannot be subsequently changed for the database.
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.
Configuration parameters in the
postgresql.conffile are overridden when the same parameters are included in the
ALTER SYSTEMcommand is used to manage the configuration parameters in the
Parameter settings can be modified in the configuration file while the database server is running. If the configuration file is then reloaded (meaning a SIGHUP signal is issued), for certain parameter types, the changed parameters settings immediately take effect. For some of these parameter types, the new settings are available in a currently running session immediately after the reload. For other of these parameter types, a new session must be started to use the new settings. And yet for other parameter types, modified settings do not take effect until the database server is stopped and restarted. See the following section of the PostgreSQL Core Documentation for information on how to reload the configuration file:
The SQL commands
ALTER ROLE, or
ALTER ROLE IN DATABASEcan be used to modify certain parameter settings. The modified parameter settings take effect for new sessions after the command is executed.
ALTER DATABASEaffects new sessions connecting to the specified database.
ALTER ROLEaffects new sessions started by the specified role.
ALTER ROLE IN DATABASEaffects 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 ROLE, or
ALTER ROLE IN DATABASEcommands 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 DEFAULTclause or the
RESET parameterclause. These clauses change the parameter back to using the setting established by the methods set forth in the prior bullet points. See the “SQL Commands” section 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
PGOPTIONSenvironment variable or by using the
SETcommand within the
PSQLcommand 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.
The configuration parameters in the
postgresql.conf file specify server behavior with regards to auditing, authentication, encryption, and other behaviors. On Linux and Windows host, the
postgresql.conf file resides in the
data directory under your Advanced Server installation.
Parameters that are preceded by a pound sign (#) are set to their default value. To change a parameter value, remove the pound sign and enter a new value. After setting or changing a parameter, you must either
restart the server for the new parameter value to take effect.
postgresql.conf file, some parameters contain comments that indicate
change requires restart. To view a list of the parameters that require a server restart, execute the following query at the psql command line:
On a Linux system, you can reload the system configuration parameter values by navigating through the EDB Postgres menu to the
Advanced Server 10 menu; then, navigate through the
Expert Configuration menu, selecting
Reload Configuration. Reloading the configuration parameters does not require Advanced Server users to log out of their current Advanced Server sessions.
On a Windows system, you will find the
Reload Configuration menu selection on the
Appropriate authentication methods provide protection and security. Entries in the
pg_hba.conf file specify the authentication method or methods that the server will use when authenticating connecting clients. Before connecting to the server, you may be required to modify the authentication properties specified in the
When you invoke the
initdb utility to create a cluster,
initdb creates a
pg_hba.conf file for that cluster that specifies the type of authentication required from connecting clients. To modify the
pg_hba.conf file, open the file with your choice of editor. After modifying the authentication settings in the
pg_hba.conf file, use the services utility (Windows), or use the following command to restart the server and apply the changes:
On Linux 7.x:
For more information about authentication and modifying the
pg_hba.conf file, see the PostgreSQL Core Documentation at: