PostgreSQL learning Manual (9) Server Configuration

1. Start and close server processes:
The following are the usage and common options of the pg_ctl command. It should be noted that this command is the encapsulation body of the S command, so it is more convenient to use than using Postgres directly.
Pg_ctl init [dB] [-D datadir] [-S] [-o "options"]
Pg_ctl start [-W] [-T secs] [-D datadir] [-S] [-l filename] [-o "options"]
Pg_ctl stop [-W] [-T secs] [-D datadir] [-S] [-M shutdown-mode]
Pg_ctl restart [-W] [-T secs] [-D datadir] [-S] [-M shutdown-mode]
Pg_ctl reload [-D datadir] [-S]
Pg_ctl status [-D datadir]
Pg_ctl promote [-D datadir] [-S]

Option	Description
-D	Database storage path
-L	Log File of the specified server process
-S	Only print error messages, not general information
-T secs	Number of seconds to wait when the-W option is used
-W	Wait until the database operation is completed (this option is the default option for stop)
-W	Do not wait for the completion of any operation
-- Help	Show Help Information
-- Version	Show version information
-M	You can specify the shutdown mode for stop and restart operations.
System shutdown mode
Smart	Do not receive new connections until all existing connections are disconnected.
Fast	If you do not receive new connection requests, close the established connection and exit the system.
immediate	exit now, however, recovery operations must be executed during restart

Here we only provide the most commonly used method, that is, the normal startup and shutdown of the database server.
# Start indicates to start the ipvs 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/startup. Log
# Stop indicates stopping the ipvs server process
#-M fast: when the system is shut down, use the fast Close mode.
/> Pg_ctl stop-m fast-w-D/opt/PostgreSQL/9.1/Data

Ii. Server Configuration:
1. Set parameters:
In PostgreSQL, all configuration parameter names are case-insensitive. Each parameter can accept four types of values: Boolean, integer, floating point, and string. The boolean values can be on, off, true, false, yes, no, 1, and 0. The configuration file that contains these parameters is PostgreSQL. conf The file is usually stored in the data directory initialized by initdb. See the following configuration snippet:
# This is a comment
Log_connections = Yes
Log_destination = 'syslog'
Search_path = '$ user, public'
The behavior comment line starting with the pound sign (#). If the configuration value contains numbers, it must be enclosed in single quotes. If the parameter value itself contains single quotes, we can write two single quotes (recommended) or enclose them with a backslash.
Note that not all configuration parameters can be modified dynamically 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 the modification command on the command line, such:
/> S-C log_connections = yes-C log_destination = 'syslog'
If the parameters set in the command line conflict with those in the configuration file, the parameters in the command line overwrite the existing parameter values in the configuration file. In addition, we can also modify the configuration information of a specified database or user by using PostgreSQL data definition commands such as alter database and alter user. The settings for the database will overwrite any settings provided from the Postgres command line or configuration file, and will be overwritten by the settings for the user, and will be overwritten by the options for each session. The following figure shows the priority of the PostgreSQL server when the server configuration conflicts, for example:
1). Session-based configuration;
2). User-based configuration;
3). Database-based configuration;
4). The configuration specified by the S command line;
5). configuration in the configuration file PostgreSQL. conf.
It should be noted that some settings can be set through the set command of PostgreSQL. For example, in Psql, We can enter:
Set enable_seqscan to off;
You can also use the show command to display the current value of the specified configuration, for example:
Show enable_seqscan;
At the same time, you can also manually query the pg_settings system table to retrieve the system parameters you are interested in.

Iii. Memory-related parameter configuration:
1. shared_buffers (integer ):
Set the amount of shared memory that can be used by the database server. By default, it can be set to 32 MB, but not less than kb. The higher the value, the better the system performance. This configuration parameter can only be set when the database is started.
If you have a dedicated database server with 1 GB memory or more, we recommend that you set this value to 25% of the system memory.

2. work_mem (integer ):
PostgreSQL determines whether to split a large result set into several temporary files of the same size as work_mem based on the size of work_mem. Obviously, the splitting result reduces the sorting speed. Therefore, adding work_mem helps increase the sorting speed. However, if multiple sorting operations exist in the system at the same time, the number of memory used by each operation is work_mem, therefore, we need to pay attention to this issue 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. The default value of this configuration is 16 Mb. Because each session can only execute one operation at a time, the usage frequency is not high, but these commands often consume a large amount of system resources. Therefore, these commands should be executed quickly.

 

 

 

Reprinted from Stephen Liu for the purposes of learning to add to favorites only.

1. Start and close server processes:
The following are the usage and common options of the pg_ctl command. It should be noted that this command is the encapsulation body of the S command, so it is more convenient to use than using Postgres directly.
Pg_ctl init [dB] [-D datadir] [-S] [-o "options"]
Pg_ctl start [-W] [-T secs] [-D datadir] [-S] [-l filename] [-o "options"]
Pg_ctl stop [-W] [-T secs] [-D datadir] [-S] [-M shutdown-mode]
Pg_ctl restart [-W] [-T secs] [-D datadir] [-S] [-M shutdown-mode]
Pg_ctl reload [-D datadir] [-S]
Pg_ctl status [-D datadir]
Pg_ctl promote [-D datadir] [-S]

Option	Description
-D	Database storage path
-L	Log File of the specified server process
-S	Only print error messages, not general information
-T secs	Number of seconds to wait when the-W option is used
-W	Wait until the database operation is completed (this option is the default option for stop)
-W	Do not wait for the completion of any operation
-- Help	Show Help Information
-- Version	Show version information
-M	You can specify the shutdown mode for stop and restart operations.
System shutdown mode
Smart	Do not receive new connections until all existing connections are disconnected.
Fast	If you do not receive new connection requests, close the established connection and exit the system.
immediate	exit now, however, recovery operations must be executed during restart

Here we only provide the most commonly used method, that is, the normal startup and shutdown of the database server.
# Start indicates to start the ipvs 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/startup. Log
# Stop indicates stopping the ipvs server process
#-M fast: when the system is shut down, use the fast Close mode.
/> Pg_ctl stop-m fast-w-D/opt/PostgreSQL/9.1/Data

Ii. Server Configuration:
1. Set parameters:
In PostgreSQL, all configuration parameter names are case-insensitive. Each parameter can accept four types of values: Boolean, integer, floating point, and string. The boolean values can be on, off, true, false, yes, no, 1, and 0. The configuration file that contains these parameters is PostgreSQL. conf The file is usually stored in the data directory initialized by initdb. See the following configuration snippet:
# This is a comment
Log_connections = Yes
Log_destination = 'syslog'
Search_path = '$ user, public'
The behavior comment line starting with the pound sign (#). If the configuration value contains numbers, it must be enclosed in single quotes. If the parameter value itself contains single quotes, we can write two single quotes (recommended) or enclose them with a backslash.
Note that not all configuration parameters can be modified dynamically 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 the modification command on the command line, such:
/> S-C log_connections = yes-C log_destination = 'syslog'
If the parameters set in the command line conflict with those in the configuration file, the parameters in the command line overwrite the existing parameter values in the configuration file. In addition, we can also modify the configuration information of a specified database or user by using PostgreSQL data definition commands such as alter database and alter user. The settings for the database will overwrite any settings provided from the Postgres command line or configuration file, and will be overwritten by the settings for the user, and will be overwritten by the options for each session. The following figure shows the priority of the PostgreSQL server when the server configuration conflicts, for example:
1). Session-based configuration;
2). User-based configuration;
3). Database-based configuration;
4). The configuration specified by the S command line;
5). configuration in the configuration file PostgreSQL. conf.
It should be noted that some settings can be set through the set command of PostgreSQL. For example, in Psql, We can enter:
Set enable_seqscan to off;
You can also use the show command to display the current value of the specified configuration, for example:
Show enable_seqscan;
At the same time, you can also manually query the pg_settings system table to retrieve the system parameters you are interested in.

Iii. Memory-related parameter configuration:
1. shared_buffers (integer ):
Set the amount of shared memory that can be used by the database server. By default, it can be set to 32 MB, but not less than kb. The higher the value, the better the system performance. This configuration parameter can only be set when the database is started.
If you have a dedicated database server with 1 GB memory or more, we recommend that you set this value to 25% of the system memory.

2. work_mem (integer ):
PostgreSQL determines whether to split a large result set into several temporary files of the same size as work_mem based on the size of work_mem. Obviously, the splitting result reduces the sorting speed. Therefore, adding work_mem helps increase the sorting speed. However, if multiple sorting operations exist in the system at the same time, the number of memory used by each operation is work_mem, therefore, we need to pay attention to this issue 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. The default value of this configuration is 16 Mb. Because each session can only execute one operation at a time, the usage frequency is not high, but these commands often consume a large amount of system resources. Therefore, these commands should be executed quickly.