Postgres的配置文件是用来对数据库设置基础配置的,默认安装在数据目录下,在PG9.4版本以后,系统提供了postgres.conf和postgresql.auto.conf两个配置文件,主要的配置文件为postgres.conf。一般来说,配置文件中的大部分参数都是用的系统默认值,除非有需要,我们才会手动修改。我们可以通过下面的命令来查询配置文件的位置:
postgres=# show config_file;
config_file
--------------------------------------
/var/postgresql/data/postgresql.conf
(1 row)
下面我们在系统中实际查看配置文件的一些重要参数值:
#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)
data_directory为系统的数据目录,hba_file和ident_dile确定了系统另外两个配置文件pg_hba.conf和pg_ident.conf的路径,默认在数据目录下。
port = 5442 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
PG的默认端口号为5432,max_connection规定了数据库的最大连接数,superuser_reserved_connections是系统给超级用户保留的连接数。更改这些参数值后需要重启数据库才能生效。
shared_buffers = 128MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
shared_buffers为共享缓冲区,一般推荐设置参数值为系统内存的25%。
temp_buffers是临时缓冲区,用于数据库会话访问临时表。
work_mem表示工作内存或操作内存,用于复杂的排序操作,一般work_mem值不要设置得太高。
maintenance_work_mem指维护工作内存,用于vacuum,create index,reindex等操作。
#max_worker_processes = 8 # (change requires restart)
#max_parallel_maintenance_workers = 2 # taken from max_parallel_workers
#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
#max_parallel_workers = 8 # maximum number of max_worker_processes that
# can be used in parallel operations
max_worker_processes:数据库允许的最大后台工作进程数。
max_parallel_maintenance_workers:数据库负责维护操作允许的最大后台工作进程数。
max_parallel_workers_per_gather:数据库支持并行操作的并行度。
max_parallel_workers:数据库允许并行的后台工作进程数。
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)
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#checkpoint_timeout = 5min # range 30s-1d
max_wal_size = 1GB
min_wal_size = 80MB
wal_level:日志的归档级别,归档级别由低到高依次为minimal、replica到logical。
fsync控制wal日志日否刷新到磁盘。
wal_sync_method表示wal日志的刷新方式,fsync为off的情况下,该参数值无意义。
wal_buffers设置日志缓冲区的大小,默认为-1,大小为1/32的shared_buffer。
wal_writer_delay表示每隔此参数值的时间,wal日志缓冲区的数据就会被刷新。
checkpoint_timeout代表执行checkout的间隔时间。
max_wal_size和min_wal_size控制wal日志的大小。
# - Archiving -
archive_mode = off # enables archiving; off, on, or always
# (change requires restart)
#archive_command = 'cp $p /var/lib/postgresql/archive/%f' # 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 = 600 # force a logfile segment switch after this
# number of seconds; 0 disables
archive_mode:是否开启归档备份。
archive_command:开启归档备份的命令操作。
archive_timeout:表示归档备份的超时时间。
# REPLICATION
#------------------------------------------------------------------------------
max_wal_senders = 2 # max number of walsender processes
# (change requires restart)
#wal_keep_segments = 0 # in logfile segments; 0 disables
#wal_sender_timeout = 60s # in milliseconds; 0 disables
max_wal_senders:开启流复制后,向备用服务器发送wal日志的最大进程数。
wal_keep_segments:指定pg_wal目录中保存wal日志的最小数量。
wal_sender_timeout:发送wal日志的超时时间。
# - Standby Servers -
# These settings are ignored on a master server.
#hot_standby = on # "off" disallows queries during recovery
# (change requires restart)
hot_standby:是否开启热备份
# - 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.
# This is used when logging to stderr:
#logging_collector = off # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = '/var/postgresql/logs' # directory where log files are written,
# can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_timezone = 'Asia/Shanghai'
log_destination:设置日志输出的方式,默认为stderr。
logging_collector: 是否将日志重定向至文件中,默认为on。
log_directory:设置日志文件的输出路径,默认在PG数据库的数据目录下。
log_filename:设置日志文件的名称。
log_file_mode:设置日志文件的权限。
log_timezone:设置输出日志的时区。
# AUTOVACUUM
#------------------------------------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be 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.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 1000000 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
autovacuum_vacuum_scale_factor = 0 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
# before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
上面是有关autovacuum的一些参数,autovacuum可以设置为on和off,重点介绍一下两个参数:
autovacuum_vacuum_scale_factor:表示触发vacuum 自动清理操作的dml 比例。当dml操作的数据量达到该表的比例值时,会触发 vacuum 自动清理操作。
autovacuum_analyze_scale_factor:表示触发vacuum自动analyze的dml比例。当dml操作的数据量达到该表的比例值时,会触发vacuum自动analyze操作。
还有很多其他的参数,在这篇文章中就不做过多介绍。