postgresql.conf 部分参数说明

checkpoint参数描述：

checkpoint的时候会将所有的脏数据都flush到磁盘上

checkpoint_timeout：

两次checkpoint间隔时长

checkpoint_segments:

两次checkpoint间隔最大的xlog日志文件数量(指的是pg_xlog下的文件：000000010000000000000007)以日志文件段（每个段通常 16 兆大）计。

walwriter进程参数描述：

fsync：

该参数直接控制wal日志是否先写入磁盘，然后在更新数据。批量更新/插入时刻off此项，加快执行速度。（off不影响wal writer process 进程运行）

synchronous_commit：

flush wal日志到xlog后才给xact（事务）返回信息。此参数也控制流复制是同步还是异步。

wal_sync_method：

wal日志写到磁盘的控制方式。

full_page_writes：

是否将wal日志一个page整个写入磁盘。好处不会有数据一半写入另一半崩溃时未来得及未写入,不好恢复。

wal_buffers：

wal日志缓冲区大小最小是32K（4个page）。

wal_writer_delay：

两次wal日志缓冲区flush到磁盘间隔时长。

commit_delay：

已经提交的数据在wal缓冲区存放的时长。（为了等够了一定数额一并提交）

commit_siblings：

超过这个数值就将wal缓冲区应该提交的一并flush到磁盘。（参见 commit_delay ）

归档：

archive_mode:

归档命令。

archive_command：

将xlog拷贝到一个地方的命令。

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as 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)
...skipping...
#fsync = on                             # turns forced synchronization on or off
#synchronous_commit = on                # synchronization level;
                                        # off, local, remote_write, or on
#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
#full_page_writes = on                  # recover from partial page writes
#wal_buffers = -1                       # min 32kB, -1 sets based on shared_buffers
                                        # (change requires restart)
#wal_writer_delay = 200ms               # 1-10000 milliseconds

#commit_delay = 0                       # range 0-100000, in microseconds
#commit_siblings = 5                    # range 1-1000

# - Checkpoints -

checkpoint_segments = 16                # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min              # range 30s-1h
#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s               # 0 disables

# - Archiving -

archive_mode = on               # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /home/pg93/test_rep/arch/%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 = 0            # force a logfile segment switch after this
                                # number of seconds; 0 disables

流复制：

max_wal_senders:

允许多少个流复制协议连接过来。一个流复制协议会产生一个walsender进程（一个master可能会有多个standby节点进行流复制）。

wal_keep_segments：

xlog目录中最多容纳多少个wal日志文件，超过了则删掉最初的几个。（一个日志文件16M）

synchronous_standby_names：

同步流复制允许standby节点的名字。（'*' = all）

hot_standby：

standby节点是否允许执行SQL查询。

max_standby_archive_delay&max_standby_streaming_delay：

standby节点查询表A时master节点已经删除了此表，则会发生冲突（因为standby节点必须replies），此参数就是等待冲突解决的时长。

wal_receiver_status_interval：

反馈给主节点自己已经接受（ replies ）到数据信息。

hot_standby_feedback：

如果standby和master发生了冲突是否将此冲突信息反馈给master。

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

# - Sending Server(s) -

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

max_wal_senders = 13            # max number of walsender processes
                                # (change requires restart)
wal_keep_segments = 16          # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s       # in milliseconds; 0 disables

# - Master Server -

# These settings are ignored on a standby server.

#synchronous_standby_names = '' # standby servers that provide sync rep
                                # comma-separated list of application_name
                                # from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is delayed

# - Standby Servers -

# These settings are ignored on a master server.

#hot_standby = off                      # "on" allows queries during recovery
                                        # (change requires restart)
#max_standby_archive_delay = 30s        # max delay before canceling queries
                                        # when reading WAL from archive;
                                        # -1 allows indefinite delay
#max_standby_streaming_delay = 30s      # max delay before canceling queries
                                        # when reading streaming WAL;
                                        # -1 allows indefinite delay
#wal_receiver_status_interval = 10s     # send replies at least this often
                                        # 0 disables
#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

vacuum:

log_autovacuum_min_duration:

在规定时长内未完成的vacuum予以记录日志

autovacuum_max_workers：

允许同事开启的vacuum进程数

autovacuum_naptime：

两次vacuum间隔时间

autovacuum_vacuum_threshold：

达到某表DML操作的最小行数则vacuum

autovacuum_analyze_threshold:

激活自动analyze操作的最小行数，analyze有利于对SQL语句进行更精准的plan(这是由于explain用到的pg_class列reltuples and relpages are not updated on-the-fly)

autovacuum_vacuum_scale_factor：

表示autovacuum的vacuum操作所需的变更量阈值,当这个表的update/delete的tuple总数大于(pg_class.reltuples*autovacuum_vacuum_scale_factor+autovacuum_vacuum_threshold)时, 触发vacuum操作. http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html#AUTOVACUUM

autovacuum_analyze_scale_factor：

表示autovacuum的analyze操作所需的变更量阈值,当这个表的INSERT/update/delete的tuple总数大于(pg_class.reltuples*autovacuum_analyze_scale_factor+autovacuum_analyze_threshold)时, 触发analyze操作

autovacuum_freeze_max_age：

某表的pg_class.relfrozenxid的最大值，如果超出此值则重置xid

autovacuum_vacuum_cost_delay：

运行一次vacuum的时长，如果超过此值则休眠然后起来接着vacuum(vacuum很好I/O)

#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 = 50       # min number of row updates before
                                        # vacuum
#autovacuum_analyze_threshold = 50      # min number of row updates before
                                        # analyze
#autovacuum_vacuum_scale_factor = 0.2   # 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

 

转载于:https://www.cnblogs.com/xxvv/p/3766282.html