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 start |
| systemd | sudo systemctl start postgresql |
| Direct | postgres -D $PGDATA |
3. Stopping Server
| Mode | Command | Behavior |
|---|---|---|
| smart | pg_ctl stop -m smart | Wait for clients (default) |
| fast | pg_ctl stop -m fast | Disconnect clients, clean shutdown |
| immediate | pg_ctl stop -m immediate | Abort; requires recovery on next start |
4. Restarting Server
| Use Case | Command |
|---|---|
| Apply config requiring restart | pg_ctl restart -D $PGDATA -m fast |
| systemd | 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 | 5432 | Restart required |
| max_connections | 100-300 | Use 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 | on |
| log_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 | on |
| log_connections / log_disconnections | on (audit) |
| log_lock_waits | on |
| log_temp_files | 0 (log all) |
11. Setting WAL Parameters
| Parameter | Typical | Purpose |
|---|---|---|
| wal_level | replica or logical | Replication 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();