pg 10 主要参数配置整理_pg 数据库参数

一、 查看参数设置

• show 参数名
• select * from pg_settings where name='参数名';
• 查看 postgresql.conf文件 

 

二、 参数是否可修改

pg中主要可优化参数都位于postgresql.conf文件中，其中参数是否可修改可通过pg_settings的context字段查询。

postgres=# select distinct context from pg_settings;        
      context      
-------------------
 postmaster
 superuser-backend
 user
 internal
 backend
 sighup
 superuser
(7 rows)
 
postgres=# select name,context from pg_settings limit 5;
          name           |  context   
-------------------------+------------
 allow_system_table_mods | postmaster
 application_name        | user
 archive_command         | sighup
 archive_mode            | postmaster
 archive_timeout         | sighup
(5 rows)

 

context含义

• internal：编译期间确定的数据库内部设置，除非重新编译PG否则无法更改
• postmaster：服务器重启后才生效（所有共享内存设置都属此类）
• signup：向服务器发送HUP信号会使服务器重新加载postgresql.conf，加载后对参数的设置会立即生效
• backend：类似signup，但参数的更改只对新连接生效，不影响现有会话
• superuser：修改后即生效，但只能由拥有超级用户权限的用户修改
• user：修改仅影响当前会话，类似alter session
• superuser-backend：可以在postgresql.conf中直接更改，无需重启生效。此值不能在启动会话后进行更改，另外仅超级用户（如postgres用户）可更改这些设置

 

三、 重新加载配置文件

1. pg_reload_conf（超级用户）

select pg_reload_conf();

2. kill命令发送HUP信号

ps -ef|grep "postgres -D"  #记下进程号
 
kill -HUP 进程号

3. pg_ctl命令触发服务器进程SIGHUP信号

pg_ctl reload
#或者
service postgresql reload

4. 查看最后load的时间

select pg_postmaster_start_time();

 

四、 主要参数详解

1. 数据库连接

 

2. 内存参数

 

3. 日志记录

 

4. vacuum

 

5. 检查点

 

6. wal

 

四、 实际参数文件内容

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
 
#------------------------------------------------------------------------------
# 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 = '/data/PRD/postgres/10.7/pg5432/data'          # use data in another directory
                                        # (change requires restart)
hba_file = '/data/PRD/postgres/10.7/pg5432/data/pg_hba.conf'    # host-based authentication file
                                        # (change requires restart)
ident_file = '/data/PRD/postgres/10.7/pg5432/data/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 = ''                 # write an extra PID file
                                        # (change requires restart)
 
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
 
# - Connection Settings -
 
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 1000                  # (change requires restart)
superuser_reserved_connections = 10     # (change requires restart)
unix_socket_directories = '/tmp'        # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
unix_socket_permissions = 0770          # begin with 0 to use octal notation
 
# - Security and Authentication -
 
authentication_timeout = 1min           # 1s-600s
ssl = off
tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;
 
#------------------------------------------------------------------------------
# RESOURCE USAGE (except 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
                                        # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB                         # min 64kB
#maintenance_work_mem = 64MB            # min 1MB
#replacement_sort_tuples = 150000       # limits use of replacement selection sort
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB                  # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
                                        # supported by the operating system:
                                        #   posix
                                        #   sysv
                                        #   windows
                                        #   mmap
                                        # use none to disable dynamic shared memory
                                        # (change requires restart)
 
# - Disk -
 
#temp_file_limit = -1                   # limits per-process temp file space
                                        # in kB, or -1 for no limit
 
# - Kernel Resource Usage -
 
#max_files_per_process = 1000           # min 25
                                        # (change requires restart)
shared_preload_libraries = 'pg_stat_statements,auto_explain'            # (change requires restart)
 
#------------------------------------------------------------------------------
# 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 = local              # 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)
                                        #   fsync
                                        # (change requires restart)
#wal_buffers = -1                       # min 32kB, -1 sets based on shared_buffers
                                        # (change requires restart)
 
# - Checkpoints -
 
#checkpoint_timeout = 5min              # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB         # measured in pages, 0 disables
checkpoint_warning = 10800s             # 0 disables
 
# - Archiving -
 
archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'test ! -f /data/PRD/postgres/10.7/pg5432/pg_archlog/%f && cp %p /data/PRD/postgres/10.7/pg5432/pg_archlog/%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
 
 
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
 
# - Sending Server(s) -
 
# Set these on the master and on any standby that will send replication data.
 
max_wal_senders = 5             # max number of walsender processes
                                # (change requires restart)
wal_keep_segments = 64          # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s       # in milliseconds; 0 disables
 
#max_replication_slots = 10     # max number of replication slots
                                # (change requires restart)
#track_commit_timestamp = off   # collect timestamp of transaction commit
                                # (change requires restart)
 
# - Standby Servers -
 
# These settings are ignored on a master server.
 
hot_standby = on                        # "off" disallows queries during recovery
                                        # (change requires restart)
 
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
 
# - Where to Log -
 
log_destination = 'stderr,csvlog'               # csvlog是pg_rman需要
 
# This is used when logging to stderr:
logging_collector = on          # 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 = '/data/PRD/postgres/10.7/pg5432/data/pg_log'                    # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%d.log'      # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0640                    # creation mode for log files,
                                        # begin with 0 to use octal notation
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.
log_rotation_size = 100MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
 
 
log_min_duration_statement = 60000      # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds
 
 
# - What to Log -
 
log_checkpoints = on
log_connections = on
log_line_prefix = '%t:%r:%u@%d:[%p]: '          
log_lock_waits = on                     # log lock waits >= deadlock_timeout
log_timezone = 'Asia/Shanghai'
 
 
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
 
autovacuum = on
autovacuum_work_mem = 3GB
autovacuum_max_workers = 10
autovacuum_naptime = 1s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.002
autovacuum_analyze_scale_factor = 0.001
autovacuum_vacuum_cost_delay = 0
 
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
 
# - Locale and Formatting -
 
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'PRC'
 
 
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
 
# Add settings for extensions here
 
# - pg_stat_statements auto_explain -
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = true
pg_stat_statements.save = true
auto_explain.log_min_duration = 30s
 
# - AUDIT DDL -
log_statement=ddl

 

参考

https://blog.csdn.net/liyingke112/article/details/78805350