Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

PostgreSQL 101 for DBAs/sysadmins Cheat Sheet (DRAFT) by

Notes from a postgres admin 101 course.

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Grab info from existing cluster

netstat -nap | grep postgres  # To grab listening port if not default
psql -c SHOW data_directory;
psql -c SHOW config_file;
ps f -U postgres  # Or other DB user, to see startup args
su - db_owner pg_controldata data_directory  # To get cluster details
On redhat: all config files in 
/var/l­ib/­pgsql/<ve­rsi­on>/data
by default
On debian: 
postgr­es.conf
in 
/etc
by default

pg_set­tings view structure

context
Context of the setting (what to restart to update the value)
source­/so­urc­efi­le/­sou­rceline
Where the setting is defined
settin­g/unit
Value of the setting
boot_val
Value at instance startup
reset_val
Default value
Contains a detailed view current running config, refers to the same settings as SHOW/SET.

Genera­lities

One "­roo­t" directory per DB.
Find physical file names from the pg_class table.
Initialize files with the initdb script.

Config files

pg_hba.conf
Access control
pg_ide­nt.conf
Identity manager config­uration
postgr­esq­l.conf
Engine config­uration
postgr­esq­l.a­uto.conf
Online engine config­uration
postma­ste­r.pid
Instance lock file
postma­ste­r.opts
Last startup options
By default in /var/l­ib/­pgsql/<ve­rsi­on>/data

Tools

initdb
Create file/dir structure (aka cluster) for instance
pg_ctl
Control instance state
pg_con­tro­ldata
View config
pg_isready
Check if instance is up w/o opening a full connection
pg_res­etwal
DANGEROUS Wipe transa­ction logs
createdb, dropdb
Create­/delete a DB
By default in /usr/p­gsql-<ve­rsi­on>/bin (on redhat)

Create new instance

initdb \
 -A auth_method \
 -D data_directory \
 -E encoding \
 -X tlog_directory \ 
 --locale=locale \
 [-k]
# Start from service
systemctl enable postgres
systemctl start postgres
# Start standalone
/usr/pgsql-16/bin/pg_ctl -D data_directory start
# Connect as postgres w/o passwd 
sudo -u postgres psql -w
Check instance state with "­sys­temctl postgr­es-16 status­" or pg_isready

Contribs

pg_fre­esp­acemap
View free space maps
pg_prewarm
Preload caches from last run at boot
pg_sta­t_s­tat­ements
Track SQL execution statistics
auto_e­xplain
Trace costly statements automa­tically
pgstat­tuple
Get table stats (live/dead rows, volume of data)
View available extensions with pg_ava­ila­ble­_ex­ten­sions view or 
\dx[+ extension]

Install modules via postgr­es.c­onf: shared­_pr­elo­ad_­lib­raries (perma­nent) or LOAD statement (volat­ile).

Users and permis­sions

Users and groups are both roles. User = can login, group = cannot login.
Roles can be granted to other roles.
Roles can impers­onate roles granted to them.
GRANT adminrole to userrole
means userrole can 
SET ROLE adminrole
and impers­onate.

Access checklist

Can connect according to pg_hba
Has LOGIN permission
Has CONNECT on DB
Has USAGE on schema­/na­mespace
Has <op­era­tio­n> on table/­column of queryable

GRANT/­REVOKE: object permis­sions

Everyone has CONNECT and TEMP permis­sions on DBs through 
public
group.
DROP and ALTER belong to the owner role only.
Change default permis­sions with:
ALTER DEFAULT PRIVILEGES
FOR ROLE object­_cr­eator
permis­sio­n_stmt`
See psql section to view permis­sions.

pg_hba: authen­tic­ation management

TYPE
 
local | host
DATABASE
 
all | replic­ation | db_name
USER
 
role
for specific role, 
+role
for group
ADDRESS
Origin address
METHOD
Accepted auth method, see man page
Space-­sep­arated file, one rule per line with above fields.
Config­uration reload required to apply.
  

"­Spe­cia­l" data files

pg_wal
Write-­ahead log
pg_xact
Commit data
pg_com­mit_ts
Commit timestamps
pg_mul­tixacts
States of multiple transa­ctions
pg_serial
States of serial­izable transa­ctions
pg_two­phase
States of prepared transa­ctions
pg_dyn­­shmem
Dynamic shared memory
pg_logical
Logical replic­­ation
pg_notify
Listen­­/N­otify states
pg_repslot
Replic­­ation slots
pg_sna­­pshots
Exported snapshots
"­Pri­vat­e" files, should not be edited in normal operation

Data files

"­Nor­mal­" files
Tables, indexes
TOAST
Oversized object storage
FSM
Free space map
VM
Visibility map
Filesize is max 1 GB except TOAST files.
Files over 1 GB are split into .1, .2, .3, etc.

WAL

Filename split into timeline, logical file, physical file (segment).
Logical filesize max 4 GB.
LSN stored is only determ­inable with 16 MB physical WAL files (offset at end of filename).
pg_cur­ren­t_w­al_­lsn()
pg_wal­fil­e_n­ame­(lsn) to get WAL file containing LSN

Storage best practices

Recomm­ended: ext4 first, xfs second
zfs possible but not well-known yet
ext4 recomm­ended parame­ters: 
noatime, data=w­rit­eback

On Linux, consider scheduler config changes depending on hardware
RAID 10 preferable to raid 5 for controller load reasons
Better hardware badly configured usually outper­forms well-c­onf­igured worse hardware.

Perfor­mance tracking

Use extension pg_sta­t_s­tat­ements to track execution times. ⚠️Requires restart to install⚠️
Note parameters pg_sta­t_s­tat­eme­nts.track and pg_sta­t_s­tat­eme­nts.tr­ack­_pl­anning (<- costly)
Query pg_sta­t_s­tat­ements to view results.

External utilities

pg_act­ivity
 
top
-like monitor
dbeaver
GUI tool for DDL visual­ization
pgadmin
Web-based tool (slow)
pgloader
Data Migration tool from other psql instance, CSV or other DBMS
pgHero
Perfor­mance dashboard for Postgres
pgTune
Perfor­mance config­uration tuning tool
 

psql things

Connect from system shell
psql -h hostname -p port -U role -d DB
Connect from psql shell
\c db user host port
Execute sql script
\i file
Execute shell command
\! command
Edit psql options
\set [variable=value]
Execute command every 3 seconds 2 times
\watch i=2 c=3
Get help on SQL command
\h command
Edit command in external editor and execute
\e
Make wide tables readable
\x on|auto
Profile script
Located in ~/.psqlrc
Re-run last command
\g (\gx to output as \x on)
Can supply connection string instead of connection arguments

List DB objects with psql

\l
Databases
\dt
Tables
\dn
Namespaces (aka schemas)
\d name
Describe queryable or index
\d
List everything
\di
Indexes
\ds
Sequences
\dp
Permis­sions
\du
Roles (users­/gr­oups)
\dv
Views
\dx
Extensions
\dn
Namespaces (schemas)
See psql manual
All commands can take a filter pattern as argument.
See "­search path" above.

Search path

Alter for this session
SET search­_path TO my_db;
Persist for this DB
ALTER DATABASE SET search­_path to ... ;
ALTER DATABASE SET search­_path FROM CURRENT;
Persist for this instance
postgr­esq­l.conf
"­Lis­t" psql commands (
\d
) only show what is in the search path by default.

postgr­esq­l.conf

listen­_ad­dresses
interfaces to listen on (ipv4 and v6)
port
TCP port
max_co­nne­ctions
Max simult­aneous sessions
ssl
Enable encrypted transport
shared­_bu­ffers
Instan­ce-wide shared memory (see oracle sga)
temp_b­uffers
Dedicated memory per session (see oracle pga)
work_mem
Dedicated memory per query
mainte­nan­ce_­wor­k_mem
Memory reserved for mainte­nance ops (vacuum, index creation, etc)
autova­cuu­m_w­ork_mem
Memory dedicated to autovacuum processes

postgr­esq­l.conf: planne­r/o­pti­mizer

seq_pa­ge_cost
Sequential page read cost
random­_pa­ge_cost
Random page read cost
cpu_op­era­tor­_cost
Operator call cost
effect­ive­_ca­che­_size
Estimated system cache size
defaul­t_s­tat­ist­ics­_target
"­Target qualit­y" of the optimizer: higher values takes longer to calculate plan but yields better results
from_c­oll­aps­e_limit
Merge nested FROM (to avoid "­reorder experi­men­tat­ion­" from the planner) if sub-FROM would yield less than this setting
join_c­oll­aps­e_limit
See from_c­oll­aps­e_limit but with JOINs instead of FROMs

postgr­es.c­onf: autovacuum

autovacuum
Enable­/di­sable autovacuum
autova­cuu­m_m­ax_­workers
Max parallel autovac jobs
autova­cuu­m_n­aptime
Max delay before autovac
autova­cuu­m_v­acu­um_­cos­t_limit
Accumu­lated I/O cost of autovac before pausing
autova­cuu­m_v­acu­um_­cos­t_delay
Duration of autovacuum pause (see previous)
autova­cuu­m_v­acu­um_­thr­eshold
Insert­/update threshold to trigger autovac (absolute)
autova­cuu­m_v­acu­um_­sca­le_­factor
Insert­/update threshold to trigger autovac (relative)
Autovacuum keeps count of I/O (as "­cos­t"). Once cost has reached autova­cuu­m_v­acu­um_­cos­t_limit, it pauses for autova­cuu­m_v­acu­um_­cos­t_delay.
Vacuum threshold is the sum of vacuum­_th­reshold and vacuum­_sc­ale­_factor.
Autovacuum trigger thresholds have separate, identical parameters for inserts.

postgr­es.c­onf: WAL

wal_level
Transa­ction log level: minimal (no log), replica, logical
fsync
Enable write syncs
synchr­ono­us_­commit
Wait for writes to be committed to disk before sending commit to client
wal_sy­nc_­method
Method used to invalidate WAL caches
full_p­age­_writes
Write complete pages to WAL
wal_bu­ffers
Size of WAL buffers in RAM
checkp­oin­t_t­imeout
Max delay between checkp­oints
checkp­oin­t_c­omp­let­ion­_target
Spread checkpoint over n times checkp­oin­t_t­imeout
min/ma­x_w­al_size
Soft limits (targets) for WAL size

postgr­es.c­onf: replic­ation

archiv­e_mode
archiv­e_c­ommand
max_wa­l_s­enders
Max concurrent replic­ation threads
wal_ke­ep_size
Size of preserved WAL segments on the primary
max_re­pli­cat­ion­_slots
Number of replic­ation slots (max concurrent standby connec­tions)
hot_st­andby
When in standby (in recovery), accept connec­tions
hot_st­and­by_­fee­dback
When in standby, give status feedback to primary

postgr­es.c­onf: logging

log_fi­lename
Log filename pattern
log_mi­n_m­essages
Minimum event severity to be logged
log_mi­n_d­ura­tio­n_s­tat­ement
Log statements longer than value
log_ch­eck­point
Log checkp­oints
log_li­ne_­prefix
Prefix for all log lines
log_lo­ck_­waits
Log lock waits longer than value
log_st­atement
Log SQL statements (DML only, DDL only, ...)
log_te­mp_­files
Log temp files larger than value

postgr­es.conf

Main postgresql config file.
Settings with context = sighup -> send SIGHUP to postgres to apply.
Settings with context = postmaster -> restart instance to apply.
Settings with context = user can be overriden for own user
Can be hot-sw­apped with SET or ALTER SYSTEM­/DA­TAB­ASE­/SE­SSION.
Per-da­tab­ase­/pe­r-s­ession settings are saved in pg_db_­rol­e_s­etting and pg_class.
ALTER SYSTEM applies changes to postgr­es.a­ut­o.conf which is read last/has priority. ALTER SYSTEM is not necess­arily applied immedi­ately depending on context of setting. Check pg_set­tings for the actual running config (and note pendin­g_r­est­art).
See 
systemctl reload
or 
pg_rel­oad­_conf()
.