Introduction to the Postgresql database 14-server setup
Introduction to the Postgresql database 14-server setup
server configuration
Contents
1 Setting parameters
2 File location
3 Links and authorization
3.1 Link settings
3.2 Security and authorization
4 Resource consumption
4.1 Memory
4.2 Use of Kernel Resources
4.3 cost-based vacuum delay
4.4 Backend writer process
4.5 Asynchronous behavior
5 Write header file log
5.1 Settings
5.2 Check points
5.3 Archiving
5.4 Streaming Copy
5.5 Asynchronous replication
5.6 Backup server
6 Query design
6.1 Design method configuration
6.2 Design cost constant
6.3 Genetic query optimization
6.4 Other design options
7 Error reporting and logging
7.1 Where to record
7.2 When to record
7.3 What to record
7.4 Use CSV format log output
8 Running time statistics
8.1 Query and Index Statistics
8.2 Statistical monitoring
9 Automatic emptying
10 Default customer link
10.1 Statement behavior
10.2 Regions and formatting
10.3 Other default values
11 Lock management
12 version and platform compatible
12.1 Previous PostgreSQL versions
12.2 Platform and customer compatibility
13 Error handling
14 preset options
15 Customization options
16 Developer options
17 Short short option
There are many configuration parameters that affect the behavior of the database system. In the first section of this chapter, we describe how to set configuration parameters, and the remaining sections discuss each parameter in detail.
1 Setting parameters
All parameter names are not case sensitive, and each parameter takes one of the five types: Boolean, integer, floating-point character or enumeration. Boolean values can be written as on, off, true, false, yes, no, 1,0 (not case sensitive) or a clear prefix of any of these values.
Some settings represent a memory or time value. Each value has an implied unit, which is either kilobytes, block (usually 8 kilobytes), milliseconds, seconds, or minutes. The default unit can be found by referring to pg_settings.unit. For convenience, different units can also be clearly specified. Valid memory units are usually kB, MB, and GB; valid time units are ms (milliseconds), s (seconds), min (minutes), h (hours), and d (days). Note that the multiplier of the memory unit is 1024, not 1000.
Enumerated parameters and character parameters are expressed in the same way, but only limited to some restricted values. The allowed values can be found in pg_settings.enumvals. Enumeration parameter values are not case sensitive.
One way to set these parameters is to modify the file postgersql.conf, which is usually in the data directory. (A default copy is installed when the database cluster directory is initialized). Examples of the file style:
- this is a comment
Log_connections=yes Log_destination=’syslog’ Search_path=’”$user”,public’ Shared_buffers=128MB
Specify one parameter per line, and the equal sign between the name and the value is optional. Spaces are not important, blank lines are ignored. The alarm sign (#) indicates that the rest of the line is a comment. Parameter values that are not simple identifiers or numbers must be enclosed in single quotes. To embed a single quotation mark in the parameter value, or use two quotation marks or backslashes.
In addition to parameter settings, the postgersql.conf file can contain include directives, which specify other files to be read and processed, at this point as if it were inserted into the configuration file. Include directives are like:
Include ‘filename’
If the file name is not an absolute path, it is treated as a relative path containing the reference configuration file, and inclusions can be embedded.
Whenever the main server process receives a SIGHUP signal (it can be sent by pg_ctl reload), it will reread the configuration file, and the main server process will also send the signal to all currently running server processes so that the existing part is also obtained The new value. In addition, you can send signals directly to a single server process. Some parameters can only be set when the server is started, and any changes in the configuration file are ignored until the server is restarted.
The second way to set these configuration parameters is to give them a command line option in the postgres command, like:
Postgres –c log_connections=yes –c log_destination=’syslog’
Command line options override any conflicting settings in postgresql.conf. Note that this means that you cannot change the on-the-fly value by editing postgresql.conf, so although the command line method may be convenient, it will destroy your flexibility.
Occasionally it is useful to give only a command line option to a particular part. The environment variable PGOPTIONS can be used for this purpose on the client side:
Env PGOPTIONS=’-c geqo=off’ psql
(It is suitable for any client application based on libpq, not only psql). Note that it is not suitable for parameters that are fixed when the server starts or must be specified in postgresql.conf.
Moreover, it is possible to assign a series of parameter settings to a user or database. Whenever a part is started, the default values of the participating users and the database will be loaded. The commands ALTER USER and ALTER DATABASE are used to configure these settings respectively. Perdatabase settings override anything received from the postgres command-line or the configuration file, and in turn are overridden by per-user settings; both are overridden by per-session settings. Use the SET command to change some parameters in a single SQL section, for example :
SET ENABLE_SEQSCAN TO OFF; If SET is allowed, it overrides all other sources of parameter values. Some parameters cannot be changed by SET: for example, if they control the behavior that cannot be changed without restarting the entire PostgreSQL server. Similarly, some SET or ALTER parameter modifications require super user permission.
The SHOW command allows checking the current values of all parameters.
The virtual table pg_settings also allows to display and update login runtime parameters. Refer to section 45.62 for detailed details and descriptions of different variable types and when they can be changed. Pg_settings is equivalent to SHOW and SET, but it is more convenient to use because it can be combined with other tables or use any desired selection conditions. It also contains more information about what values are allowed for the parameters.
2 File location
In addition to the postgresql.conf file already mentioned, PostgreSQL also uses two other manually edited configuration files, which manage client authentication (their use is discussed in Chapter 19). By default, all three configuration files are stored in the data directory of the database cluster. The parameter configuration file described in this section is placed anywhere. (This can ease management, especially since it is simpler to ensure that the configuration files are properly backed up.)
Data_directory(string)
Specify the directory to store the data. This parameter can only be set when the server is started.
Config_flie(string)
Specify the man n server configuration file (usually called postgresql.conf). This parameter can only be set in the postgres command line.
Hba_file(string)
Specify a configuration file (usually called pg_hba.conf) for host-based authentication. This parameter is only set when the server is started.
Ident_file(string)
Specify the configuration file (usually called pg_ident.conf) for the username mapping in Section 19.2. This parameter is only set when the server starts.
External_pid_file(string)
Specify the name of the additional process id (PID) file, which can be established and used by the server management program, and the parameters are only set when the server is started.
In the default installation, none of the above parameters are explicitly set. On the contrary, the data directory is specified by the -D command option or the PGDATA environment variable, and the configuration file can be found in the data directory.
If you want to put the configuration file in another directory than the data directory, the postgres-D command line option or the PGDATA environment variable must point to the directory containing the configuration file, and the data_directory parameter must be set in postgresql_conf to show that the data directory is actually placed Where. Note that data_directory ignores the location of the data directory pointed to by -D and PGDATA, but does not ignore the location of the configuration file.
If you want, you can use the parameters config_file, hba_file and/or ident_file to individually specify the configuration file name and location. config_file can only be specified in the postgres command line, but others can be set in the main configuration file. If the three parameters and data_directory are explicitly set, there is no need to specify -D or PGDATA.
When setting any of these parameters, a relative path relative to the postgres startup directory will be interpreted.
3 Link and authentication
3.1 Link settings
Listen_addresses(string)
Specify the TCP/IP address to connect from the client application. The value takes the form of a comma separated host name and/or numeric IP address. The dedicated entry* is equivalent to all available IP interfaces. Entry 0.0.0.0 allows all IPv4 addresses, :: allows all IPv6 addresses. If the list is empty, the server does not listen to any IP interface. In this case, only the Unix-domain interface can connect to it. The default value is localhost, which only allows local TCP/IP “loop” connections. When the client application (Chapter 19) allows precise control over who can access the server, listen_addresses controls which interface accepts connection attempts, and which can help organize repeated malicious connection requests to enter insecure network interfaces. This parameter can only be set when the server is started.
Port（integer）
The TCP port the server listens to, the default is 5432. Note that all IP addresses the server listens to use the same port number. This parameter can only be set when the server is started.
Max_connections(integer)
Determine the maximum number of simultaneous connections to the database server. The default is 100, but it may be less if the kernel setting does not support it. This parameter can only be set when the server is started.
Increasing this parameter may cause PostgreSQL to require more system V shared memory or semaphores than allowed by the default configuration of your operating system. If necessary, please refer to section 17.4.1 for information on how to adjust these parameters.
If you are running a backup server, you must set this value to be the same or higher than the value of the management server.
Superuser_reserved_connections(integer)
Determine the number of connection “slots” reserved by the PostgreSQL super user for the connection, most of the max_connections connections can be activated at the same time,
Thanks for reading this.
