Setting configuration parameters v17

Suggest edits

Set each configuration parameter using a name/value pair. Parameter names aren't case sensitive. The parameter name is typically separated from its value by an optional equals sign (=).

This example shows some configuration parameter settings in the postgresql.conf file:

# This is a comment
log_connections = yes
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

Types of parameter values

Parameter values are specified as one of five types:

  • Boolean — Acceptable values are 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 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 unit explicitly.

Valid memory units are:

  • kB (kilobytes)
  • MB (megabytes)
  • GB (gigabytes).

Valid time units are:

  • ms (milliseconds)
  • s (seconds)
  • min (minutes)
  • h (hours)
  • d (days).

The multiplier for memory units is 1024.

Specifying configuration parameter settings

A number of parameter settings are set when the EDB Postgres Extended 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. However, there are a number of ways to specify the configuration parameter settings:

  • The initial settings for almost all configurable parameters across the entire database cluster are listed in the postgresql.conf configuration file. 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 unless you explicitly override them.

  • Configuration parameters in the postgresql.conf file are overridden when the same parameters are included in the postgresql.auto.conf file. Use the ALTER SYSTEM command to manage the configuration parameters in the postgresql.auto.conf file.

  • 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 others, you must start a new session to use the new settings. And for some others, 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 DATABASE, ALTER ROLE, or ALTER ROLE IN DATABASE to modify certain parameter settings. The modified parameter settings take effect for new sessions after you execute the command. 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 other methods. You can change parameter settings established using the ALTER DATABASE, ALTER ROLE, or ALTER ROLE IN DATABASE commands by either:

    • Reissuing these commands with a different parameter value.

    • Issuing these commands using the SET parameter TO DEFAULT clause or the RESET parameter clause. These clauses change the parameter back to using the setting set by the other methods. See the PostgreSQL core documentation for the syntax of these SQL commands.

  • You can make changes for certain parameter settings for the duration of individual sessions using the PGOPTIONS environment variable or by using the SET command in the EDB-PSQL or PSQL command-line programs. Parameter settings made this way override settings established using any of the methods discussed earlier, but only during that session.

Modifying the postgresql.conf file

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 Extended 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 reload or restart the server for the new parameter value to take effect.

In the postgresql.conf file, some parameters contain comments that indicate change requires restart. To view a list of the parameters that require a server restart, use the following query at the psql command line:

SELECT name FROM pg_settings WHERE context = 'postmaster';

Modifying the pg_hba.conf file

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 pg_hba.conf file.

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 postgresql.conf file.

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 pg_hba.conf file.

This example shows a log detail for a valid pg_hba.conf entry after successful authentication:

2020-05-08 10:42:17 IST LOG:  connection received: host=[local]
2020-05-08 10:42:17 IST LOG:  connection authorized: user=u1 database=edb
application_name=psql
2020-05-08 10:42:17 IST DETAIL:  Connection matched pg_hba.conf line 84:
"local   all             all               md5"
← Prev

Database configuration

↑ Up

Database configuration