PostgreSQL Tutorial (11): Server Configuration _postgresql

Source: Internet
Author: User
Tags create index postgresql syslog

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.
#-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
#-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):


    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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.