postgresql配置參數(shù)

#base
data_directory = '/dbdata/data'        
hba_file = '/dbdata/data/pg_hba.conf'    
ident_file = '/dbdata/data/pg_ident.conf'    
external_pid_file = '/var/run/postgres/9.5-main.pid'            
listen_addresses = '*'        
port = 5432                
max_connections = 1000            
max_prepared_transactions = 1000
superuser_reserved_connections = 20
unix_socket_directories = '/var/run/postgres'
unix_socket_permissions = 0700


#ssl
ssl = false
ssl_cert_file = '/service/ssl/pgsql/server.crt'     
ssl_key_file = '/service/ssl/pgsql/server.key'         


tcp_keepalives_idle = 30               
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 6GB
huge_pages = try           
maintenance_work_mem = 16MB
autovacuum_work_mem = 16MB
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
max_worker_processes = 20
wal_level = hot_standby                    
synchronous_commit = local
archive_mode = on
archive_command = 'cp -i %p /dbdata/pgsql/archive/%f </dev/null'
archive_timeout = 86400
max_wal_senders = 15
max_wal_size = 3GB
min_wal_size = 1GB
checkpoint_timeout = 20min
checkpoint_completion_target = 0.8
wal_keep_segments = 3000
wal_sender_timeout = 60s
hot_standby = on
max_replication_slots = 32
hot_standby_feedback = off
vacuum_defer_cleanup_age = 0
random_page_cost = 1.3
effective_cache_size = 40GB
wal_sync_method = open_datasync
wal_buffers = 16MB
wal_writer_delay = 10ms
work_mem = 8MB
maintenance_work_mem = 8MB
wal_log_hints = on
full_page_writes = on
dynamic_shared_memory_type = posix

#pg_stat_statements
pg_stat_statements.max = 10000   
pg_stat_statements.track = all
pg_stat_statements.track_utility=off


#log set
log_destination = 'stderr'
logging_collector = on
log_truncate_on_rotation = on
log_directory = 'pg_log'        
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    
log_file_mode = 0600        
log_rotation_age = 1d            
log_rotation_size = 100MB    
log_min_duration_statement = 2s
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_timezone = 'PRC'
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_lock_waits = on
log_statement = 'ddl'
log_line_prefix = '%m %p %u %d %r'

#autovacuum set
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 10              # 根據(jù)實際頻繁變更或刪除記錄的對象數(shù)決定
autovacuum_naptime = 30s                  # 快速喚醒, 防止膨脹
autovacuum_vacuum_scale_factor = 0.1    # 當垃圾超過比例時, 啟動垃圾回收工作進程
autovacuum_analyze_scale_factor = 0.2  
autovacuum_freeze_max_age = 1600000000
autovacuum_multixact_freeze_max_age = 1600000000
vacuum_freeze_table_age = 1500000000
vacuum_multixact_freeze_table_age = 1500000000
auth_delay.milliseconds = 5000          # 認證失敗, 延遲多少毫秒反饋
auto_explain.log_min_duration = 5000    # 記錄超過多少毫秒的SQL當時的執(zhí)行計劃
auto_explain.log_analyze = true
auto_explain.log_verbose = true
auto_explain.log_buffers = true
auto_explain.log_nested_statements = true


#other set
stats_temp_directory = '/service/databases/postgresql/run/9.5-main.pg_stat_tmp'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.UTF-8'        
lc_monetary = 'en_US.UTF-8'            
lc_numeric = 'en_US.UTF-8'        
lc_time = 'en_US.UTF-8'        
default_text_search_config = 'pg_catalog.english'
deadlock_timeout = 1s
max_locks_per_transaction = 100
shared_preload_libraries = 'pg_pathman,pg_stat_statements'