Postgresql篇：Postgresql 13.7 postgresql.conf配置文件详解

逃离我推掉我的手 2024-03-23 00:20 109阅读 0赞

`postgresql.conf`是PostgreSQL数据库服务器的主要配置文件之一，它包含了许多重要的参数，用于配置数据库服务器的各种行为。下面是一些常用的参数的详细说明：

1.  `listen_addresses`：这个参数指定PostgreSQL服务器监听的IP地址。默认情况下，它设置为localhost，只允许本地连接。如果你想允许来自其他IP地址的连接，你需要将它设置为你的服务器的公共IP地址。
2.  `port`：这个参数指定PostgreSQL服务器监听的端口号。默认情况下，它设置为5432。
3.  `max_connections`：这个参数指定PostgreSQL服务器允许的最大连接数。默认情况下，它设置为100。
4.  `shared_buffers`：这个参数指定PostgreSQL服务器用于缓存数据的内存大小。默认情况下，它设置为128MB。
5.  `work_mem`：这个参数指定每个查询可用的内存量。默认情况下，它设置为4MB。
6.  `maintenance_work_mem`：这个参数指定PostgreSQL服务器用于维护操作的内存量。默认情况下，它设置为64MB。
7.  `effective_cache_size`：这个参数指定PostgreSQL服务器可以使用的系统缓存的大小。默认情况下，它设置为4GB。
8.  `checkpoint_timeout`：这个参数指定PostgreSQL服务器执行自动检查点的时间间隔。默认情况下，它设置为5分钟。
9.  `wal_level`：这个参数指定PostgreSQL服务器记录事务日志的级别。默认情况下，它设置为minimal。
10. `max_wal_size`：这个参数指定PostgreSQL服务器允许的最大事务日志文件大小。默认情况下，它设置为1GB。
11. `min_wal_size`：这个参数指定PostgreSQL服务器允许的最小事务日志文件大小。默认情况下，它设置为80MB。
12. `max_worker_processes`：这个参数指定PostgreSQL服务器允许的最大后台工作进程数。默认情况下，它设置为8
13. `log_line_prefix`: 这个参数指定日志信息的前缀，可以包括时间、用户名、数据库名、进程ID等信息。默认情况下，它设置为"%t \[%p\]: \[%l-1\] user=%u,db=%d,"，表示输出日志信息的时间、进程ID、日志级别减1、用户名和数据库名等信息。
14. `log_destination`：这个参数指定PostgreSQL服务器的日志输出目的地，可以是stderr、syslog、csvlog等。默认情况下，它设置为stderr。
15. `log_min_messages`：这个参数指定日志输出的最小级别。默认情况下，它设置为WARNING，表示只输出警告级别及以上的日志信息。
16. `datestyle`：这个参数指定日期的格式。默认情况下，它设置为ISO，表示使用ISO标准的日期格式。
17. `timezone`：这个参数指定时区。默认情况下，它设置为UTC。
18. `default_statistics_target`：这个参数指定查询优化器在收集统计信息时使用的样本数目。默认情况下，它设置为100。
19. `autovacuum`：这个参数指定是否启用自动VACUUM。默认情况下，它设置为on。
20. `vacuum_cost_limit`：这个参数指定VACUUM操作的成本限制，当VACUUM操作的成本超过该限制时，它将停止执行。默认情况下，它设置为200。
21. `autovacuum_vacuum_scale_factor`: 这个参数指定自动VACUUM操作的触发阈值，当表中已删除行的占比达到该阈值时，自动触发VACUUM操作。默认情况下，它设置为0.2。
22. `autovacuum_analyze_scale_factor`: 这个参数指定自动VACUUM操作的触发阈值，当表中新增行的占比达到该阈值时，自动触发ANALYZE操作。默认情况下，它设置为0.1。
23. `autovacuum_vacuum_cost_limit`: 这个参数指定自动VACUUM操作的成本限制，当VACUUM操作的成本超过该限制时，它将停止执行。默认情况下，它设置为-1，表示不设置成本限制。
24. `autovacuum_vacuum_scale_factor`: 这个参数指定自动VACUUM操作的触发阈值，当表中已删除行的占比达到该阈值时，自动触发VACUUM操作。默认情况下，它设置为0.2。
25. `checkpoint_completion_target`: 这个参数指定执行检查点操作时，需要将多少缓存中的数据写入磁盘。默认情况下，它设置为0.5。
26. `default_text_search_config`: 这个参数指定全文搜索使用的默认配置。默认情况下，它设置为pg\_catalog.simple。
27. `max_parallel_workers`: 这个参数指定允许的最大并行工作进程数。默认情况下，它设置为8。
28. `max_parallel_workers_per_gather`: 这个参数指定每个查询允许的最大并行工作进程数。默认情况下，它设置为2。
29. `jit`: 这个参数指定是否启用JIT(即时编译)优化。默认情况下，它设置为off。
30. `jit_above_cost`: 这个参数指定在哪些查询成本高于该阈值时，JIT优化将被启用。默认情况下，它设置为100000。

这些参数是PostgreSQL配置文件中的一部分。在修改配置文件时，需要谨慎处理，避免对数据库的正常运行造成影响。如果不确定如何配置，可以参考PostgreSQL官方文档或寻求专业的帮助。 **下面是关于Postgresql 13.7 postgresql.conf配置文件详解部分：**

\`\`\`

## \----------------------------- ##

## PostgreSQL configuration file ##

## \----------------------------- ##

## This file consists of lines of the form: ##

## 该文件由如下格式的行组成: ##

## name = value ##

## (The "=" is optional.) Whitespace may be used. Comments are introduced with ##

## "\#" anywhere on a line. The complete list of parameter names and allowed ##

## values can be found in the PostgreSQL documentation. ##

## (=是可选的。)可以使用空格。注释在一行中的任意位置用“\#”引入。参数名称和允许的值的完整列表可以在PostgreSQL文档中找到。 ##

## The commented-out settings shown in this file represent the default values. ##

## Re-commenting a setting is NOT sufficient to revert it to the default value; ##

## you need to reload the server. ##

## 该文件中显示的带注释的设置表示默认值。重新注释一个设置不足以将其恢复为默认值;您需要重新加载服务器。 ##

## This file is read on server startup and when the server receives a SIGHUP ##

## signal. If you edit the file on a running system, you have to SIGHUP the ##

## server for the changes to take effect, run "pg\_ctl reload", or execute ##

## "SELECT pg*reload*conf()". Some parameters, which are marked below, ##

## require a server shutdown and restart to take effect. ##

## 该文件在服务器启动和服务器接收到SIGHUP信号时读取。如果你在一个正在运行的系统上编辑这个文件，你必须SIGHUP服务器以便修改生效，运行"pg*ctl reload"，或者执行"SELECT pg*reload\_conf()"。下面标记的一些参数需要服务器关闭和重新启动才能生效。 ##

## Any parameter can also be given as a command-line option to the server, e.g., ##

## "postgres -c log\_connections=on". Some parameters can be changed at run time ##

## with the "SET" SQL command. ##

## 任何参数也可以作为命令行选项提供给服务器，例如，"postgres -c log\_connections=on"。一些参数可以在运行时使用“SET”SQL命令更改。 ##

## Memory units: B = bytes Time units: us = microseconds ##

## kB = kilobytes ms = milliseconds ##

## MB = megabytes s = seconds ##

## GB = gigabytes min = minutes ##

## TB = terabytes h = hours ##

## d = days ##

## \------------------------------------------------------------------------------ ##

## FILE LOCATIONS 文件位置 ##

## \------------------------------------------------------------------------------ ##

## The default values of these variables are driven from the -D command-line ##

## option or PGDATA environment variable, represented here as ConfigDir. ##

## 这些变量的默认值由-D命令行选项或PGDATA环境变量驱动，在这里表示为ConfigDir。 ##

## data\_directory = 'ConfigDir' \# use data in another directory 使用其他目录中的数据 ##

# (change requires restart) 改变需要重新启动数据库

## hba*file = 'ConfigDir/pg*hba.conf' \# host-based authentication file 基于主机的认证文件 ##

# (change requires restart)

## ident*file = 'ConfigDir/pg*ident.conf' \# ident configuration file 识别配置文件 ##

# (change requires restart)

## If external*pid*file is not explicitly set, no extra PID file is written. ##

## 如果没有显式地设置external*pid*file，则不会写入额外的PID文件。 ##

## external*pid*file = '' \# write an extra PID file 写一个额外的PID文件 ##

# (change requires restart)

## \------------------------------------------------------------------------------ ##

## CONNECTIONS AND AUTHENTICATION 连接和身份验证 ##

## \------------------------------------------------------------------------------ ##

## \- Connection Settings - 连接设置 ##

listen*addresses = '\*' \# what IP address(es) to listen on; 侦听的IP地址是什么; \# comma-separated list of addresses; 逗号分隔的地址列表; \# defaults to 'localhost'; use '\*' for all 默认为'localhost';全部使用“\*” \# (change requires restart) port = 5432 \# (change requires restart) \#端口 max*connections = 100 \# (change requires restart) \# 数据库最大连接数

## superuser*reserved*connections = 3 \# (change requires restart) ##

## unix*socket*directories = '/var/run/postgresql, /tmp' \# comma-separated list of directories 以逗号分隔的目录列表 ##

# (change requires restart)

## unix*socket*group = '' \# (change requires restart) ##

## unix*socket*permissions = 0777 \# begin with 0 to use octal notation 以0开始使用八进制表示法 ##

# (change requires restart)

## bonjour = off \# advertise server via Bonjour 通过Bonjour广告服务器 ##

# (change requires restart)

## bonjour\_name = '' \# defaults to the computer name 默认为计算机名 ##

# (change requires restart)

## \- TCP settings - ##

## see "man tcp" for details ##

## tcp*keepalives*idle = 0 \# TCP*KEEPIDLE, in seconds; TCP*KEEPIDLE，单位为秒; ##

# 0 selects the system default 0选择系统默认值

## tcp*keepalives*interval = 0 \# TCP\_KEEPINTVL, in seconds; ##

# 0 selects the system default

## tcp*keepalives*count = 0 \# TCP\_KEEPCNT; ##

# 0 selects the system default

## tcp*user*timeout = 0 \# TCP*USER*TIMEOUT, in milliseconds; TCP*USER*TIMEOUT，以毫秒为单位; ##

# 0 selects the system default

## \- Authentication - 身份验证 ##

## authentication\_timeout = 1min \# 1s-600s ##

password\_encryption = scram-sha-256 \# md5 or scram-sha-256

## db*user*namespace = off ##

## GSSAPI using Kerberos 使用Kerberos的GSSAPI ##

## krb*server*keyfile = 'FILE:$\{sysconfdir\}/krb5.keytab' ##

## krb*caseins*users = off ##

## \- SSL - ##

## ssl = off ##

## ssl*ca*file = '' ##

## ssl*cert*file = 'server.crt' ##

## ssl*crl*file = '' ##

## ssl*key*file = 'server.key' ##

## ssl\_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' \# allowed SSL ciphers ##

## ssl*prefer*server\_ciphers = on ##

## ssl*ecdh*curve = 'prime256v1' ##

## ssl*min*protocol\_version = 'TLSv1.2' ##

## ssl*max*protocol\_version = '' ##

## ssl*dh*params\_file = '' ##

## ssl*passphrase*command = '' ##

## ssl*passphrase*command*supports*reload = off ##

## \------------------------------------------------------------------------------ ##

## RESOURCE USAGE (except WAL) 资源使用情况(WAL除外) ##

## \------------------------------------------------------------------------------ ##

## \- Memory - 内存 ##

shared\_buffers = 128MB \# min 128kB \# (change requires restart)

## huge\_pages = try \# on, off, or try ##

# (change requires restart)

## temp\_buffers = 8MB \# min 800kB ##

## max*prepared*transactions = 0 \# zero disables the feature 0禁用该特性 ##

# (change requires restart)

## Caution: it is not advisable to set max*prepared*transactions nonzero unless ##

## you actively intend to use prepared transactions. ##

## 注意:不建议将max*prepared*transactions设置为非零，除非您打算积极使用准备事务。 ##

## work\_mem = 4MB \# min 64kB ##

## hash*mem*multiplier = 1.0 \# 1-1000.0 multiplier on hash table work*mem 哈希表work*mem上的1-1000.0乘数 ##

## maintenance*work*mem = 64MB \# min 1MB ##

## autovacuum*work*mem = -1 \# min 1MB, or -1 to use maintenance*work*mem min 1MB，或-1使用maintenance*work*mem ##

## logical*decoding*work\_mem = 64MB \# min 64kB ##

## max*stack*depth = 2MB \# min 100kB ##

## shared*memory*type = mmap \# the default is the first option 默认是第一个选项 ##

# supported by the operating system:  默认是第一个选项
          #   mmap
          #   sysv
          #   windows
          # (change requires restart)

dynamic*shared*memory\_type = posix \# the default is the first option 默认是第一个选项 \# supported by the operating system: 默认是第一个选项 \# posix \# sysv \# windows \# mmap \# (change requires restart)

## \- Disk - 磁盘 ##

## temp*file*limit = -1 \# limits per-process temp file space 限制每个进程临时文件空间 ##

# in kilobytes, or -1 for no limit 单位是千字节，-1表示没有限制

## \- Kernel Resources - 内核资源 ##

## max*files*per\_process = 1000 \# min 64 ##

# (change requires restart)

## \- Cost-Based Vacuum Delay - 基于成本的真空延迟 ##

## vacuum*cost*delay = 0 \# 0-100 milliseconds (0 disables) 0-100毫秒(0 禁用) ##

## vacuum*cost*page\_hit = 1 \# 0-10000 credits 0 - 10000学分 ##

## vacuum*cost*page\_miss = 10 \# 0-10000 credits ##

## vacuum*cost*page\_dirty = 20 \# 0-10000 credits ##

## vacuum*cost*limit = 200 \# 1-10000 credits ##

## \- Background Writer - ##

## bgwriter\_delay = 200ms \# 10-10000ms between rounds 回合之间10-10000ms ##

## bgwriter*lru*maxpages = 100 \# max buffers written/round, 0 disables 最大缓冲区写入/循环，0禁用 ##

## bgwriter*lru*multiplier = 2.0 \# 0-10.0 multiplier on buffers scanned/round 0-10.0乘数的缓冲区扫描/轮 ##

## bgwriter*flush*after = 512kB \# measured in pages, 0 disables 以页面为单位，0表示禁用 ##

## \- Asynchronous Behavior - 异步行为 ##

## effective*io*concurrency = 1 \# 1-1000; 0 disables prefetching 1 - 1000;0禁用预取 ##

## maintenance*io*concurrency = 10 \# 1-1000; 0 disables prefetching ##

## max*worker*processes = 8 \# (change requires restart) ##

## max*parallel*maintenance*workers = 2 \# taken from max*parallel*workers 取自max*parallel\_workers ##

## max*parallel*workers*per*gather = 2 \# taken from max*parallel*workers ##

## parallel*leader*participation = on ##

## max*parallel*workers = 8 \# maximum number of max*worker*processes that max*worker*进程的最大数目 ##

# can be used in parallel operations 能否用于并行操作

## old*snapshot*threshold = -1 \# 1min-60d; -1 disables; 0 is immediate 1 min-60d;1禁用;0是立即的 ##

# (change requires restart)

## backend*flush*after = 0 \# measured in pages, 0 disables 以页面为单位，0表示禁用 ##

## \------------------------------------------------------------------------------ ##

## WRITE-AHEAD LOG 写前日志 ##

## \------------------------------------------------------------------------------ ##

## \- Settings - ##

## wal\_level = replica \# minimal, replica, or logical 最小、复制或逻辑 ##

# (change requires restart)

## fsync = on \# flush data to disk for crash safety 为防止崩溃，将数据刷新到磁盘 ##

# (turning this off can cause
          # unrecoverable data corruption) 关闭此功能会导致不可恢复的数据损坏

## synchronous\_commit = on \# synchronization level; 同步水平 ##

# off, local, remote_write, remote_apply, or on

## wal*sync*method = fsync \# the default is the first option 默认是第一个选项 ##

# supported by the operating system:
          #   open_datasync
          #   fdatasync (default on Linux and FreeBSD)
          #   fsync
          #   fsync_writethrough
          #   open_sync

## 操作系统支持: ##

# open_datasync
      # fdatasync (Linux和FreeBSD默认)
      # fsync
      # fsync_writethrough
      # open_sync

## full*page*writes = on \# recover from partial page writes 从部分页写入中恢复 ##

## wal\_compression = off \# enable compression of full-page writes 启用整页写入的压缩 ##

## wal*log*hints = off \# also do full page writes of non-critical updates 也可以对非关键的更新进行整页的写入 ##

# (change requires restart)

## wal*init*zero = on \# zero-fill new WAL files 零填充的新WAL文件 ##

## wal\_recycle = on \# recycle WAL files 回收WAL文件 ##

## wal*buffers = -1 \# min 32kB, -1 sets based on shared*buffers min 32kB， -1集基于shared\_buffers ##

# (change requires restart)

## wal*writer*delay = 200ms \# 1-10000 milliseconds 1 - 10000毫秒 ##

## wal*writer*flush\_after = 1MB \# measured in pages, 0 disables 以页面为单位，0表示禁用 ##

## wal*skip*threshold = 2MB ##

## commit\_delay = 0 \# range 0-100000, in microseconds 范围0-100000，单位为微秒 ##

## commit\_siblings = 5 \# range 1-1000 范围1 - 1000 ##

## \- Checkpoints - ##

## checkpoint\_timeout = 5min \# range 30s-1d 范围30 s-1d ##

max*wal*size = 1GB min*wal*size = 80MB

## checkpoint*completion*target = 0.5 \# checkpoint target duration, 0.0 - 1.0 检查点目标持续时间，0.0 - 1.0 ##

## checkpoint*flush*after = 256kB \# measured in pages, 0 disables 以页面为单位，0表示禁用 ##

## checkpoint\_warning = 30s \# 0 disables 0表示禁用 ##

## \- Archiving - 存档 ##

## archive\_mode = off \# enables archiving; off, on, or always 使归档;关，开，或总是 ##

# (change requires restart)

## archive\_command = '' \# command to use to archive a logfile segment 用于归档日志文件段的命令 ##

# placeholders: %p = path of file to archive
        #               %f = file name only
        # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

## archive\_timeout = 0 \# force a logfile segment switch after this 在此之后强制进行日志文件段切换 ##

# number of seconds; 0 disables  秒数;0禁用

## \- Archive Recovery - 归档文件恢复 ##

## These are only used in recovery mode. ##

## 这些仅用于恢复模式。 ##

## restore\_command = '' \# command to use to restore an archived logfile segment ##

## 命令用于恢复归档的日志文件段 ##

# placeholders: %p = path of file to restore
        #               %f = file name only
        # e.g. 'cp /mnt/server/archivedir/%f %p'
        # (change requires restart)

## archive*cleanup*command = '' \# command to execute at every restartpoint 命令在每个重启点执行 ##

## recovery*end*command = '' \# command to execute at completion of recovery 在恢复完成时执行的命令 ##

## \- Recovery Target - 恢复目标 ##

## Set these only when performing a targeted recovery. 仅在执行目标恢复时设置这些。 ##

## recovery\_target = '' \# 'immediate' to end recovery as soon as a ##

\# consistent state is reached Immediate”，即在达到一致状态后立即结束恢复 \# (change requires restart)

## recovery*target*name = '' \# the named restore point to which recovery will proceed 恢复将继续到的指定还原点 ##

# (change requires restart)

## recovery*target*time = '' \# the time stamp up to which recovery will proceed 将进行恢复的时间戳 ##

# (change requires restart)

## recovery*target*xid = '' \# the transaction ID up to which recovery will proceed 将进行恢复的事务ID ##

# (change requires restart)

## recovery*target*lsn = '' \# the WAL LSN up to which recovery will proceed WAL LSN，恢复将继续进行 ##

# (change requires restart)

## recovery*target*inclusive = on \# Specifies whether to stop: ##

# just after the specified recovery target (on)
        # just before the recovery target (off)

## 是否停止: ##

#在指定的恢复目标之后(on)
        #在恢复目标之前(off)
        # (change requires restart)

## recovery*target*timeline = 'latest' \# 'current', 'latest', or timeline ID current'， 'latest'，或时间轴ID ##

# (change requires restart)

## recovery*target*action = 'pause' \# 'pause', 'promote', 'shutdown' ##

# (change requires restart)

## \------------------------------------------------------------------------------ ##

## REPLICATION 复制 ##

## \------------------------------------------------------------------------------ ##

## \- Sending Servers - ##

## Set these on the master and on any standby that will send replication data. ##

## 在主服务器和将发送复制数据的任何备用服务器上设置这些参数。 ##

## max*wal*senders = 10 \# max number of walsender processes 最大walsender进程数 ##

# (change requires restart)

## wal*keep*size = 0 \# in megabytes; 0 disables 以兆字节为单位;0禁用 ##

## max*slot*wal*keep*size = -1 \# in megabytes; -1 disables 以兆字节为单位;-1禁用 ##

## wal*sender*timeout = 60s \# in milliseconds; 0 disables 以毫秒为单位;0禁用 ##

## max*replication*slots = 10 \# max number of replication slots 最大复制插槽数 ##

# (change requires restart)

## track*commit*timestamp = off \# collect timestamp of transaction commit 收集事务提交的时间戳 ##

# (change requires restart)

## \- Master Server - ##

## These settings are ignored on a standby server. ##

## 在备用服务器上忽略这些设置。 ##

## synchronous*standby*names = '' \# standby servers that provide sync rep 提供同步代表的备用服务器 ##

# method to choose sync standbys, number of sync standbys,
        # 方法选择同步备用，同步备用数量，
        # and comma-separated list of application_name
        # 和以逗号分隔的application_name列表
        # from standby(s); '*' = all
        # 从备用(s);'*' =全部

## vacuum*defer*cleanup\_age = 0 \# number of xacts by which cleanup is delayed 延迟清除的xact数 ##

## \- Standby Servers - ##

## These settings are ignored on a master server. ##

## 这些设置在主服务器上被忽略 ##

## primary\_conninfo = '' \# connection string to sending server 发送服务器的连接字符串 ##

## primary*slot*name = '' \# replication slot on sending server 发送服务器上的复制插槽 ##

## promote*trigger*file = '' \# file name whose presence ends recovery 恢复结束时存在的文件名 ##

## hot\_standby = on \# "off" disallows queries during recovery "off"禁止在恢复期间查询 ##

# (change requires restart)

## max*standby*archive\_delay = 30s \# max delay before canceling queries 取消查询前最大延迟 ##

# when reading WAL from archive;
          # -1 allows indefinite delay
          # 从档案中读取WAL时;-1允许无限期延迟

## max*standby*streaming\_delay = 30s \# max delay before canceling queries 取消查询前最大延迟 ##

# when reading streaming WAL;
          # -1 allows indefinite delay
          # 读取流时WAL;-1允许无限期延迟

## wal*receiver*create*temp*slot = off \# create temp slot if primary*slot*name is not set ##

## 如果未设置primary*slot*name，则创建临时槽位 ##

## wal*receiver*status\_interval = 10s \# send replies at least this often ##

# 0 disables 发送回复至少这个经常0禁用

## hot*standby*feedback = off \# send info from standby to prevent ##

# query conflicts 从待机发送信息以防止查询冲突

## wal*receiver*timeout = 60s \# time that receiver waits for ##

# communication from master
          # in milliseconds; 0 disables 接收端等待主机通信的时间(以毫秒为单位);0禁用

## wal*retrieve*retry\_interval = 5s \# time to wait before retrying to ##

# retrieve WAL after a failed attempt 在尝试失败后重新尝试检索WAL之前等待的时间

## recovery*min*apply\_delay = 0 \# minimum delay for applying changes during recovery ##

## 恢复期间应用更改的最小延迟 ##

## \- Subscribers - 用户 ##

## These settings are ignored on a publisher. 在发布服务器上，这些设置将被忽略。 ##

## max*logical*replication*workers = 4 \# taken from max*worker\_processes ##

## 取自max*worker*processes ##

# (change requires restart)

## max*sync*workers*per*subscription = 2 \# taken from max*logical*replication\_workers ##

## 取自max*logical*replication\_workers ##

## \------------------------------------------------------------------------------ ##

## QUERY TUNING 查询调优 ##

## \------------------------------------------------------------------------------ ##

## \- Planner Method Configuration - Planner方法配置 ##

## enable\_bitmapscan = on ##

## enable\_hashagg = on ##

## enable\_hashjoin = on ##

## enable\_indexscan = on ##

## enable\_indexonlyscan = on ##

## enable\_material = on ##

## enable\_mergejoin = on ##

## enable\_nestloop = on ##

## enable*parallel*append = on ##

## enable\_seqscan = on ##

## enable\_sort = on ##

## enable*incremental*sort = on ##

## enable\_tidscan = on ##

## enable*partitionwise*join = off ##

## enable*partitionwise*aggregate = off ##

## enable*parallel*hash = on ##

## enable*partition*pruning = on ##

## \- Planner Cost Constants - 计划成本常数 ##

## seq*page*cost = 1.0 \# measured on an arbitrary scale 用任意尺度测量的 ##

## random*page*cost = 4.0 \# same scale as above 与以上比例相同 ##

## cpu*tuple*cost = 0.01 \# same scale as above ##

## cpu*index*tuple\_cost = 0.005 \# same scale as above ##

## cpu*operator*cost = 0.0025 \# same scale as above ##

## parallel*tuple*cost = 0.1 \# same scale as above ##

## parallel*setup*cost = 1000.0 \# same scale as above ##

## jit*above*cost = 100000 \# perform JIT compilation if available ##

# and query more expensive than this;
          # -1 disables 如果可用，则执行JIT编译，并且查询的开销大于此值; -1禁用

## jit*inline*above\_cost = 500000 \# inline small functions if query is ##

# more expensive than this; -1 disables 内联小函数如果查询比这更昂贵;-1禁用

## jit*optimize*above\_cost = 500000 \# use expensive JIT optimizations if ##

# query is more expensive than this;
          # -1 disables 如果查询的开销大于此值，则使用昂贵的JIT优化;-1禁用

## min*parallel*table*scan*size = 8MB ##

## min*parallel*index*scan*size = 512kB ##

## effective*cache*size = 4GB ##

## \- Genetic Query Optimizer - 遗传查询优化器 ##

## geqo = on ##

## geqo\_threshold = 12 ##

## geqo\_effort = 5 \# range 1-10 ##

## geqo*pool*size = 0 \# selects default based on effort 根据工作量选择默认值 ##

## geqo\_generations = 0 \# selects default based on effort ##

## geqo*selection*bias = 2.0 \# range 1.5-2.0 ##

## geqo\_seed = 0.0 \# range 0.0-1.0 ##

## \- Other Planner Options - 其他计划选项 ##

## default*statistics*target = 100 \# range 1-10000 ##

## constraint\_exclusion = partition \# on, off, or partition ##

## cursor*tuple*fraction = 0.1 \# range 0.0-1.0 ##

## from*collapse*limit = 8 ##

## join*collapse*limit = 8 \# 1 disables collapsing of explicit JOIN clauses ##

## 1禁用显式JOIN子句的折叠 ##

## force*parallel*mode = off ##

## jit = on \# allow JIT compilation 允许JIT编译 ##

## plan*cache*mode = auto \# auto, force*generic*plan or ##

# force_custom_plan

## Auto、force*generic*plan或force*custom*plan ##

## \------------------------------------------------------------------------------ ##

## REPORTING AND LOGGING 报告和日志记录 ##

## \------------------------------------------------------------------------------ ##

## \- Where to Log - ##

log*destination = 'stderr' \# Valid values are combinations of \# stderr, csvlog, syslog, and eventlog, \# depending on platform. csvlog \# requires logging*collector to be on.

## 根据平台的不同，有效值为stderr、csvlog、syslog和eventlog的组合。Csvlog要求打开logging\_collector。 ##

## This is used when logging to stderr: ##

## 当记录到stderr时使用: ##

logging\_collector = on \# Enable capturing of stderr and csvlog \# into log files. Required to be on for \# csvlogs. \# (change requires restart)

## 启用将stderr和csvlog捕获到日志文件中。要求开启csvlogs。 ##

## These are only used if logging\_collector is on: ##

## 这些只在logging\_collector打开时使用: ##

log\_directory = '/backup/data/postgresql/log' \# directory where log files are written, \# can be absolute or relative to PGDATA \# 日志文件写入的目录，可以是绝对的，也可以是相对于PGDATA的

log\_filename = 'postgresql-%m-%d.log' \# log file name pattern, \# can include strftime() escapes 日志文件名模式，可以包含strftime()转义

## log*file*mode = 0600 \# creation mode for log files, ##

# begin with 0 to use octal notation 
          # 日志文件的创建模式，以0开始使用八进制表示法

log*truncate*on\_rotation = on \# If on, an existing log file with the \# same name as the new log file will be \# truncated rather than appended to. \# But such truncation only occurs on \# time-driven rotation, not on restarts \# or size-driven rotation. Default is \# off, meaning append to existing files \# in all cases. \# 如果打开，与新日志文件同名的现有日志文件将被截断而不是追加。但是这种截断只发生在时间驱动的旋转中，而不会发生在重启或大小驱动的旋转中。默认为关闭，意味着在所有情况下追加到现有文件。

log*rotation*age = 1d \# Automatic rotation of logfiles will \# happen after that time. 0 disables. \# 日志文件将在此之后自动旋转。0禁用。

log*rotation*size = 0 \# Automatic rotation of logfiles will \# happen after that much log output. \# 0 disables. \# 日志文件的自动旋转将发生在大量的日志输出之后。0禁用。

## These are relevant when logging to syslog: ##

## 当日志记录到syslog时，这些是相关的: ##

## syslog\_facility = 'LOCAL0' ##

## syslog\_ident = 'postgres' ##

## syslog*sequence*numbers = on ##

## syslog*split*messages = on ##

## This is only relevant when logging to eventlog (win32): ##

## 这只与记录到eventlog (win32)相关: ##

## (change requires restart) ##

## event\_source = 'PostgreSQL' ##

## \- When to Log - ##

log*min*messages = warning \# values in order of decreasing detail: 按细节递减顺序排列的值: \# debug5 \# debug4 \# debug3 \# debug2 \# debug1 \# info \# notice \# warning \# error \# log \# fatal \# panic log*min*error*statement = error \# values in order of decreasing detail: 按细节递减顺序排列的值: \# debug5 \# debug4 \# debug3 \# debug2 \# debug1 \# info \# notice \# warning \# error \# log \# fatal \# panic (effectively off) log*min*duration*statement = 10000 \# -1 is disabled, 0 logs all statements \# and their durations, > 0 logs only \# statements running at least this number \# of milliseconds \# -1被禁用，0记录所有语句及其持续时间，> 0只记录运行至少这个毫秒数的语句

## log*min*duration\_sample = -1 \# -1 is disabled, 0 logs a sample of statements ##

# and their durations, > 0 logs only a sample of
          # statements running at least this number
          # of milliseconds;
          # sample fraction is determined by log_statement_sample_rate
          # -1被禁用，0记录语句样本及其持续时间，> 0只记录运行至少这个毫秒数的语句样本;样本分数由log_statement_sample_rate决定

## log*statement*sample\_rate = 1.0 \# fraction of logged statements exceeding ##

# log_min_duration_sample to be logged;
          # 1.0 logs all such statements, 0.0 never logs
          # 超过log_min_duration_sample的被记录语句的百分比;1.0记录所有这样的语句，0.0从不记录

## log*transaction*sample\_rate = 0.0 \# fraction of transactions whose statements ##

# are logged regardless of their duration; 1.0 logs all
          # statements from all transactions, 0.0 never logs
          # 无论其持续时间如何，其语句都被记录的事务的百分比;1.0记录所有事务的所有语句，0.0从不记录

## \- What to Log - ##

## debug*print*parse = off ##

## debug*print*rewritten = off ##

## debug*print*plan = off ##

## debug*pretty*print = on ##

log*checkpoints = on log*connections = on log*disconnections = on log*duration = off log*error*verbosity = verbose \# terse, default, or verbose messages 简短的、默认的或冗长的消息

## log\_hostname = off ##

log*line*prefix = '%m %p %u %d %r ' \# special values: \# %a = application name 应用程序名称 \# %u = user name 用户名称 \# %d = database name 数据库名称 \# %r = remote host and port 远程主机和端口 \# %h = remote host 远程主机 \# %b = backend type 后端类型 \# %p = process ID 进程ID \# %t = timestamp without milliseconds 不含毫秒的时间戳 \# %m = timestamp with milliseconds 以毫秒为单位的时间戳 \# %n = timestamp with milliseconds (as a Unix epoch) 以毫秒为单位的时间戳(Unix纪元) \# %i = command tag 命令标签 \# %e = SQL state SQL状态 \# %c = session ID 会话ID \# %l = session line number 会话行号 \# %s = session start timestamp 会话开始时间戳 \# %v = virtual transaction ID 虚拟事务ID \# %x = transaction ID (0 if none) 事务ID(如果没有则为0) \# %q = stop here in non-session \# processes 在非会话进程中此处停止 \# %% = '%' \# e.g. '<%u%%%d> ' log*lock*waits = on \# log lock waits >= deadlock*timeout 日志锁等待>= deadlock*timeout

## log*parameter*max\_length = -1 \# when logging statements, limit logged ##

# bind-parameter values to N bytes;
          # -1 means print in full, 0 disables
          # 当记录语句时，将记录的bind-parameter值限制为N个字节;-1表示完全打印，0禁用

## log*parameter*max*length*on\_error = 0 \# when logging an error, limit logged ##

# bind-parameter values to N bytes;
          # -1 means print in full, 0 disables
          # 当记录错误时，将记录的bind-parameter值限制为N个字节;-1表示完全打印，0禁用

log\_statement = 'mod' \# none, ddl, mod, all

## log*replication*commands = off ##

## log*temp*files = -1 \# log temporary files equal or larger ##

# than the specified size in kilobytes;
          # -1 disables, 0 logs all temp files
          # 记录等于或大于指定大小(单位为千字节)的临时文件;-1禁用，0记录所有临时文件

log\_timezone = 'Asia/Shanghai'

## \------------------------------------------------------------------------------ ##

## PROCESS TITLE 流程标题 ##

## \------------------------------------------------------------------------------ ##

## cluster\_name = '' \# added to process titles if nonempty 如果非空，则添加到进程标题中 ##

# (change requires restart)

## update*process*title = on ##

## \------------------------------------------------------------------------------ ##

## STATISTICS 统计数据 ##

## \------------------------------------------------------------------------------ ##

## \- Query and Index Statistics Collector - 查询和索引统计信息收集器 ##

## track\_activities = on ##

## track\_counts = on ##

## track*io*timing = off ##

## track\_functions = none \# none, pl, all ##

## track*activity*query\_size = 1024 \# (change requires restart) ##

## stats*temp*directory = 'pg*stat*tmp' ##

## \- Monitoring - ##

## log*parser*stats = off ##

## log*planner*stats = off ##

## log*executor*stats = off ##

## log*statement*stats = off ##

## \------------------------------------------------------------------------------ ##

## AUTOVACUUM ##

## \------------------------------------------------------------------------------ ##

## autovacuum = on \# Enable autovacuum subprocess? 'on' ##

# requires track_counts to also be on.
          # 启用autovacuum子进程?'on'要求track_counts也为on。

## log*autovacuum*min\_duration = -1 \# -1 disables, 0 logs all actions and ##

# their durations, > 0 logs only
          # actions running at least this number
          # of milliseconds.
          # -1禁用，0记录所有操作及其持续时间，> 0只记录运行至少这个毫秒数的操作。

## autovacuum*max*workers = 3 \# max number of autovacuum subprocesses autovacuum子进程的最大数量 ##

# (change requires restart)

## autovacuum\_naptime = 1min \# time between autovacuum runs 自动抽真空运行之间的时间 ##

## autovacuum*vacuum*threshold = 50 \# min number of row updates before ##

# vacuum 真空前的最小行更新数

## autovacuum*vacuum*insert\_threshold = 1000 \# min number of row inserts ##

# before vacuum; -1 disables insert
          # vacuums 真空前的最小行插入数;-1禁用插入真空

## autovacuum*analyze*threshold = 50 \# min number of row updates before ##

# analyze 分析前的最小行更新数

## autovacuum*vacuum*scale\_factor = 0.2 \# fraction of table size before vacuum ##

## 真空前工作台尺寸的比例 ##

## autovacuum*vacuum*insert*scale*factor = 0.2 \# fraction of inserts over table ##

# size before insert vacuum 在真空插入前，插入件比表尺寸的比例

## autovacuum*analyze*scale\_factor = 0.1 \# fraction of table size before analyze ##

## 表大小的分数，然后进行分析 ##

## autovacuum*freeze*max\_age = 200000000 \# maximum XID age before forced vacuum ##

## 强制真空前最大XID年龄 ##

# (change requires restart)

## autovacuum*multixact*freeze*max*age = 400000000 \# maximum multixact age ##

# before forced vacuum 强制真空前的最大多效寿命
          # (change requires restart)

## autovacuum*vacuum*cost\_delay = 2ms \# default vacuum cost delay for ##

# autovacuum, in milliseconds;
          # -1 means use vacuum_cost_delay
          # 自动真空的默认真空成本延迟，以毫秒为单位;-1表示使用vacuum_cost_delay

## autovacuum*vacuum*cost\_limit = -1 \# default vacuum cost limit for ##

# autovacuum, -1 means use
          # vacuum_cost_limit
          # autovacuum的默认真空成本限制，-1表示使用vacuum_cost_limit

## \------------------------------------------------------------------------------ ##

## CLIENT CONNECTION DEFAULTS 客户端连接默认值 ##

## \------------------------------------------------------------------------------ ##

## \- Statement Behavior - 声明的行为 ##

## client*min*messages = notice \# values in order of decreasing detail: 按细节递减顺序排列的值: ##

#   debug5
          #   debug4
          #   debug3
          #   debug2
          #   debug1
          #   log
          #   notice
          #   warning
          #   error

## search\_path = '"$user", public' \# schema names 模式名 ##

## row\_security = on ##

## default\_tablespace = '' \# a tablespace name, '' uses the default 表空间名称，”使用默认值 ##

## temp\_tablespaces = '' \# a list of tablespace names, '' uses ##

# only default tablespace 表空间名称列表，仅使用默认表空间

## default*table*access\_method = 'heap' ##

## check*function*bodies = on ##

## default*transaction*isolation = 'read committed' ##

## default*transaction*read\_only = off ##

## default*transaction*deferrable = off ##

## session*replication*role = 'origin' ##

## statement\_timeout = 0 \# in milliseconds, 0 is disabled 以毫秒为单位，0被禁用 ##

## lock\_timeout = 0 \# in milliseconds, 0 is disabled ##

## idle*in*transaction*session*timeout = 0 \# in milliseconds, 0 is disabled ##

## vacuum*freeze*min\_age = 50000000 ##

## vacuum*freeze*table\_age = 150000000 ##

## vacuum*multixact*freeze*min*age = 5000000 ##

## vacuum*multixact*freeze*table*age = 150000000 ##

## vacuum*cleanup*index*scale*factor = 0.1 \# fraction of total number of tuples ##

# before index cleanup, 0 always performs
            # index cleanup 清除索引前元组总数的百分比，0总是执行索引清除

## bytea\_output = 'hex' \# hex, escape ##

## xmlbinary = 'base64' ##

## xmloption = 'content' ##

## gin*fuzzy*search\_limit = 0 ##

## gin*pending*list\_limit = 4MB ##

## \- Locale and Formatting - 区域设置和格式 ##

datestyle = 'iso, mdy'

## intervalstyle = 'postgres' ##

timezone = 'Asia/Shanghai'

## timezone\_abbreviations = 'Default' \# Select the set of available time zone ##

# abbreviations.  Currently, there are
          #   Default
          #   Australia (historical usage)
          #   India
          # You can create your own file in
          # share/timezonesets/.
          # 选择可用的时区缩写集。目前有默认澳大利亚(历史使用)印度您可以创建自己的文件共享/timezonesets/。

## extra*float*digits = 1 \# min -15, max 3; any value >0 actually ##

# selects precise output mode
          # 最小-15，最大3;任何值>0实际上选择了精确的输出模式

## client*encoding = sql*ascii \# actually, defaults to database ##

# encoding
          # 实际上，默认为数据库编码

## These settings are initialized by initdb, but they can be changed. ##

## 这些设置由initdb初始化，但可以更改。 ##

lc*messages = 'en*US.UTF-8' \# locale for system error message strings 系统错误消息字符串的区域设置 lc*monetary = 'en*US.UTF-8' \# locale for monetary formatting 货币格式的区域设置 lc*numeric = 'en*US.UTF-8' \# locale for number formatting 数字格式的区域设置 lc*time = 'en*US.UTF-8' \# locale for time formatting 用于时间格式化的区域设置

## default configuration for text search ##

## 文本搜索的默认配置 ##

default*text*search*config = 'pg*catalog.english'

## \- Shared Library Preloading - 共享库预加载 ##

## shared*preload*libraries = '' \# (change requires restart) ##

## local*preload*libraries = '' ##

## session*preload*libraries = '' ##

## jit\_provider = 'llvmjit' \# JIT library to use 使用的JIT库 ##

## \- Other Defaults - ##

## dynamic*library*path = '$libdir' ##

## \------------------------------------------------------------------------------ ##

## LOCK MANAGEMENT 锁管理 ##

## \------------------------------------------------------------------------------ ##

## deadlock\_timeout = 1s ##

## max*locks*per\_transaction = 64 \# min 10 ##

# (change requires restart)

## max*pred*locks*per*transaction = 64 \# min 10 ##

# (change requires restart)

## max*pred*locks*per*relation = -2 \# negative values mean ##

# (max_pred_locks_per_transaction
          #  / -max_pred_locks_per_relation) - 1
          # 负值意味着(max_pred_locks_per_transaction / -max_pred_locks_per_relation) - 1

## max*pred*locks*per*page = 2 \# min 0 ##

## \------------------------------------------------------------------------------ ##

## VERSION AND PLATFORM COMPATIBILITY 版本和平台兼容性 ##

## \------------------------------------------------------------------------------ ##

## \- Previous PostgreSQL Versions - 以前的PostgreSQL版本 ##

## array\_nulls = on ##

## backslash*quote = safe*encoding \# on, off, or safe\_encoding ##

## escape*string*warning = on ##

## lo*compat*privileges = off ##

## operator*precedence*warning = off ##

## quote*all*identifiers = off ##

## standard*conforming*strings = on ##

## synchronize\_seqscans = on ##

## \- Other Platforms and Clients - 其他平台和客户端 ##

## transform*null*equals = off ##

## \------------------------------------------------------------------------------ ##

## ERROR HANDLING 错误处理 ##

## \------------------------------------------------------------------------------ ##

## exit*on*error = off \# terminate session on any error? 在任何错误时终止会话? ##

## restart*after*crash = on \# reinitialize after backend crash? 后端崩溃后重新初始化? ##

## data*sync*retry = off \# retry or panic on failure to fsync data? ##

## 对fsync数据失败进行重试或恐慌? ##

# (change requires restart)

## \------------------------------------------------------------------------------ ##

## CONFIG FILE INCLUDES 配置文件包括 ##

## \------------------------------------------------------------------------------ ##

## These options allow settings to be loaded from files other than the ##

## default postgresql.conf. Note that these are directives, not variable ##

## assignments, so they can usefully be given more than once. ##

## 这些选项允许从默认的postgresql.conf以外的文件加载设置。请注意，这些是指令，而不是变量赋值，因此它们可以有效地多次给出。 ##

## include\_dir = '...' \# include files ending in '.conf' from 包含以“.conf”结尾的文件 ##

# a directory, e.g., 'conf.d'

## include*if*exists = '...' \# include file only if it exists 仅在文件存在时包含该文件 ##

## include = '...' \# include file 包含文件 ##

## \------------------------------------------------------------------------------ ##

## CUSTOMIZED OPTIONS 自定义选项 ##

## \------------------------------------------------------------------------------ ##

## Add settings for extensions here ##

## 在这里添加扩展设置 ##

\`\`\`

Postgresql篇：Postgresql 13.7 postgresql.conf配置文件详解

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Postgresql篇：Postgresql 13.7 postgresql.conf配置文件详解

相关 postgresql 13 的 postgresql-13.service 文件

相关 PostgreSQL配置优化

相关 PostgreSQL配置优化

相关 PostgreSQL 配置文件详解

相关 PostgreSQL 配置优化

相关 postgresql 日志配置

相关 Postgresql 配置文件详解

相关 postgresql基础配置

相关 PostgreSQL-基础篇