Show Menu

PostgreSQL Configuration Cheat Sheet by

PostgreSQL configuration options and sensible defaults
database     server     configuration     deployment     postgresql

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 share­d_b­uff­ers).

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:
share­d_b­uffers
work_mem
maint­ena­nce­_wo­rk_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?
share­d_b­uffers = 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 check­p­o­in­­t_s­­eg­ments 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.

Download the PostgreSQL Configuration Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/aaronmyatt_postgresql-configuration.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          MySQL Cheat Sheet
          SQL Server Cheat Sheet