max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 6990kB
maintenance_work_mem = 512MB
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 6990kB
maintenance_work_mem = 512MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 3495kB
maintenance_work_mem = 1GB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500

listen_addresses = '*'
port = 5432
max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 6990kB
maintenance_work_mem = 512MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 100000
log_line_prefix='%m %p %x'
wal_level = hot_standby

wal_sync_method = fsync
commit_delay = 0
synchronous_commit = on
full_page_writes = on
fsync = on

max_connections = 300
#根据数据量尽量调大shared_buffer值，把所有数据都放到内存中更好，
#曾经在32G内存的服务器上把shared_buffert调到了26G 
#wal_buffers根据产生的wal日志量也适当设大点
shared_buffers=1200MB 
wal_buffers = 2000kB
#work_mem要适可而止，每个连接都要用这么大的
work_mem = 1024kB
#一般做做检查点的时间长于压力测试的时间，这样性能数据会更好，等压力测试完了再去做检查点吧。
Checkpoint_timeout=120min
 
bgwriter_delay = 10ms  
bgwriter_lru_maxpages = 75
full_page_writes = off
log_min_messages = fatal
#压力测试时由于高并发等锁的时间可以长一些
deadlock_timeout = 3s
 
#平时实践有些应用中把位图扫描和顺序扫描关了性能会更好 
enable_bitmapscan = off
enable_seqscan = off
#如果是只读的压力测试，还可以关掉没事的后台写进程等

shared_buffers = 512MB        # 不需要太大，因为postgresql的不是使用direct io 还依赖系统的page cache来缓存的。
effective_cache_size  = 4GB   # 提示系统可以用来做page cache的内存大小，可以设置为系统内存的75%。只是提示数据库选择合适query plan

checkpoint_segments = 64      # 这两个控制checkpoint的频率和持续的时间。
checkpoint_timeout = 10min    # 跟上面一个类似，不过是时间限制，那个先到达都触发checkpoint操作
checkpoint_completion_target = 0.9   #  checkpoint的持续占总的比例， 可以避免checkpoint的突发io

fsync = on   # 强制等待wal log 写到磁盘才能保证数据的完整性，关闭之后数据完整性就不能保证了，不过性能更好。因为transaction的WAL就不刷数据到磁盘了。
synchronous_commit = on   # 默认强制保证所有的transaction 都等待磁盘操作完才会返回成功。如果关闭了，宕机后可能最最多3个wal_writer_delay 的时间窗口内的transaction的数据就丢失。 如果你能容许少量的事务在crash之后丢失，是可以考虑这个选项试试看。性能会好很多


vacuum_cost_delay = 10ms  #这个值是指vacuum在消耗多少资源后停顿多少时间,以便其他的操作可以使用更多的硬件资源.

log_min_duration_statement = 1000ms # 记录运行超过1秒的SQL到日志中, 一般用于跟踪哪些SQL执行时间长. /var/log/postgres/

log_checkpoints = on # 记录每一次checkpoint到日志中.

# 记录锁等待超过1秒的操作, 一般用于排查业务逻辑上的问题.
log_lock_waits = on
deadlock_timeout = 1s