Skip to main content

Configuration

PostgreSQL Configuration Configuration Files Location

  • Main config: /etc/postgresql/17/main/postgresql.conf

  • Host-based auth: /etc/postgresql/17/main/pg_hba.conf

  • Ident mapping: /etc/postgresql/17/main/pg_ident.conf

Basic Configuration

bash
# postgresql.conf - Basic settings
listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200

Authentication Configuration

bash
# pg_hba.conf - Authentication settings
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

Performance Configuration

bash
# postgresql.conf - Performance tuning
shared_buffers = 25% of RAM
work_mem = 4MB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 1GB
effective_cache_size = 75% of RAM
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Security Configuration

bash
# postgresql.conf - Security settings
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'
password_encryption = scram-sha-256
row_security = on
log_connections = on
log_disconnections = on
log_statement = 'all'