#
# CCP Postgres Configuration
#
# See http://www.postgresql.org/docs/8.4/interactive/runtime-config.html for more info

listen_addresses = '*'
port = 5432

#
# Connections:
#
# Reserve 2 connections for superusers so that in times of fail we can still log in # to find out what's going on
#
max_connections = 100
superuser_reserved_connections = 2
unix_socket_directory = '/tmp'

#
# Memory:
#
# 25%-ish of system memory is recommended
#
shared_buffers = 1024MB

#
# Amount to use for a single sort/hash operation before switching to temporary files.
# Consider multiple connections and multiple ops per query can make the total work_mem very large.
# default is 1MB
#
work_mem = 1MB

#
# Place to temporary tables, indexes and files. This can be a list.
#
temp_tablespaces = ''

#
# Amount to use for a maintenance operation # e.g. VACUUM, CREATE INDEX, ALTER TABLE ADD FORIEGN KEY.
# Default is 16MB
#
maintenance_work_mem = 16MB

#
# The majority of server processes will touch plpython, so load that automatically
#
shared_preload_libraries = '$libdir/plpython,$libdir/plugins/plugin_debugger.so'

#
# Vacuum process management:
#
# These setting will become more important as we use the database more and more
# over its maintenance periods.
# Setting vacuum_cost_delay to non-zero turns these features on, although this
# should only need to be 10-20ms
#
vacuum_cost_delay = 20ms

#
# Background Writer:
#
# Our usage pattern is quite spiky at the moment, so bgwriter_lru_multiplier is set
# to greater than the default of 2.0. As our usage becomes more uniform this can be reduced.
#
bgwriter_lru_multiplier = 2.0

#
# Asynchronous IO
#
# Number of concurrent disc IO ops. Good starting point is the size of the RAID array (without parity)
#
effective_io_concurrency = 1

#
# Write Ahead Log
#
fsync = True
synchronous_commit = True

#
# Checkpoints:
#
# Increasing these numbers increases the time needed to recover from a crash,
# but since that is assumed to be a fairly infrequent occurrence we can
# squeeze out some more performance
#
checkpoint_segments = 16
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
checkpoint_warning = 30s

#
# Archiving:
#
# The mode only takes effect at server start archive_mode = False
#
# command example: 'cp "%p" /mnt/server/archivedir/"%f"'
# %p is replaced by path name of file to archive, %f is the filename only, %% is a %
# command can be changed at any time. Must return zero exit status on success.
archive_command = ''
#
# max age for WAL segments before archiving is forced. The size of the WAL file does
# not change if a segment is archived early. Zero disables.
archive_timeout = 0

#
# Query Planner Cost Constants:
#
# For DBs that are mostly memory-cached, seq and random page costs should be brought closer
# together than the default 1.0/4.0 and be reduced relative to the cpu_* parameters
#
seq_page_cost = 1.0
random_page_cost = 2.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
effective_cache_size = 6GB
from_collapse_limit = 15
join_collapse_limit = 15
geqo_threshold = 15

#
# Logging:
#
log_destination = 'csvlog,stderr'
logging_collector = on
log_directory = '/home/procuser/log/postgres'
log_filename = 'postgresql-%Y%m%d.log'
log_truncate_on_rotation = True
log_rotation_age = 1440min
log_rotation_size = 0

client_min_messages = 'log'
log_min_messages = 'warning'
log_min_error_statement = 'error'
log_min_duration_statement = 15s

log_checkpoints = True
log_connections = True
log_hostname = True
log_disconnections = True
log_lock_waits = True
log_statement = 'ddl'
log_line_prefix = '%t:%r:%u@%d:[%p]: '
log_temp_files = 10MB

#
# Statistics:
#
track_activities = True
track_counts = True
track_functions = 'all'
default_statistics_target = 1000

#
# Autovacuum
#
# *_scale_factor numbers are quite aggressive because we're more likely to query more recent data
#
autovacuum = True
log_autovacuum_min_duration = 1s
autovacuum_max_workers = 3
autovacuum_naptime = 5min
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_threshold = 0
autovacuum_analyze_scale_factor = 0.01

#
# Client Connections:
#
default_transaction_isolation = 'read committed'

#
# Locale:
#
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

#
# Lock Management:
#
deadlock_timeout = 10s


#
# Streaming Replication setup
#
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32