Configuring PostgreSQL Server
1. Initializing Database Cluster
initdb -D /var/lib/postgresql/17/main \ -U postgres --pwprompt \ -E UTF8 --locale=en_US.UTF-8 \ --data-checksums
Flag Purpose -D Data directory (PGDATA) -U Superuser name (default postgres) -E Default encoding --locale Default collation/ctype --data-checksums Page-level corruption detection (recommended) --wal-segsize WAL file size (default 16 MB)
2. Starting Server
Method Command pg_ctl pg_ctl -D $PGDATA -l logfile startsystemd sudo systemctl start postgresqlDirect postgres -D $PGDATA
3. Stopping Server
Mode Command Behavior smart pg_ctl stop -m smartWait for clients (default) fast pg_ctl stop -m fastDisconnect clients, clean shutdown immediate pg_ctl stop -m immediateAbort; requires recovery on next start
4. Restarting Server
Use Case Command Apply config requiring restart pg_ctl restart -D $PGDATA -m fastsystemd sudo systemctl restart postgresql
Note: Parameters with context postmaster require restart; check with SELECT name, context FROM pg_settings;
5. Reloading Configuration
pg_ctl reload -D $PGDATA sudo systemctl reload postgresql psql -c "SELECT pg_reload_conf();"
Reload Picks Up Restart Required pg_hba.conf, pg_ident.conf shared_buffers, max_connections log_*, work_mem, autovacuum_* listen_addresses, port, wal_level
6. Setting Environment Variables
Variable Purpose PGDATA Data directory path PGHOST Default server host PGPORT Default port (5432) PGUSER Default username PGPASSWORD Password (insecure; prefer .pgpass) PGDATABASE Default database name PGSSLMODE require, verify-full, etc. PGAPPNAME application_name shown in pg_stat_activity
7. Configuring postgresql.conf
Parameter Recommended Notes listen_addresses '*' or 'localhost'Restart required port 5432Restart required max_connections 100-300Use pooler beyond ~300 shared_preload_libraries 'pg_stat_statements'Restart required timezone 'UTC'Reload
8. Setting Memory Parameters
Parameter Typical (16 GB box) Purpose shared_buffers 4GB (25% RAM) OS-shared page cache effective_cache_size 12GB (75% RAM) Planner hint, not allocated work_mem 16-64MB Per sort/hash op maintenance_work_mem 1GB VACUUM, CREATE INDEX wal_buffers 16MB Auto-tuned by default temp_buffers 8MB Per-session temp tables
9. Setting Connection Limits
Setting Effect max_connections Total backend cap superuser_reserved_connections Slots reserved for superusers CREATE ROLE ... CONNECTION LIMIT n Per-role cap ALTER DATABASE x CONNECTION LIMIT n Per-database cap
10. Configuring Logging
Parameter Recommended logging_collector onlog_directory 'log'log_filename 'postgresql-%Y-%m-%d.log'log_min_duration_statement 1000 (ms)log_line_prefix '%m [%p] %q%u@%d 'log_checkpoints onlog_connections / log_disconnections on (audit)log_lock_waits onlog_temp_files 0 (log all)
11. Setting WAL Parameters
Parameter Typical Purpose wal_level replica or logicalReplication mode max_wal_size 4GB Checkpoint trigger min_wal_size 1GB Recycle floor checkpoint_timeout 15min Max time between checkpoints checkpoint_completion_target 0.9 Spread checkpoint I/O wal_compression on Reduce WAL volume synchronous_commit on / off / remote_apply Durability vs latency
12. Understanding Configuration File Hierarchy
postgresql.conf ← base file
└─ include 'conf.d/*.conf'
postgresql.auto.conf ← written by ALTER SYSTEM (highest precedence)
ALTER DATABASE x SET param = ...
ALTER ROLE x SET param = ...
SET param = ... (session)
SET LOCAL param = ... (transaction)
SHOW work_mem; SELECT name , setting, source, context FROM pg_settings WHERE name = 'work_mem' ; ALTER SYSTEM SET work_mem = '32MB' ; -- writes postgresql.auto.conf SELECT pg_reload_conf();