Setting configuration parameters v14
Set each configuration parameter using a name/value pair. Parameter names are not case sensitive. The parameter name is typically separated from its value by an optional equals sign (
This an example shows some configuration parameter settings in the
Parameter values are specified as one of five types:
- Boolean. Acceptable values are
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 enclosed in single quotes if the value isn't 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 not case sensitive.
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. You can find default units by referencing the system view
pg_settings.unit. You can specify a different 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.
You can set the configuration parameter settings in a number of different ways:
A number of parameter settings are set when the EDB Postgres Advanced Server database product is built. These are read-only parameters, and you can't change their values. A couple of parameters are also permanently set for each database when the database is created. These parameters are read-only and you can't later change them 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. You can override some of these initial parameter settings. 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
You can modify parameter settings 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, you must start a new session to use the new settings. And yet for other parameter types, modified settings don't take effect until the database server is stopped and restarted. See the PostgreSQL core documentation for information on how to reload the configuration file:
You can use the SQL commands
ALTER ROLE, or
ALTER ROLE IN DATABASEto modify certain parameter settings. The modified parameter settings take effect for new sessions after you execute the command.
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 other methods. Parameter settings established using the
ALTER ROLE, or
ALTER ROLE IN DATABASEcommands can be changed only by either:
Reissuing these commands with a different parameter value.
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 set by the other methods. See the PostgreSQL core documentation for the exact syntax of these SQL commands.
You can make changes for certain parameter settings for the duration of individual sessions using the
PGOPTIONSenvironment variable or by using the
SETcommand in the EDB-PSQL or PSQL command-line programs. Parameter settings made this way override settings established using any of the methods descussed earlier, but only during that session.
The configuration parameters in the
postgresql.conf file specify server behavior with regard to auditing, authentication, encryption, and other behaviors. On Linux and Windows hosts, the
postgresql.conf file resides in the
data directory under your EDB Postgres 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:
Appropriate authentication methods provide protection and security. Entries in the
pg_hba.conf file specify the authentication methods that the server uses with connecting clients. Before connecting to the server, you might need to modify the authentication properties specified in the
When you invoke the initdb utility to create a cluster, the utility creates a
pg_hba.conf file for that cluster that specifies the type of authentication required from connecting clients. You can modify this file. After modifying the authentication settings in the
pg_hba.conf file, restart the server and apply the changes.
For more information about authentication and modifying the
pg_hba.conf file, see the PostgreSQL core documentation.
When the server receives a connection request, it verifies the credentials provided against the authentication settings in the
pg_hba.conf file before allowing a connection to a database. To log the
pg_hba.conf file entry to authenticate a connection to the server, set the
log_connections parameter to
ON in the
A record specifies a connection type, database name, user name, client IP address, and the authentication method to authorize a connection upon matching these parameters in the
pg_hba.conf file. Once the connection to a server is authorized, you can see the matched line number and the authentication record from the
This example shows a log detail for a valid
pg_hba.conf entry after successful authentication: