zabbix监控数据存放pg的数据库配置

系统运维

一、操作系统
/etc/sysctl.conf 文件追加如下内容：

(sysctl -p 使配置生效)

kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.core.netdev_max_backlog = 10000
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65000
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_keepalive_time=72
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_intvl = 7
vm.overcommit_memory = 0
vm.swappiness=0
vm.dirty_background_bytes=102400000
vm.dirty_bytes=102400000

/etc/security/limits.conf 文件追加如下内容：

soft nofile 131072
hard nofile 131072
soft nproc 131072
hard nproc 131072
soft core unlimited
hard core unlimited
soft memlock 50000000
hard memlock 50000000
二、数据库配置
listen_addresses = \'*\' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 600 # (change requires restart)
unix_socket_directories = \'.\' # comma-separated list of directories
shared_buffers = 11581MB # min 128kB
maintenance_work_mem = 2048MB # min 1MB
dynamic_shared_memory_type = posix # the default is the first option
shared_preload_libraries = \'pg_stat_statements,pg_pathman\' # (change requires restart)
vacuum_cost_limit = 10000 # 1-10000 credits
max_worker_processes = 8 # (change requires restart)
max_parallel_workers_per_gather = 5 # taken from max_worker_processes
wal_level = logical # minimal, replica, or logical
synchronous_commit = off # synchronization level;
wal_writer_delay = 500ms # 1-10000 milliseconds
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
checkpoint_flush_after = 256kB # 0 disables,
archive_mode = on # enables archiving; off, on, or always
archive_command = \'/bin/date\' # command to use to archive a logfile segment
max_wal_senders = 13 # max number of walsender processes
wal_keep_segments = 256 # in logfile segments, 16MB each; 0 disables
hot_standby = on # "on" allows queries during recovery
random_page_cost = 4 # same scale as above
log_destination = \'csvlog\' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = \'pg_log\' # directory where log files are written,
log_filename = \'postgresql-%a.log\' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 10MB # Automatic rotation of logfiles will
log_min_duration_statement = 100ms # -1 is disabled, 0 logs all statements
log_timezone = \'PRC\'
autovacuum_naptime = 30s # time between autovacuum runs
autovacuum_vacuum_scale_factor = 0.02 # fraction of table size before vacuum
temp_tablespaces = \'tbs_zabbix\' # a list of tablespace names, \'\' uses
idle_in_transaction_session_timeout = 7200000 # in milliseconds, 0 is disabled
datestyle = \'iso, mdy\'
timezone = \'PRC\'
lc_messages = \'en_US.utf8\' # locale for system error message
lc_monetary = \'en_US.utf8\' # locale for monetary formatting
lc_numeric = \'en_US.utf8\' # locale for number formatting
lc_time = \'en_US.utf8\' # locale for time formatting
default_text_search_config = \'pg_catalog.english\'
pg_stat_statements.max = 1000
pg_stat_statements.track = all
auth_delay.milliseconds = 5000