PostgreSQL Learning Notes (ii) configuration files

1, configuration Files

The configuration file controls the basic behavior of a PostgreSQL server instance, mainly including postgresql.conf, pg_hba.conf, pg_ident.conf

(1) postgresql.conf

This file contains common settings such as memory allocation, the default storage location for new database, the IP address of the PostgreSQL server, the location of the log, and many other settings. Version 9.4 introduces the

A new postgresql.auto.conf file that will be created or rewritten at any time by executing the Altersystem SQL command. The settings in this file override the settings in the postgresql.conf file.

(2) pg_hba.conf

This file is used to control access security and to manage client access to the PostgreSQL server, including: Which users are allowed to connect to which database, which IP or network segment IP connection is allowed

Access to this server, and the authentication mode used when specifying the connection

(3) pg_ident.conf

The authentication Mode field in the PG_HBA.CONF permission control information if specified as ident, the system tries to access the Pg_ident file when the user connects, and if the file exists, the system is based on the

The file content maps the operating system user who is currently performing the logon operation to the identity of the internal user of a PostgreSQL database to log on.

2, view the location of the configuration file: 

postgres = # selectname, setting from pg_settings where category = ‘File Locations’;
       name | setting
------------------- + ------------------------------ -----------
 config_file | /var/lib/pgsql/9.6/data/postgresql.conf
 data_directory | /var/lib/pgsql/9.6/data
 external_pid_file |
 hba_file | /var/lib/pgsql/9.6/data/pg_hba.conf
 ident_file | /var/lib/pgsql/9.6/data/pg_ident.conf
 

3.postgresql.conf
3.1. Key settings

postgres = # selectname, context, unit, setting, boot_val, reset_val from pg_settings where namein (‘listen_addresses‘, ‘max_connections’, ’shared_buffers’, ‘effective_cache_size’, ’work_mem’, ‘maintenance_work_mem’) order by context, name;
         name | context | unit | setting | boot_val | reset_val
---------------------- + ------------ + ------ + ------- -+ ----------- + -----------
 listen_addresses | postmaster | | * | localhost | *
 max_connections | postmaster | | 100 | 100 | 100
 shared_buffers | postmaster | 8kB | 16384 | 1024 | 16384
 effective_cache_size | user | 8kB | 524288 | 524288 | 524288
 maintenance_work_mem | user | kB | 65536 | 65536 | 65536
 work_mem | user | kB | 4096 | 4096 | 4096
(6 rows)
 

The context is set to postmaster, after changing this parameter, you need to restart the PostgreSQL service to take effect;

Set to user, then only need to perform a reload to take effect globally. Restarting the database service will terminate the active connection, but the reload will not.

The unit field indicates the unit of these settings

setting refers to the current settings; boot_val refers to the default settings; reset_val refers to the new settings after restarting the server or reloading the settings

After modifying the settings in postgresql.conf, be sure to check the settings and reset_val and make sure they are consistent, otherwise the settings are not effective, you need to restart the server or reload the settings

3.2, the difference between postgresql.auto.conf and postgresql.conf

For version 9.4 and later, the priority of Postgresql.auto.conf is higher than that of postgresql.conf. If there are configuration items with the same name in these two files, the system will preferentially select the value set by the former.

3.3, postgresql.conf the following network settings, modify these values must restart the database service

listen_addresses is generally set to localhost or local, but many people will set it to *, which means that any IP address of this machine can be connected to the Postgresql service

port default value is 5432

max_connections

3.4. The following four settings have a global impact on system performance. It is recommended that you find the optimal value through actual measurement in the actual environment

(1) share_buffers

    The size of the memory area used to cache recently accessed data pages. All user sessions can share this cache area

    Generally speaking, the bigger the better, it should be at least 25% of the total memory of the system, but it should not exceed 8GB, because after that, there will be a "diminishing marginal return" effect.

    Need to restart postgreSQL service

(2) effective_cache_size

The maximum cache that can be used during the execution of a query, including the part used by the operating system and the part used by PostgreSQL, the system does not actually allocate so much memory based on this value, but the planner will use this value to determine whether the system can provide Memory required during query execution. If this setting is set too small, which is far less than the actual amount of available memory in the system, it may cause misleading to the planner and make the planner think that the available memory of the system is limited, so he chooses not to use the index but to take a full table scan Although the index is fast, it requires more intermediate memory).

On a server dedicated to running the PostgreSQL database service, it is recommended to set the effective_cache_size value to half or more of the total system memory.

This setting can take effect dynamically, just reload.

(3) work_mem

This setting specifies the maximum amount of memory used to perform operations such as sorting, hash associations, and table scans.

This setting can take effect dynamically, just reload.

   (4) mining_work_mem

     This setting specifies the total amount of memory available for internal maintenance operations such as vaccum operations (that is, emptying records that have been marked as "deleted").

     Its value should not be greater than 1GB

This setting can take effect dynamically, just reload.

3.5 Modify parameter command

Alter system set work_mem = 8192;
Set the reload command

Select pg_reload_conf ();
3.6. When the postgresql.conf file is modified, the server crashes

The easiest way to locate this problem is to view the log file, which is located in the root directory of the postgresql data folder or the pg_log subfolder.

4. pg_hba.conf
cat /var/lib/pgsql/9.6/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD
 
# "local" isfor Unix domain socket connections only
local all all peer
# IPv4 localconnections:
host all all 0.0.0.0/0 trust
# IPv6 localconnections:
host all all :: 1/128 ident
# Allow replicationconnections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres :: 1/128 ident
(1) Identity verification mode, generally the following common options: ident, trust, md5 and password

Version 1 introduced the peer authentication mode.

Ident and peer modes are publicly available for Linux, Unix and Mac, not for windwos

Reject mode, its role is to reject all requests.

(2) If you put the + 0.0.0. / 0 reject + rule in front of + 127.0.0.1 / 32 trust +, then all local users cannot connect at this time, even if the rules below allow it.

(3) Each mode

trust is the most insecure authentication mode, which allows users to “self-certify and innocent”, that is, they can connect to the database without a password

md5 This mode is most commonly used and requires the initiator of the connection to carry a password encrypted with the md5 algorithm

password is not recommended because this mode uses a clear text password for authentication and is not secure

ident: In this authentication mode, the system maps the operating system user who initiated the request to the internal user of the PostgreSQL database, and logs in with the authority of the internal user, and no login password is required at this time. The mapping relationship between operating system users and users within the database is recorded in the pg_ident.conf file.

peer uses the operating system name of the initiator to authenticate

5. Reloading of configuration files
/usr/pgsql-9.6/bin/pg_ctlreload -D /var/lib/pgsql/9.6/data/
systemctlreload postgresql-9.6.service
selectpg_reload_conf ();


 



This article is from the "corasql" blog, please keep this source http://corasql.blog.51cto.com/5908329/1910485

Postgresql study notes (2) configuration file