PostgreSQL Tutorial (11): Server Configuration

1. Start and shut down the server process:

The following are the usage methods and common options of the pg_ctl command. It should be pointed out that this command is the encapsulation of the postgres command, so it is more convenient to use than to directly use postgres.

Options	describe
-D	Specify the path to the database storage
-l	Specify the log file for the server process
-s	Only print error messages, not normal messages
-t SECS	Number of seconds to wait when using the -w option
-w	Wait until the database operation is completed (for stop, the default option when this option is completed)
-W	No waiting for any operation to complete
--help	Show help information
--version	Display version information
-m	For stop and restart operations, you can specify a shutdown mode
System shutdown mode
smart	No new connection is received and the system is exited until all the existing connections are disconnected.
fast	Not receiving new connection requests, actively close the established connection, and then exit the system
immediate	Exit immediately, but when restarting, there is a recovery operation required to be executed

Here we just give the most commonly used method of use, namely the normal startup and shutdown of the database server.
#start means starting the postgres server process.
#-D Specifies the storage path of the initial directory of the database server.
#-l Specify the log file of the database server process
    /> pg_ctl -w start -D /opt/PostgreSQL/9.1/data -l /opt/PostgreSQL/9.1/data/pg_log/
#stop means stopping the postgres server process
#-m fast uses fast's shutdown mode when shutting down the system.
    /> pg_ctl stop -m fast -w -D /opt/PostgreSQL/9.1/data

2. Server configuration:

1. Set parameters:
In PostgreSQL, all configuration parameter names are case-insensitive. Each parameter can accept four types of values, namely Boolean, integer, floating point number, and string. The boolean values ​​can be ON, OFF, TRUE, FALSE, YES, NO, 1 and 0. The configuration file containing these parameters is that the file is usually stored in the data directory initialized by initdb, see the following configuration fragment:

A behavior comment line starting with a pound sign (#). If the configuration value contains a number, it needs to be enclosed in single quotes. If the parameter value itself contains single quotes, we can write two single quotes (recommended method) or wrap it around with a backslash.
It should be noted here that not all configuration parameters can be dynamically modified when the server is running. Some parameters can only take effect after the server is restarted.
PostgreSQL also provides another method to modify configuration parameters, that is, directly execute modification commands on the command line, such as:
 
If the parameters set by the command line conflict with the parameters in the configuration file at this time, the parameters given in the command line will overwrite the existing parameter values ​​in the configuration file. In addition, we can also modify the configuration information of the specified database or the specified user through PostgreSQL data definition commands such as ALTER DATABASE and ALTER USER. The settings for the database will overwrite any settings given from the postgres command line or configuration file, and will then be overwritten by the settings for the user, and will finally be overwritten by the options for each session. Here is the priority of what method PostgreSQL server will adopt when there is a conflict in server configuration, such as:

1). Session-based configuration;
2). Based on user configuration;
3). Database-based configuration;
4). The configuration specified by the postgres command line;
5). Configuration given in the configuration file.

Finally, it should be noted that some settings can be set through the PostgreSQL set command, such as in psql we can enter:
 

You can also use the show command to display the current value of the specified configuration, such as:
 
At the same time, we can also manually query the pg_settings system table to retrieve the system parameters of interest.

3. Memory-related parameter configuration:

    1. shared_buffers(integer)：

Set the amount of shared memory that the database server can use. By default, it can be set to 32MB, but not less than 128KB. Because the higher the value is set, the better the performance of the system. This configuration parameter can only be set when the database is started.
At this time, if you have a dedicated database server with 1G memory or more, then we recommend setting this value to 25% of the system memory.
    
    2. work_mem(integer)：

When PostgreSQL performs sorting operations, it will decide whether to split a large result set into several small temporary files of the same size as work_mem based on the size of work_mem. Obviously the result of the split is to slow down the sorting speed. Therefore, increasing work_mem helps to improve the speed of sorting. However, it should be noted that if there are multiple sorting operations in the system at the same time, the amount of memory used by each operation when sorting is work_mem, so we need to pay attention to this problem when setting this value.
    
    3. maintence_work_mem(integer)：

Specifies the maximum number of memory used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY, etc. The default value of this configuration is 16MB. Because each session can only perform one operation at the same time, it is not used frequently, but these instructions often consume more system resources, so these instructions should be executed quickly as soon as possible.