PostgreSQL Tutorial (11): Server Configuration _postgresql

First, server process startup and shutdown:

The following is the use of the PG_CTL command and the common options, and it should be noted that the command is a wrapper for the Postgres command, so it is more convenient to use than the direct use of postgres.

Copy Code code as follows:

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]

Options	Describe
-D	Specify the path to the database store
-L	Specify log files for server processes
-S	Print only error messages, do not print general information
-t secs	The number of seconds to wait when the-w option is used
-W	Wait until the database operation is complete (the default option for stop)
-W	Do not wait for any action to complete
--help	Display Help information
--version	Display version Information
-M	For stop and restart operations, you can specify the shutdown mode
System shutdown mode
Smart	Do not exit the system until a new connection is received until the current connection is disconnected
Fast	Do not accept new connection requests, actively close established connections, and then exit the system
Immediate	Exit now, but restore operations are performed at restart

Here we just give the most common usage, that is, the normal startup and shutdown of the database server.
#start表示启动postgres服务器进程.
#-d Specifies the repository path for the initial directory of the database server.
#-l Specifies the log file for 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表示停止postgres服务器进程
#-m Fast uses the fast shutdown mode when shutting down the system.
/> Pg_ctl stop-m fast-w-d/opt/postgresql/9.1/data

Second, server configuration:

1. Set Parameters:
In PostgreSQL, all configuration parameter names are case insensitive. Each parameter can accept four types of values, which are Boolean, Integer, floating-point, and string. Where the Boolean value can be on, off, TRUE, FALSE, YES, NO, 1, and 0. The configuration file that contains these parameters is postgresql.conf, which is usually stored in the INITDB initialized data directory, as shown in the following configuration fragment:

Copy Code code as follows:

# This is a note
Log_connections = yes
log_destination = ' syslog '
Search_path = ' $user, public '

The action comment line that starts with the pound sign (#), if the configuration value contains a number, it needs to be enclosed in single quotes. If the value of the parameter itself contains single quotes, we can write two single quotes (the recommended method) or surround it with a reverse tilt.
It should be noted here that not all configuration parameters can be dynamically modified while the server is running, and some of the parameters are modified only until the server restarts.
PostgreSQL also provides another way to modify configuration parameters, that is, to execute the Modify command directly on the command line, such as:

Copy Code code as follows:

/> postgres-c log_connections=yes-c log_destination= ' syslog '

If the parameters in the command line settings and the parameters in the configuration file conflict with each other, the parameters given in the command line override the parameter values already in the configuration file. In addition, we can modify the configuration information for the specified database or the specified user individually through the PostgreSQL data definition commands such as ALTER DATABASE and ALTER USER. The settings for the database will overwrite any settings from the Postgres command line or configuration file, which will then be overwritten by the user's settings, and will be overwritten by the options for each session. The following is how the PostgreSQL server will take precedence when a server configuration conflict occurs, such as:

1. Session-based configuration;
2). Based on the user configuration;
3). Based on the database configuration;
4). Postgres the configuration specified by the command line;
5). Configuration in the configuration file postgresql.conf.

Finally, some settings can be set through the PostgreSQL set command, as in Psql we can enter:

Copy Code code as follows:

SET Enable_seqscan to Off;

You can also display the current value of a specified configuration through the show command, such as:

Copy Code code as follows:

Show Enable_seqscan;

At the same time, we can also manually query the Pg_settings system tables to retrieve the system parameters of interest.

Three, 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 point, if you have a dedicated database server with a memory of 1G or more, then we recommend setting this value to 25% of the system's memory.

2. Work_mem (integer):

PostgreSQL When a sort operation is performed, the size of the WORK_MEM determines whether a large result set is split into small and work_mem approximately the size of a temporary file. Obviously the result of splitting is to reduce the speed of sorting. So adding WORK_MEM helps to improve the speed of sorting. It should be noted, however, that if there are multiple sorting operations in the system, the amount of memory used for each operation is work_mem, so we need to be aware of this when we set the value.

3. Maintence_work_mem (integer):

The

    specifies the maximum amount of memory used in a maintenance operation, such as vacuum, CREATE index, and alter TABLE ADD FOREIGN key, and the default value for this configuration is 16MB. Because each session can only perform one operation at a time, the frequency is not high, but these instructions tend to consume more system resources and should be quickly completed as soon as possible.