postgresql配置参数-创新互联

#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 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             # 根据实际频繁变更或删除记录的对象数决定
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当时的执行计划
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'

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。