PostgreSQL Configuration Cheat Sheet
by aaronmyatt via cheatography.com/30256/cs/9021/
PostgreSQL Server Config pg_hba.conf WORK MEM (postgresql.conf)
Merely a convenient reproduction of the advise local all postgres peer Start low: 32-64MB.
given by Christophe Pettus during his excellent local replication repl trust Look for ‘temporary file’ lines in logs.
PyCon PostgreSQL workshop: Check out the
local fugu fugu trust
THEN - Set to 2-3x the largest temp file you
video! .
host all all 127.0.0.1/32 md5 see.
host all all ::1/128 md5
Config Files Can cause a huge speed-up if set properly. But
hostssl fugu fugu 178.79.191.8/32
be careful: It can use that amount of memory
postgresql.conf pg_hba.conf md5 per planner node.
Aside from log files, these are the ONLY files hostssl replication repl
you should edit in the main postgreSQL 178.79.191.8/32 md5 MAINTENANCE WORK MEM
directory.
Example file 10% of system memory, up to 1GB.
LOGGING (postgresql.conf)
User Settings DO NOT TOUCH
log_destination = 'csvlog'
By default, database traffic is not encrypted. The contents and special files in the main
log_directory = 'pg_log'
PostgreSQL directories should never, ever be
logging_collector = on Turn on ssl if you are running in a cloud
modified directly. Ever.
provider.
log_filename = 'postgres-%Y-%m-
Exceptions: pg_log (if you put the log files
%d_%H%M%S' For pre-9.4, set ssl_renegotiation_limit = 0.
there), and the configuration files.
log_rotation_age = 1d
Memory Config pg_xlog and pg_clog are off-limits!
log_rotation_size = 1GB
log_min_duration_statement = 250ms Consists of:
Checkpoint Config
log_checkpoints = on shared_buffers
Essentially, don't let
log_connections = on
work_mem checkpoint_segments get out of hand.
log_disconnections = on
maintenance_work_mem
log_lock_waits = on
9.4 and earlier (postgresql.conf)
log_temp_files = 0
SHARED BUFFERS (postgresql.conf) wal_buffers = 16MB
Be generous with logging; it has very low impact
Below 2gb RAM? checkpoint_completion_target = 0.9
on the system.
checkpoint_timeout = 10m-30m #
NOTE: “Standard format” or “stderr” is obsolete. 20% Total System Memory
There is no good reason to use it anymore. Depends on restart time
Below 64gm RAM?
checkpoint_segments = 32 # To
25% Total System Memory
Changing Settings start.
> 64gm RAM?
Most settings just require a server reload to checkpoint_segments happening more often
take effect. shared_buffers = 16gb than checkpoint_timeout?
Some require a full server restart (such as
shared_buffers). Adjust checkpoint_segments so that
checkpoints happen due to timeouts rather
filling segments.
By aaronmyatt Published 6th September, 2016. Sponsored by CrosswordCheats.com
cheatography.com/aaronmyatt/ Last updated 6th September, 2016. Learn to solve cryptic crosswords!
Page 1 of 2. http://crosswordcheats.com
PostgreSQL Configuration Cheat Sheet
by aaronmyatt via cheatography.com/30256/cs/9021/
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.
REFERENCE
http://thebuild.com/presentations/pycon-2016-pppp.pdf
By aaronmyatt Published 6th September, 2016. Sponsored by CrosswordCheats.com
cheatography.com/aaronmyatt/ Last updated 6th September, 2016. Learn to solve cryptic crosswords!
Page 2 of 2. http://crosswordcheats.com
