PostgreSQL tutorial of: server configuration

Server process startup and shutdown:

The following is the usage and common options of the pg_ctl command. It is important to note that this command is an wrapper around the postgres command, so it is more convenient to use than 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]

选项	描述
-D	指定数据库存储的路径
-l	指定服务器进程的日志文件
-s	仅打印错误信息，不打印普通信息
-t SECS	当使用-w选项时等待的秒数
-w	等待直到数据库操作完成(对于stop而言，该选项时缺省选项)
-W	不等待任何操作的完成
--help	显示帮助信息
--version	显示版本信息
-m	对于stop和restart操作，可以指定关闭模式
系统关闭模式
smart	不在接收新的连接，直到当前已有的连接都断开之后才退出系统
fast	不在接收新的连接请求，主动关闭已经建立的连接，之后退出系统
immediate	立即退出，但是在restart的时候需要有恢复的操作被执行

  here we only show the most common usage, which is the normal startup and shutdown of the database server.
      #start means to start the postgres server process.
      #-D specifies the location 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
      # esstop means stop 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

ii. Server configuration:

1. Setting 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 containing these parameters is postgresql.conf, which is typically stored in the initdb initialized data (data) directory, as shown in the following configuration section:

    #  This is a comment 

    log_connections = yes

    log_destination = 'syslog'

    search_path = '$user, public'

 

The behavior comment line that starts with the       well number (#) needs to be enclosed in single quotes if the configuration value contains a number. If the parameter value itself contains single quotation marks, we can either write two single quotation marks (the recommended method) or enclose them with an inverse slant.      
The important thing to note here is that not all configuration parameters can be dynamically modified while the server is running, and some parameters are modified to take effect only after the server is restarted.
      PostgreSQL also provides another way to modify configuration parameters by directly executing modification commands on the command line, such as
 

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

 

      if the parameters set on the command line conflict with the parameters in the configuration file, the parameters given on the command line will override the values of the parameters already in the configuration file. In addition, we can modify the configuration information of the specified database or the specified user respectively through the data definition commands of PostgreSQL such as ALTER DATABASE and ALTER USER. The Settings for the database will override any Settings given from the postgres command line or configuration file, then be overwritten by the Settings for the user, and finally by the options per session. The following is the priority of which way the PostgreSQL server will take when a server configuration conflict occurs, such as

1). Session-based configuration;
      2).
3). Database based configuration;
      4). postgres command line specified configuration;
      5). Configuration given in the configuration file postgresql.conf.

      finally, some Settings can be set by PostgreSQL's set command. For example, in psql we can type
 

    SET ENABLE_SEQSCAN TO OFF;

 

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

    SHOW ENABLE_SEQSCAN;

 

At the same time, we can manually query the pg_settings system table to retrieve the system parameters of interest.

3. Memory-related parameter configuration:

      1. shared_buffers(integer) :

      sets the amount of Shared memory that the database server can use. By default it can be set to 32MB, but not less than 128KB. The higher the value, the better the performance of the system. This configuration parameter can only be set at database startup.
At this point, if you have a dedicated database server with memory of 1G or more, we recommend setting this value to 25% of system memory.
       
      2. work_mem(integer) :

When       PostgreSQL performs a sort operation, it determines whether to split a large result set into several small temporary files about the size of work_mem based on the size of work_mem. Obviously the result of the split is that it slows down the sort. So adding work_mem will help speed up the sorting. However, it is important to note that if there are multiple sort 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 be careful when setting this value.
       
      3. maintence_work_mem(integer) :

      specifies the maximum amount of memory to be used for maintenance operations, such as VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, etc. The default value for this configuration is 16MB. Because each session can only perform one of these operations at a time, the frequency of use is not high, but these instructions tend to consume a lot of system resources, so they should be quickly executed as soon as possible.