Cheatography
https://cheatography.com
PostgreSQL configuration options and sensible defaults
Config Files
postgresql.conf |
pg_hba.conf |
Aside from log files, these are the ONLY files you should edit in the main postgreSQL directory.
LOGGING (postgresql.conf)
log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = on
log_filename = 'postgres-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 250ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
|
Be generous with logging; it has very low impact on the system.
NOTE: “Standard format” or “stderr” is obsolete. There is no good reason to use it anymore.
Changing Settings
Most settings just require a server reload to take effect. |
Some require a full server restart (such as shared_buffers
). |
pg_hba.conf
local all postgres peer
local replication repl trust
local fugu fugu trust
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
hostssl fugu fugu 178.79.191.8/32 md5
hostssl replication repl 178.79.191.8/32 md5
|
User Settings
By default, database traffic is not encrypted. |
Turn on ssl if you are running in a cloud provider. |
For pre-9.4, set ssl_renegotiation_limit = 0. |
|
|
SHARED BUFFERS (postgresql.conf)
|
Below 2gb RAM? 20% Total System Memory
|
|
Below 64gm RAM? 25% Total System Memory
|
|
> 64gm RAM?
|
WORK MEM (postgresql.conf)
Start low: 32-64MB. |
Look for ‘temporary file’ lines in logs. |
THEN - Set to 2-3x the largest temp file you see. |
Can cause a huge speed-up if set properly. But be careful: It can use that amount of memory per planner node.
MAINTENANCE WORK MEM
10% of system memory, up to 1GB. |
DO NOT TOUCH
The contents and special files in the main PostgreSQL directories should never, ever be modified directly. Ever. |
Exceptions: pg_log (if you put the log files there), and the configuration files. |
pg_xlog and pg_clog are off-limits! |
|
|
Checkpoint Config
Essentially, don't let checkpoint_segments
get out of hand. |
9.4 and earlier (postgresql.conf)
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10m-30m # Depends on restart time
checkpoint_segments = 32 # To start.
|
checkpoint_segments happening more often than checkpoint_timeout?
Adjust checkpoint_segments so that checkpoints happen due to timeouts rather filling segments.
9.5 and later (postgresql.conf)
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10m-30m # Depends on restart time
min_wal_size = 512MB
max_wal_size = 2GB
|
checkpoint_segments happening more often than checkpoint_timeout?
Step 1: Adjust min_wal_size so that checkpoints happen due to timeouts rather filling segments.
Step 2: Adjust max_wal_size to be about three times min_wal_size.
effective_cache_size
Set to the amount of file system cache available.
Otherwise:
If you don’t know, set it to 75% of total system memory. |
|
Created By
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets