PostgreSQL database postgresql.conf Some related parameters

Listen_addresses: #指定数据库用来监听客户端连接的TCP/IP address, the default is the value is *, indicating that the database will be on the computer running the data on all IP addresses on the machine to listen to user requests, can be written as the name of the machine, can also be written as an IP address, Different values are separated by commas and, if set to localhost, indicate that the database can only accept local client connection requests and cannot accept remote client connection requests

listen_addresses = ' 0.0.0.0 ' #监听IPv4的所有IP.

Port: #指定数据库监听户端连接的TCP端口. The default value is 5432

Max_connections: #数据库可以同时建立的最大的客户端连接的数目

Max_connections = 1000# Maximum allowable 1000 connections.

Superuser_reserved_connections: #为超级用户保留的可用连接数.

Superuser_reserved_connections = 3# retains 3 available connections for super users.

Unix_socket_directory: #默认的unix socket file in/tmp,

unix_socket_directory = '/pgdata/socket_dir ' is modified to $pgdata to ensure security.

Unix_socket_permissions: #默认的访问权限是0777,

Unix_socket_permissions = 0700 #修改为0700更安全.

Tcp_keepalives_idle = 60

Tcp_keepalives_interval = 10

Tcp_keepalives_count = 6 #空闲多少秒后, send TCP keepalives packets to idle connected clients, the default for Linux is 2 hours.

Shared_buffers: #共享缓存大小

Shared_buffers = 32mb# The default value is 32MB, when the value of share_buffers is large to a certain extent, the bottleneck is not the memory, but the CPU.

Temp_buffers: #决定存放临时表的数据缓冲区中的数据块的个数, the size of each data block is 8KB. Temporary table buffers are stored in the private memory of each database process, not in the shared memory of the database

Work_mem: #数据库的排序操作和哈希表使用的内存缓冲区的大小

Maintenance_work_mem: #决定数据库的维护操作使用的内存空间的大小

vacuum*: #清空 (garbage collection related parameter setting)

bgwriter_delay: #bgwriter process Wake-up cycle, the default is 200ms,bgwriter Process Execution once will pause 200ms and wake up to perform the next operation, when the database write operations are very frequent, 200ms may be too long, Causes other processes to take too much time to perform bgwriter operations.

wal_level: #数据库预写日志级别, If you need to do database Wal log backup, at least set to archive level, if need to do hot_standby, need to set to Hot_standby of course hot_ Standby means that the Wal record is more detailed if there is no intention to do hot_standby set the lower performance the better.

Wal_level = Hot_standby: #设置为 hot_standby

The configuration of the log output.

log_destination = ' Csvlog '

Logging_collector = On

log_directory = ' Pg_log '

Log_filename = ' Postgresql-%y-%m-%d_%h%m%s.log '

Log_file_mode = 0600

Log_truncate_on_rotation = On

Log_rotation_age = 1d

Log_rotation_size = 10MB

log_min_duration_statement:# This parameter adjusts the SQL to log that records execution time of more than 1 seconds, and is typically used to track which SQL execution time is long.

log_checkpoints:# Records every checkpoint to the log.

Log_lock_waits = on: #锁等待打开

deadlock_timeout = 1s: #锁超时时间, the record lock waits more than 1 seconds, and is typically used to troubleshoot problems on business logic.

log_statement = ' DDL ': #记录DDL语句, typically used to track dangerous operations in a database.

Autovacuum = on: #默认autovacuum就是打开的

log_autovacuum_min_duration = 0: #记录所有的autovacuum操作.