PostgreSQL Config Templates

postgresql_config_templates.md

A Couple of PostgreSQL Config Templates for Dev and Production Settings

Use PgTune y PgConfig to configure a PostgreSQL instance.

A Note About Logging SQL

For heavy SQL debugging, set logging log_statement to all and message levels as shown at the dev config. However, logging all SQL queries will result in extremely cumbersome logs in some scenarios. Use this sparingly, set it to none under normal operation.

Dev

max_connections=4000
shared_buffers=14GB
effective_cache_size=40GB
maintenance_work_mem=2GB
checkpoint_completion_target=0.9
wal_buffers=16MB
default_statistics_target=500
random_page_cost=4.0
effective_io_concurrency=2
work_mem=14MB
min_wal_size=2GB
max_wal_size=6GB
max_worker_processes=5
max_parallel_workers_per_gather=2
max_parallel_workers=5
max_parallel_maintenance_workers=3
idle_in_transaction_session_timeout=300000
max_wal_senders=5
max_locks_per_transaction=1024
listen_addresses='*'
dynamic_shared_memory_type=posix
log_timezone='UTC'
datestyle='iso, mdy'
timezone='UTC'
log_statement='all'
log_directory='pg_log'
log_filename='postgresql-%Y-%m-%d_%H%M%S.log'
client_min_messages=NOTICE
log_line_prefix='%a %u %d %r %h %m %i %e'
log_destination='stderr,csvlog'
log_rotation_size=500MB
log_min_messages=NOTICE
log_min_error_statement=NOTICE
log_error_verbosity=DEFAULT
log_connections=ON
log_checkpoints=ON
log_disconnections=ON
log_lock_waits=ON
logging_collector=ON

Production

max_connections=4000
shared_buffers=14GB
effective_cache_size=40GB
maintenance_work_mem=2GB
checkpoint_completion_target=0.9
wal_buffers=16MB
default_statistics_target=500
random_page_cost=4.0
effective_io_concurrency=2
work_mem=14MB
min_wal_size=2GB
max_wal_size=6GB
max_worker_processes=5
max_parallel_workers_per_gather=2
max_parallel_workers=5
max_parallel_maintenance_workers=3
idle_in_transaction_session_timeout=300000
max_wal_senders=5
max_locks_per_transaction=1024
listen_addresses='*'
dynamic_shared_memory_type=posix
log_timezone='UTC'
datestyle='iso, mdy'
timezone='UTC'
log_statement='none'
log_directory='pg_log'
log_filename='postgresql-%Y-%m-%d_%H%M%S.log'
client_min_messages=WARNING
log_line_prefix='%a %u %d %r %h %m %i %e'
log_destination='stderr,csvlog'
log_rotation_size=500MB
log_min_messages=WARNING
log_min_error_statement=ERROR
log_error_verbosity=DEFAULT
log_connections=ON
log_checkpoints=ON
log_disconnections=ON
log_lock_waits=ON
logging_collector=ON