PostgreSQL configuration file postgresql.conf

PostgreSQL configuration file postgresql.conf
The configuration file mainly affects the basic behavior of this server instance, such as the number of connections allowed, the maximum amount of memory allowed for the operation, specifying which users can connect to the database in which way, etc. Of course, all of these have a default value when the database is installed, but if you need to customize your database, you can modify these values ​​to meet your needs.

1. Location of configuration file
Since it is called a configuration file, it must first be a file. We know that Oracle’s configuration file is divided into pfile and spfile. The postgreSQL configuration file is a bit similar to pfile and can be edited directly. Because the installation environment is different, the location of the configuration file is also different. We first need to find where the configuration file is.
Enter the psql environment. If it fails, check the contents of the alarm, whether it is not switched to the postgres user or the database is not started. If not started, use the following command to start.

/etc/init.d/postgresql start

Of course, it can also be set to start automatically at boot. Remember to use su-to switch to the root user in advance.

chkconfig -add postgresql

After entering the psql environment, we use the following command to find the location of the database configuration file.

select name,setting from pg_settings where category=’File Locations’;

Basically, everyone should be familiar with the select statement query. If you are not familiar with it, you can go to the Internet to find a tutorial to supplement the tutorial. A few points to note here are actually the same as oracle’s syntax. The string is expanded with single quotes, and the end is terminated with a semicolon. Entering without a semicolon is actually a line break.
Pankajconnect: The psql that comes with postgres can be automatically completed with the tab key, you can move the cursor with the arrow keys, and use the up and down keys to find the past commands. It is much easier to use than the sqlplus that comes with Oracle. Oracle needs to install additional plug-ins to implement these.

2.postgresql.conf and postgresql.auto.conf
This configuration file mainly contains some common settings, which is the most important configuration file. However, since version 9.4, postgresql introduced a new configuration file postgresql.auto.conf. In the case of the same configuration, the system first executes the auto.conf file. In other words, the auto.conf configuration file takes precedence over the conf file . It is worth noting that the auto.conf file must be modified using alter system in psql, and conf can be modified directly in the text editor.

Pankajconnect: Since postgresql is the main open source competitor of oracle, we can compare it with oracle. oracle is divided into pfile and spfile. When the oracle database starts, it will first find spfile. If pfile is not used, Oracle uses pfile to enter the system. , You can use pfile to generate spfile, and start the database again with spfile to boot. In postgresql, like oracle, postgresql.conf and pfile are both in text format, and postgresql.auto.conf and spfile are both in binary format. But the difference is that the two files in postgres take effect at the same time, only the priority is different. When alter system set is used to modify the postgres configuration file, it only affects the contents of the auto.conf file. When the parameter is set back to default, this configuration of the auto.conf file will be deleted, and the configuration of the conf file will be used again. This is different from oracle.
By the way, after the oracle administrator uses alter system set to modify the parameters to the spfile,
create pfile from spfile; It’s a good habit.
Here we demonstrate a few key settings
Use commands
select name,context,unit,setting,boot_val,reset_val
from pg_settings
where name in
(‘listen_addresses’,’max_connection’,’shared_buffers’,’effective_cache_size’,’work_mem’,’maintenance_work_mem’
)
order by context,name;

The meaning of the columns queried here are as follows:

context: When this value is postmaster, you need to restart the database to take effect, and the user only needs to reload the configuration to take effect globally. Restarting the database means that all services are interrupted, and reloading the configuration does not.

unit: Unit, which means the unit of the following values. The unit mainly takes care of readability. For example, if you have 2G memory, your unit is 2048 in MB, but if you want to use 8KB, it is 256000, so the readability is too bad.

setting: current setting
boot_val: default setting
reset_val: settings after restart or reload configuration

You can see whether the configuration takes effect by checking whether setting and reset_val are the same.

The meaning of each main parameter is as follows:
listen_addresses: The IP that can be used to connect to the server. The general initial value is localhost or local, which means that only the local machine can connect to the database. This is generally set to “*”, allowing all IPs to connect to the database.

port: Listening port, the default is 5432.
Qianyue: I don’t think it’s a special reason not to change the default listening port. Sometimes it brings inexplicable trouble to operation and maintenance. To inform many collaboration departments, the related management software must also be modified. There is something wrong with a confusion. Still follow the “non-essential, do not change” principle to configure it.

max_connections: The maximum number of concurrent connections allowed, in simple terms is how many people can connect to your database at the same time.
Pankajconnect: How much this setting actually depends on your needs and the system’s load capacity (the memory has a greater impact) is not a simple linear accumulation. Affected by many factors, in a production environment, some connections will occupy a lot of resources. Try to set this value after investigation. If the resources consumed exceed the range that your host can bear, it will be down. Another thing to note is that a connection is not a host, but a connection process. So you can’t simply think of how many people have to connect to set the maximum number of connections.

share_buffers: The size of the shared cache, which mainly stores the most recently accessed data pages. This cache area is shared by all user sessions .
Qianyue: The cache is shared by all users, so it is very important. This setting has a great impact on the query speed. Generally speaking, the bigger the better, at least 25% of the system memory, but not more than 8G, which is no pressure for mainstream servers. If it exceeds 8G, according to Gerson’s first rule, it can be introduced that the speed increase obtained by consuming more memory is very small, and it is not worth the gains.

Zero-based remarks: Gerson’s first rule, also known as the law of diminishing marginal benefits, because one variable is changing and the other variable is unchanged, resulting in more and more investment in the first variable and less and less benefits. For example: for our database cache, all of our data is like a book on the bookshelf. I want to find a “Hamlet” and I need to turn the bookshelf. There are many bookshelves, which are very slow to find. In the end, we spent 30 I found it in minutes, we put this “Hamlet” in a small cart, this small cart is share_buffers, because “Hamlet” is very popular, many people look for it, when others look for “Hamlet”, I am very simple It took 1 minute to flip it in the small cart to give him, for this group of people to find out if the speed has become faster. But if this small cart is made bigger and bigger, like the memory usage far exceeds 8G, it is similar to the size of a big truck, which is filled with unpopular books, some even a few days to find one or two, we It was also put in the truck, at this time we look for “Hamlet”, is it going to be hard. Therefore, the result we get is that the larger the share_buffers of the stroller exceeds the balance point, the greater the cost of our system. In the end, the efficiency of finding some popular books cannot be guaranteed, and it does take care of some niche users. , But the experience of most users is getting worse, in fact, it’s more than worth it.

effective_cache_size: The maximum number of caches that can be used during query execution. This includes the cost of the database and the cost of the system.

Thousand months: This value is generally set to more than 50% of the system memory. The system does not really see this value set to 4G and allocates 4G. This value is mainly for the optimizer. The optimizer will use this value to determine whether the system can provide enough memory for the execution plan made by the planner. For example: we need to query some data. If the index (fast speed, but the intermediate memory occupied is high) requires 5G memory, and effective_cache_size is only set to 4G, then the optimizer will give up the fast index and use the occupied memory. Low full table scan for query, full table scan will be very slow, which is not what we want to see.

maintenance_work_mem: Sets the system memory available for performing vaccum operations. In general, this value should not exceed 1G.
Qianyue: vaccum is a long story series. Simply put, after the delete operation is performed, we are only marking the deleted records, and there is no real physical deletion and no space is released. Therefore, although this part of the deleted record shows that it has been deleted, other newly added records still cannot occupy its physical space. We call this space occupation HWM (highest water mark), similar to the figure below

HWM can only be increased in theory, if we want to free up space, we need to perform vaccum operation. Of course, there are similar functions on the oracle system: alter table table name shrink space; The working situation is probably as shown in the figure below.

There are many other parameters. You can refer to the official documentation. Here are some unique features to talk about, so I won’t introduce them one by one.
Thanks for reading this post. you can followup with me to get the latest new updates.