Cheatography
Login Register

PostgreSQL Configuration Cheat Sheet

A Programming › PostgreSQL cheat sheet in English by aaronmyatt

PostgreSQL configuration options and sensible defaults

Database Server Configuration Deployment Postgresql
15,867 views 3,330 downloads 6 Sep 2016 History
PRs
Download
3.7 avg · 1 rating

PostgreSQL Server Config

Merely a convenient reprod­uction of the advise given by Christophe Pettus during his excellent PyCon PostgreSQL workshop: Check out the video! .

Config Files

postgr­esq­l.conf
pg_hba.conf
Aside from log files, these are the ONLY files you should edit in the main postgreSQL directory.

LOGGING (postg­r­e­sq­­l.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­_bu­ffers).

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
Example file

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_re­neg­oti­ati­on_­limit = 0.

Memory Config

Consists of:
shared­_bu­ffers
work_mem
mainte­nan­ce_­wor­k_mem

SHARED BUFFERS (postg­r­e­sq­­l.conf)

Below 2gb RAM?
20% Total System Memory
Below 64gm RAM?
25% Total System Memory
> 64gm RAM?
shared­_bu­ffers = 16gb

WORK MEM (postg­r­e­sq­­l.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.

MAINTE­NANCE WORK MEM

10% of system memory, up to 1GB.

DO NOT TOUCH

The contents and special files in the main PostgreSQL direct­ories should never, ever be modified directly. Ever.
Except­ions: pg_log (if you put the log files there), and the config­uration files.
pg_xlog and pg_clog are off-li­mits!

Checkpoint Config

Essent­ially, don't let checkp­­oi­n­t­_s­­egments get out of hand.

9.4 and earlier (postg­r­e­sq­­l.conf) 

wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10m-30m # Depends on restart time
checkpoint_segments = 32 # To start.
checkp­oin­t_s­egments happening more often than checkp­oin­t_t­imeout?

Adjust checkp­oin­t_s­egments so that checkp­oints happen due to timeouts rather filling segments.

9.5 and later (postg­r­e­sq­­l.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
checkp­oin­t_s­egments happening more often than checkp­oin­t_t­imeout?

Step 1: Adjust min_wa­l_size so that checkp­oints happen due to timeouts rather filling segments.
Step 2: Adjust max_wa­l_size to be about three times min_wa­l_size.

effect­ive­_ca­che­_size

Set to the amount of file system cache available.
Otherwise:
If you don’t know, set it to 75% of total system memory.

Badges

Nice Cheat Sheet Good Cheat Sheet Downloaded from Unusual Locations × 2

Comments

No comments yet.

Cheatography n. The art of mapping complex subjects into clear, instantly navigable reference guides.