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

PostgreSQL 101 for DBAs/sysadmins Cheat Sheet by

Notes from a postgres admin 101 course. Contains a mix of "good-to-know" information about the workings of PostgreSQL and practical tools. ⚠️ This guide is intended for database administration, not usage, and does not cover SQL or "user" information.

Grab info from existing cluster

netstat -nap | grep postgres  # To grab listening port if not default
ps uf -C postgres  # List all running instances w/ process owner
ps f -U instance_owner  # With user found above, to see startup args
psql -c SHOW data_directory;  # To get the main cluster directory aka data directory
psql -c SHOW config_file;  # To get the config file path, useful if nondefault
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

name
Setting name
value
Current value
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
pendin­g_r­estart
Value has pending modifi­cations
Contains a detailed view current running config, refers to the same settings as psql's SHOW/SET.

Instance files on disk

One directory per instance containing binaries, config and default datafile location ("the cluster"). All files owned by db service account with at least chmod u+rw (+x on dirs).
Datafiles are stored into one subdir per DB.
The pg_class table contains a map of DB objects to file paths.
Default cluster dir depends on package and distro: redhat is /var/l­ib/­pgs­ql/­<ve­rsi­on>

Every "­use­r" object and datafile is located in the 
pg_default
tablespace by default: hardcoded to 
base
subdir­ectory of the cluster directory. All objects (including entire DBs) can be moved to tables­paces located in any accessible path. Once declared, a "­tab­lespace folder­" is an integral, non-op­tional part of the cluster.

Tools

initdb
Create file/dir structure (aka cluster) for instance
createdb, dropdb
Create­/delete a DB
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
By default in /usr/p­gsql-<ve­rsi­on>/bin (on redhat).

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.
General slowness issues are often caused by autovacuum and/or checkpoint config­ura­tion.

Users and permis­sions

Postgres does RBAC by default.
No distin­ction between user and group: they are both roles.
Colloq­uially, group = role with no LOGIN option.
Permis­sions are GRANTe­d/D­ENYed to roles.
Roles can be granted to other roles.
Roles can impers­onate any role granted to them: an user can act as any of the groups he's a part of with SET ROLE.

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.

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

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

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.
See 20. Client Authen­tic­ation

Tables­pace/DB size management

PostgreSQL has no size limit mechanism and will only stop growing datafiles when the OS stops it (typically on full filesy­stem). Size quotas must be enforced at the file level.

Instan­ce-­level quotas are enforced by placing the cluster on its dedicated filesystem or placing quotas on the cluster directory at the OS/FS level (eg. XFS quotas).
Database or object­-level quotas can be done by housing the DB/object in a tablespace located in dedicated filesy­ste­ms/­under different quota rules.

Low level/­manual backup­/re­store flow

Physical backup: run pg_bac­kup­_start, copy data files to backup storage, call pg_bac­kup­_stop and save its output to backup storage.

PITR restore: Copy backed up files in cluster "­res­tor­e" directory, edit postgr­esq­l.conf with restor­e_c­ommand to fetch WAL files, set recovery_* options with point in time and end-of­-re­store (recov­ery­_ta­rge­t_a­ction) options, create restor­e/r­eco­ver­y.s­ignal file, start instance.

Backup & restore tools

pg_dump
Logical dumps of single DB
pg_dumpall
Logical dumps of entire instance
pg_restore
Apply logical dumps for restor­ation
archiver
Archive transa­ction logs, see archiv­e_c­ommand in postgr­esq­l.conf
pg_bac­kup­_st­art()
Prepares DB for physical backup
pg_bac­kup­_stop()
Ends physical backup process and returns missing data necessary for backed up cluster to be consistent
pg_bas­ebackup
Physical backup automation tool, does pg_bac­kup_* calls and file copies automa­tically
Only tool available on Windows
pg_ver­ify­backup
Check "­pla­in" type backup integrity
pg_rec­eivewal
Transp­arent, pull-style backup and WAL archiver tool
pgBackRest
Backup­/re­store utility
pgBarman
Backup­/re­store utility (prefer pgBack­Rest)
No diff backups natively before version 17
See 25. Backup and Restore
  

psql commands

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.

Config­uration files

Main config file is postgr­­es­q­l.conf in the main cluster directory (or specified explicitly as startup argument).
Settings changed dynami­cally (via ALTER SYSTEM) are stored into postgr­esq­l.a­uto.conf which is loaded last and has priority on postgr­esq­l.conf. Do not modify by hand.
Settings changes (incl. ALTER SYSTEM) are often not applied immedi­­ately: see the context column of pg_set­tings for how/when setting changes are applied and the pendin­g_r­estart column.
See PostgreSQL settin­gs/­config quick reference for more info on individual settings.

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.

Processes & transa­ction lifecycle

Backend processes transa­ctions by loading cache pages in memory from datafiles and updating them. One per user session
WAL writer watches WAL buffers and flushes them to disk period­ically
BG writer watches for individual dirty pages in shared memory and writes them to datafiles.
Checkp­ointer period­ically uses WAL to flush all shared memory written before a checkpoint (automatic or user-r­equ­ested) to disk.
Autovacuum period­ically reclaims invali­dated cache pages.
WAL writer, checkp­ointer and autovacuum work on a sleep-wake schedule, BG writer works contin­uously, backends work during user transa­ctions.

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
pgBadger
Web-based monitoring tool (eq. OEM reports)

Data files

no extension
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.

"­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

Write-­ahead log (WAL)

Postrg­resql's transa­ction log, stores past transa­ctions + transa­ctions not yet written to datafiles.
Stored in the pg_wal subdir­ectory, split into 16 MB chunks making up 4 GB logical files.
WAL files are considered either "­cur­ren­t" or "­pas­t".
Past WAL files are put into an archive queue (pg_wa­l/a­rch­ive­_st­atus) and processed in sequence by the archiver (see 25.3.1. Setting Up WAL Archiving).
pg_sta­t_a­rchiver
gives info about archiving processes.

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.

Official useful plugins

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).

pg_cat­alog: system views

pg_locks
Locks
pg_sta­t_d­atabase
DB-wide object statistics
pg_class
Object-ID mappings
pg_sta­t_*­_tables
Table-­level statistics
pg_stats
Column­-level stats
pg_arc­hiv­er_­stats
Archiver status
Cast table IDs to table names with the ::regclass operator
 

Metadata

  • Languages:
  • Published: 19th September, 2025

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

          PostgreSQL interactive terminal commands Cheat Sheet
          PostgreSQL Configuration Cheat Sheet

          More Cheat Sheets by armk

          Postgresql settings/config quick reference Cheat Sheet