97 lines
3.7 KiB
Plaintext
97 lines
3.7 KiB
Plaintext
# PostgreSQL Production Configuration File for Proxmox LXC Container
|
||
# Optimized for production workloads
|
||
|
||
# CONNECTIONS AND AUTHENTICATION
|
||
listen_addresses = '*'
|
||
max_connections = 200 # Increased for production workloads
|
||
password_encryption = scram-sha-256
|
||
ssl = on
|
||
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
|
||
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
|
||
ssl_prefer_server_ciphers = on # Prefer server's cipher order
|
||
ssl_min_protocol_version = 'TLSv1.2' # Minimum TLS version
|
||
|
||
# RESOURCE USAGE (Optimized for 16GB RAM, 4 CPUs)
|
||
shared_buffers = 4GB # 25% of system memory for 16GB RAM
|
||
work_mem = 64MB # Increased for better query performance
|
||
maintenance_work_mem = 1GB # Increased for faster maintenance operations
|
||
effective_cache_size = 12GB # 75% of available memory
|
||
max_worker_processes = 8 # 2 × CPU cores
|
||
max_parallel_workers_per_gather = 4 # 1 per CPU core
|
||
max_parallel_workers = 8 # Equal to max_worker_processes
|
||
huge_pages = try # Try to use huge pages if available
|
||
|
||
# WRITE-AHEAD LOG
|
||
wal_level = replica # Enables WAL archiving and replication
|
||
max_wal_size = 2GB # Increased for production
|
||
min_wal_size = 1GB # Increased for production
|
||
checkpoint_timeout = 15min # Increased to reduce checkpoint frequency
|
||
checkpoint_completion_target = 0.9 # Spread checkpoint I/O over more time
|
||
wal_buffers = 16MB # Increased for better performance
|
||
synchronous_commit = on # Ensures data durability
|
||
|
||
# QUERY TUNING
|
||
random_page_cost = 1.1 # Optimized for SSD storage
|
||
effective_io_concurrency = 200 # Increased for SSD storage
|
||
default_statistics_target = 500 # Increased for better query plans
|
||
jit = on # Enable JIT compilation for better performance
|
||
track_io_timing = on # Track I/O timing for better insights
|
||
|
||
# REPLICATION
|
||
max_wal_senders = 10 # Allow up to 10 WAL sender processes
|
||
wal_keep_size = 1GB # Keep at least 1GB of WAL segments
|
||
hot_standby = on # Allow queries during recovery
|
||
hot_standby_feedback = on # Prevent query conflicts with standby servers
|
||
|
||
# LOGGING
|
||
log_destination = 'stderr'
|
||
logging_collector = on
|
||
log_directory = 'pg_log'
|
||
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
|
||
log_truncate_on_rotation = off
|
||
log_rotation_age = 1d
|
||
log_rotation_size = 100MB # Increased for production
|
||
log_min_duration_statement = 1000 # Log slow queries (1 second)
|
||
log_checkpoints = on
|
||
log_connections = on
|
||
log_disconnections = on
|
||
log_duration = off
|
||
log_error_verbosity = default
|
||
log_line_prefix = '%m [%p] %q%u@%d '
|
||
log_statement = 'none' # Don't log statements in production
|
||
log_temp_files = 0 # Log all temp file usage
|
||
|
||
# AUTOVACUUM
|
||
autovacuum = on
|
||
log_autovacuum_min_duration = 1000
|
||
autovacuum_max_workers = 6 # Increased for production
|
||
autovacuum_naptime = 1min
|
||
autovacuum_vacuum_threshold = 50
|
||
autovacuum_analyze_threshold = 50
|
||
autovacuum_vacuum_scale_factor = 0.05 # More aggressive vacuuming
|
||
autovacuum_analyze_scale_factor = 0.025 # More aggressive analyzing
|
||
|
||
# STATISTICS
|
||
track_activities = on
|
||
track_counts = on
|
||
track_functions = all # Track function statistics
|
||
track_activity_query_size = 4096 # Increased for better monitoring
|
||
|
||
# CLIENT CONNECTION DEFAULTS
|
||
datestyle = 'iso, mdy'
|
||
timezone = 'UTC'
|
||
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'
|
||
|
||
# LOCKS AND DEADLOCKS
|
||
deadlock_timeout = 1s # Check for deadlocks after 1s
|
||
|
||
# DEVELOPER OPTIONS
|
||
debug_print_parse = off
|
||
debug_print_rewritten = off
|
||
debug_print_plan = off
|
||
debug_pretty_print = on
|