Show Menu
Cheatography

PostgreSQL Configuration Cheat Sheet by

PostgreSQL configuration options and sensible defaults

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.

REFERENCE

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

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