# postgresql.conf - PostgreSQL Configuration for Odoo Production
# Version: 2.1 | Last Updated: September 2025
# Created by: Aria Shaw
#
# Instructions:
# 1. Backup original config: sudo cp /etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf.backup
# 2. Update the settings below based on your server specifications
# 3. Apply configuration: sudo systemctl reload postgresql
# 4. Monitor performance and adjust as needed
#
# Server Specifications Template:
# - Small (2-4GB RAM): Use "Small Server" settings
# - Medium (8-16GB RAM): Use "Medium Server" settings
# - Large (32GB+ RAM): Use "Large Server" settings

# ==============================================================================
# CONNECTIONS AND AUTHENTICATION
# ==============================================================================

# Connection Settings
listen_addresses = 'localhost'          # Listen only on localhost for security
                                        # Change to '*' for separated architecture
port = 5432                             # Standard PostgreSQL port

# Connection Limits
max_connections = 200                   # Adjust based on expected Odoo connections
                                       # Small: 100, Medium: 200, Large: 400

# Authentication
password_encryption = scram-sha-256     # Secure password encryption
ssl = on                               # Enable SSL connections

# ==============================================================================
# MEMORY CONFIGURATION
# ==============================================================================

# Shared Memory Settings
# Rule: shared_buffers = 25% of total RAM
shared_buffers = 2GB                   # Small: 1GB, Medium: 2GB, Large: 8GB

# Cache Settings
# Rule: effective_cache_size = 75% of total RAM
effective_cache_size = 6GB             # Small: 3GB, Medium: 6GB, Large: 24GB

# Working Memory
# Rule: work_mem = Total RAM / max_connections / 4
work_mem = 10MB                        # Small: 5MB, Medium: 10MB, Large: 20MB

# Maintenance Memory
maintenance_work_mem = 512MB           # Small: 256MB, Medium: 512MB, Large: 2GB

# ==============================================================================
# WRITE AHEAD LOGGING (WAL)
# ==============================================================================

# WAL Configuration
wal_level = replica                    # Enable replication if needed
wal_buffers = 16MB                     # Small: 8MB, Medium: 16MB, Large: 32MB
max_wal_size = 2GB                     # Small: 1GB, Medium: 2GB, Large: 4GB
min_wal_size = 80MB                    # Keep minimum WAL files

# Checkpoint Configuration
checkpoint_completion_target = 0.9     # Spread checkpoints over 90% of interval
checkpoint_timeout = 15min             # Checkpoint every 15 minutes

# Archive Settings (uncomment for backup/replication)
# archive_mode = on
# archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'

# ==============================================================================
# QUERY PLANNER
# ==============================================================================

# Planner Configuration
default_statistics_target = 100        # Statistics target for query planning
random_page_cost = 1.1                 # Lower for SSD storage (4.0 for HDD)
effective_io_concurrency = 200         # SSD: 200, HDD: 2

# Cost Settings
seq_page_cost = 1.0                    # Sequential scan cost
cpu_tuple_cost = 0.01                  # CPU processing cost per tuple
cpu_index_tuple_cost = 0.005           # CPU cost for index scan
cpu_operator_cost = 0.0025             # CPU cost for operators

# ==============================================================================
# LOGGING AND MONITORING
# ==============================================================================

# Logging Configuration
logging_collector = on                  # Enable log collection
log_directory = '/var/log/postgresql'   # Log directory
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # Log filename pattern

# Log Rotation
log_rotation_age = 1d                  # Rotate logs daily
log_rotation_size = 100MB              # Rotate when file reaches 100MB
log_truncate_on_rotation = on          # Truncate existing log files

# What to Log
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_connections = on                   # Log connection attempts
log_disconnections = on                # Log disconnections
log_lock_waits = on                   # Log lock waits
log_statement = 'none'                # Don't log all statements (performance)

# Performance Logging
log_min_duration_statement = 1000     # Log queries taking longer than 1 second
log_checkpoints = on                  # Log checkpoint activity
log_autovacuum_min_duration = 0       # Log all autovacuum activity

# ==============================================================================
# BACKGROUND PROCESSES
# ==============================================================================

# Background Writer
bgwriter_delay = 200ms                # Background writer sleep time
bgwriter_lru_maxpages = 100          # Pages to write per round
bgwriter_lru_multiplier = 2.0        # Multiple of recent usage

# Autovacuum Configuration
autovacuum = on                       # Enable automatic vacuuming
autovacuum_max_workers = 3            # Number of autovacuum workers
autovacuum_naptime = 1min            # Time between autovacuum runs

# Autovacuum Thresholds
autovacuum_vacuum_threshold = 50      # Minimum number of tuple updates/deletes
autovacuum_analyze_threshold = 50     # Minimum number of tuple changes
autovacuum_vacuum_scale_factor = 0.2  # Fraction of table size
autovacuum_analyze_scale_factor = 0.1 # Fraction of table size for analyze

# Autovacuum Cost Settings
autovacuum_vacuum_cost_delay = 20ms   # Cost delay for autovacuum
autovacuum_vacuum_cost_limit = 200    # Cost limit for autovacuum

# ==============================================================================
# LOCK MANAGEMENT
# ==============================================================================

# Lock Configuration
max_locks_per_transaction = 64        # Locks per transaction
max_pred_locks_per_transaction = 64   # Predicate locks per transaction

# Deadlock Detection
deadlock_timeout = 1s                 # Time before deadlock detection

# ==============================================================================
# PARALLEL PROCESSING
# ==============================================================================

# Parallel Query Settings
max_parallel_workers = 8              # Maximum parallel workers
max_parallel_workers_per_gather = 2   # Workers per Gather node
max_parallel_maintenance_workers = 4  # Workers for maintenance operations

# Parallel Query Thresholds
min_parallel_table_scan_size = 8MB    # Minimum table size for parallel scan
min_parallel_index_scan_size = 512kB  # Minimum index size for parallel scan

# ==============================================================================
# REPLICATION SETTINGS (for separated architecture)
# ==============================================================================

# Uncomment these settings if setting up replication
# max_wal_senders = 3                 # Maximum WAL sender processes
# max_replication_slots = 3           # Maximum replication slots
# hot_standby = on                    # Allow read-only queries on standby
# hot_standby_feedback = on           # Send feedback to primary

# ==============================================================================
# EXTENSIONS AND MODULES
# ==============================================================================

# Shared Libraries
shared_preload_libraries = 'pg_stat_statements'  # Query statistics extension

# Extension Configuration
pg_stat_statements.max = 10000        # Number of statements to track
pg_stat_statements.track = all        # Track all statements

# ==============================================================================
# CLIENT CONNECTION DEFAULTS
# ==============================================================================

# Client Connection Settings
default_text_search_config = 'pg_catalog.english'  # Default text search
timezone = 'UTC'                      # Use UTC for consistency

# Statement Timeout
statement_timeout = 0                 # No timeout (handled by Odoo)
lock_timeout = 0                      # No lock timeout
idle_in_transaction_session_timeout = 0  # No idle timeout

# ==============================================================================
# ERROR HANDLING
# ==============================================================================

# Error Handling
exit_on_error = off                   # Don't exit on errors
restart_after_crash = on              # Restart after crash

# ==============================================================================
# LOCALE AND FORMATTING
# ==============================================================================

# Locale Settings
lc_messages = 'C'                     # Error message language
lc_monetary = 'C'                     # Monetary formatting
lc_numeric = 'C'                      # Numeric formatting
lc_time = 'C'                         # Time formatting

# ==============================================================================
# CONFIGURATION TEMPLATES BY SERVER SIZE
# ==============================================================================

# SMALL SERVER (2-4GB RAM, 2-4 CPU cores)
# shared_buffers = 1GB
# effective_cache_size = 3GB
# work_mem = 5MB
# maintenance_work_mem = 256MB
# max_connections = 100
# wal_buffers = 8MB
# max_wal_size = 1GB

# MEDIUM SERVER (8-16GB RAM, 4-8 CPU cores)
# shared_buffers = 2GB
# effective_cache_size = 6GB
# work_mem = 10MB
# maintenance_work_mem = 512MB
# max_connections = 200
# wal_buffers = 16MB
# max_wal_size = 2GB

# LARGE SERVER (32GB+ RAM, 8+ CPU cores)
# shared_buffers = 8GB
# effective_cache_size = 24GB
# work_mem = 20MB
# maintenance_work_mem = 2GB
# max_connections = 400
# wal_buffers = 32MB
# max_wal_size = 4GB

# ==============================================================================
# MONITORING QUERIES
# ==============================================================================

# Useful monitoring queries (run with psql):

# Check current connections:
# SELECT count(*) as active_connections FROM pg_stat_activity WHERE state = 'active';

# Check cache hit ratio:
# SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio FROM pg_statio_user_tables;

# Check top slow queries:
# SELECT query, mean_time, calls FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

# Check database size:
# SELECT pg_size_pretty(pg_database_size('your_database_name'));

# Check table sizes:
# SELECT schemaname,tablename,pg_size_pretty(size) as size FROM (SELECT schemaname,tablename,pg_relation_size(schemaname||'.'||tablename) as size FROM pg_tables) s ORDER BY size DESC;

# ==============================================================================
# PERFORMANCE TUNING NOTES
# ==============================================================================

# Regular Maintenance Tasks:
# 1. Run VACUUM ANALYZE weekly: sudo -u postgres psql -c "VACUUM ANALYZE;"
# 2. Monitor pg_stat_statements for slow queries
# 3. Check and adjust autovacuum settings based on workload
# 4. Monitor cache hit ratios (should be >99%)
# 5. Review log files for errors and warnings

# Performance Optimization:
# 1. Monitor shared_buffers usage in pg_buffercache
# 2. Adjust work_mem based on query complexity
# 3. Tune checkpoint settings for write-heavy workloads
# 4. Consider partitioning for very large tables
# 5. Regular reindexing for frequently updated tables

# Security Recommendations:
# 1. Use strong passwords for database users
# 2. Configure pg_hba.conf for proper authentication
# 3. Enable SSL for all connections
# 4. Regular security updates for PostgreSQL
# 5. Monitor logs for unauthorized access attempts

# Backup Considerations:
# 1. Configure continuous archiving for point-in-time recovery
# 2. Test backup and restore procedures regularly
# 3. Monitor backup completion and integrity
# 4. Store backups in secure, separate location
# 5. Document recovery procedures

# For support and updates, visit: https://ariashaw.github.io