\documentclass[10pt,a4paper]{article}

% Packages
\usepackage{fancyhdr}           % For header and footer
\usepackage{multicol}           % Allows multicols in tables
\usepackage{tabularx}           % Intelligent column widths
\usepackage{tabulary}           % Used in header and footer
\usepackage{hhline}             % Border under tables
\usepackage{graphicx}           % For images
\usepackage{xcolor}             % For hex colours
%\usepackage[utf8x]{inputenc}    % For unicode character support
\usepackage[T1]{fontenc}        % Without this we get weird character replacements
\usepackage{colortbl}           % For coloured tables
\usepackage{setspace}           % For line height
\usepackage{lastpage}           % Needed for total page number
\usepackage{seqsplit}           % Splits long words.
%\usepackage{opensans}          % Can't make this work so far. Shame. Would be lovely.
\usepackage[normalem]{ulem}     % For underlining links
% Most of the following are not required for the majority
% of cheat sheets but are needed for some symbol support.
\usepackage{amsmath}            % Symbols
\usepackage{MnSymbol}           % Symbols
\usepackage{wasysym}            % Symbols
%\usepackage[english,german,french,spanish,italian]{babel}              % Languages

% Document Info
\author{armk}
\pdfinfo{
  /Title (postgresql-101-for-dbas-sysadmins.pdf)
  /Creator (Cheatography)
  /Author (armk)
  /Subject (PostgreSQL 101 for DBAs/sysadmins Cheat Sheet)
}

% Lengths and widths
\addtolength{\textwidth}{6cm}
\addtolength{\textheight}{-1cm}
\addtolength{\hoffset}{-3cm}
\addtolength{\voffset}{-2cm}
\setlength{\tabcolsep}{0.2cm} % Space between columns
\setlength{\headsep}{-12pt} % Reduce space between header and content
\setlength{\headheight}{85pt} % If less, LaTeX automatically increases it
\renewcommand{\footrulewidth}{0pt} % Remove footer line
\renewcommand{\headrulewidth}{0pt} % Remove header line
\renewcommand{\seqinsert}{\ifmmode\allowbreak\else\-\fi} % Hyphens in seqsplit
% This two commands together give roughly
% the right line height in the tables
\renewcommand{\arraystretch}{1.3}
\onehalfspacing

% Commands
\newcommand{\SetRowColor}[1]{\noalign{\gdef\RowColorName{#1}}\rowcolor{\RowColorName}} % Shortcut for row colour
\newcommand{\mymulticolumn}[3]{\multicolumn{#1}{>{\columncolor{\RowColorName}}#2}{#3}} % For coloured multi-cols
\newcolumntype{x}[1]{>{\raggedright}p{#1}} % New column types for ragged-right paragraph columns
\newcommand{\tn}{\tabularnewline} % Required as custom column type in use

% Font and Colours
\definecolor{HeadBackground}{HTML}{333333}
\definecolor{FootBackground}{HTML}{666666}
\definecolor{TextColor}{HTML}{333333}
\definecolor{DarkBackground}{HTML}{336791}
\definecolor{LightBackground}{HTML}{F2F5F8}
\renewcommand{\familydefault}{\sfdefault}
\color{TextColor}

% Header and Footer
\pagestyle{fancy}
\fancyhead{} % Set header to blank
\fancyfoot{} % Set footer to blank
\fancyhead[L]{
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{C}
    \SetRowColor{DarkBackground}
    \vspace{-7pt}
    {\parbox{\dimexpr\textwidth-2\fboxsep\relax}{\noindent
        \hspace*{-6pt}\includegraphics[width=5.8cm]{/web/www.cheatography.com/public/images/cheatography_logo.pdf}}
    }
\end{tabulary}
\columnbreak
\begin{tabulary}{11cm}{L}
    \vspace{-2pt}\large{\bf{\textcolor{DarkBackground}{\textrm{PostgreSQL 101 for DBAs/sysadmins Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{armk} via \textcolor{DarkBackground}{\uline{cheatography.com/215747/cs/47026/}}}
\end{tabulary}
\end{multicols}}

\fancyfoot[L]{ \footnotesize
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{LL}
  \SetRowColor{FootBackground}
  \mymulticolumn{2}{p{5.377cm}}{\bf\textcolor{white}{Cheatographer}}  \\
  \vspace{-2pt}armk \\
  \uline{cheatography.com/armk} \\
  \end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Cheat Sheet}}  \\
   \vspace{-2pt}Published 19th September, 2025.\\
   Updated 19th September, 2025.\\
   Page {\thepage} of \pageref{LastPage}.
\end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Sponsor}}  \\
  \SetRowColor{white}
  \vspace{-5pt}
  %\includegraphics[width=48px,height=48px]{dave.jpeg}
  Measure your website readability!\\
  www.readability-score.com
\end{tabulary}
\end{multicols}}




\begin{document}
\raggedright
\raggedcolumns

% Set font size to small. Switch to any value
% from this page to resize cheat sheet text:
% www.emerson.emory.edu/services/latex/latex_169.html
\footnotesize % Small font.

\begin{multicols*}{2}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Grab info from existing cluster}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{netstat -nap | grep postgres  \# To grab listening port if not default \newline ps uf -C postgres  \# List all running instances w/ process owner \newline ps f -U {\emph{instance\_owner}}  \# With user found above, to see startup args \newline psql -c SHOW data\_directory;  \# To get the main cluster directory aka data directory \newline psql -c SHOW config\_file;  \# To get the config file path, useful if nondefault \newline su - {\emph{db\_owner}} pg\_controldata {\emph{data\_directory}}  \# To get cluster details} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{On redhat: all config files in `/var/lib/pgsql/{\emph{\textless{}version\textgreater{}}}/data` by default \newline On debian: `postgres.conf` in `/etc` by default}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.28 cm} x{4.72 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{pg\_settings view structure}}  \tn
% Row 0
\SetRowColor{LightBackground}
name & Setting name \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
value & Current value \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
context & Context of the setting (what to restart to update the value) \tn 
% Row Count 5 (+ 3)
% Row 3
\SetRowColor{white}
\seqsplit{source/sourcefile/sourceline} & Where the setting is defined \tn 
% Row Count 7 (+ 2)
% Row 4
\SetRowColor{LightBackground}
setting/unit & Value of the setting \tn 
% Row Count 8 (+ 1)
% Row 5
\SetRowColor{white}
boot\_val & Value at instance startup \tn 
% Row Count 10 (+ 2)
% Row 6
\SetRowColor{LightBackground}
reset\_val & Default value \tn 
% Row Count 11 (+ 1)
% Row 7
\SetRowColor{white}
pending\_restart & Value has pending modifications \tn 
% Row Count 13 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Contains a detailed view current running config, refers to the same settings as psql's SHOW/SET.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Instance files on disk}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{One directory per instance containing binaries, config and default datafile location ("the {\emph{cluster}}"). All files owned by db service account with at least chmod u+rw (+x on dirs). \newline % Row Count 4 (+ 4)
Datafiles are stored into one subdir per DB. \newline % Row Count 5 (+ 1)
The pg\_class table contains a map of DB objects to file paths. \newline % Row Count 7 (+ 2)
Default cluster dir depends on package and distro: redhat is /var/lib/pgsql/\textless{}version\textgreater{} \newline % Row Count 9 (+ 2)
Every "user" object and datafile is located in the `pg\_default` tablespace by default: hardcoded to `base` subdirectory of the cluster directory. All objects (including entire DBs) can be moved to tablespaces located in any accessible path. Once declared, a "tablespace folder" is an integral, non-optional part of the cluster.% Row Count 16 (+ 7)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.32 cm} x{5.68 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Tools}}  \tn
% Row 0
\SetRowColor{LightBackground}
initdb & Create file/dir structure (aka cluster) for instance \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
createdb, dropdb & Create/delete a DB \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
pg\_ctl & Control instance state \tn 
% Row Count 5 (+ 1)
% Row 3
\SetRowColor{white}
\seqsplit{pg\_controldata} & View config \tn 
% Row Count 7 (+ 2)
% Row 4
\SetRowColor{LightBackground}
pg\_isready & Check if instance is up w/o opening a full connection \tn 
% Row Count 9 (+ 2)
% Row 5
\SetRowColor{white}
\seqsplit{pg\_resetwal} & {\bf{DANGEROUS}} Wipe transaction logs \tn 
% Row Count 11 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{By default in /usr/pgsql-{\emph{\textless{}version\textgreater{}}}/bin (on redhat).}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Performance tracking}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Use extension \{\{link="https://www.postgresql.org/docs/current/pgstatstatements.html"\}\}pg\_stat\_statements\{\{/link\}\} to track execution times. ⚠️Requires restart to install⚠️ \newline % Row Count 4 (+ 4)
Note parameters {\bf{pg\_stat\_statements.track}} and {\bf{pg\_stat\_statements.track\_planning}} (\textless{}- costly) \newline % Row Count 6 (+ 2)
Query {\bf{pg\_stat\_statements}} to view results. \newline % Row Count 7 (+ 1)
General slowness issues are often caused by autovacuum and/or checkpoint configuration.% Row Count 9 (+ 2)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Users and permissions}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Postgres does RBAC by default. \newline % Row Count 1 (+ 1)
No distinction between user and group: they are both {\bf{roles}}. \newline % Row Count 3 (+ 2)
Colloquially, group = role with no LOGIN option. \newline % Row Count 4 (+ 1)
Permissions are GRANTed/DENYed to roles. \newline % Row Count 5 (+ 1)
Roles can be granted to other roles. \newline % Row Count 6 (+ 1)
Roles can impersonate any role granted to them: an user can act as any of the groups he's a part of with SET ROLE.% Row Count 9 (+ 3)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{See \{\{link="https://www.postgresql.org/docs/current/user-manag.html"\}\}21. Database Roles\{\{/link\}\}}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{GRANT/REVOKE: object permissions}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Everyone has CONNECT and TEMP permissions on DBs through `public` group. \newline % Row Count 2 (+ 2)
DROP and ALTER belong to the owner role only. \newline % Row Count 3 (+ 1)
Change default permissions with: \newline % Row Count 4 (+ 1)
ALTER DEFAULT PRIVILEGES  \newline % Row Count 5 (+ 1)
FOR ROLE {\emph{object\_creator}} \newline % Row Count 6 (+ 1)
{\emph{permission\_stmt}}`% Row Count 7 (+ 1)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{See psql section to view permissions.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Access checklist}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Can connect according to pg\_hba} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Has LOGIN permission} \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Has CONNECT on DB} \tn 
% Row Count 3 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Has USAGE on schema/namespace} \tn 
% Row Count 4 (+ 1)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Has \textless{}operation\textgreater{} on table/column of queryable} \tn 
% Row Count 5 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Create new instance}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{initdb \textbackslash{} \newline  -A auth\_method \textbackslash{} \newline  -D data\_directory \textbackslash{} \newline  -E encoding \textbackslash{} \newline  -X tlog\_directory \textbackslash{}  \newline  -{}-locale=locale \textbackslash{} \newline  {[}-k{]} \newline \# Start from service \newline systemctl enable postgres \newline systemctl start postgres \newline \# Start standalone \newline /usr/pgsql-16/bin/pg\_ctl -D data\_directory start \newline \# Connect as postgres w/o passwd  \newline sudo -u postgres psql -w} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Check instance state with "systemctl postgres-16 status" or pg\_isready}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.36 cm} x{6.64 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{pg\_hba: authentication management}}  \tn
% Row 0
\SetRowColor{LightBackground}
TYPE & local | host \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\seqsplit{DATABASE} & all | replication | {\emph{db\_name}} \tn 
% Row Count 3 (+ 2)
% Row 2
\SetRowColor{LightBackground}
USER & `{\emph{role}}` for specific role, `+{\emph{role}}` for group \tn 
% Row Count 5 (+ 2)
% Row 3
\SetRowColor{white}
\seqsplit{ADDRESS} & Origin address \tn 
% Row Count 7 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{METHOD} & Accepted auth method, see man page \tn 
% Row Count 9 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Space-separated file, one rule per line with above fields. \newline Configuration reload required to apply. \newline See \{\{link="https://www.postgresql.org/docs/current/client-authentication.html"\}\}20. Client Authentication\{\{/link\}\}}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Tablespace/DB size management}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{PostgreSQL has {\bf{no size limit mechanism}} and will only stop growing datafiles when the OS stops it (typically on full filesystem). Size quotas must be {\bf{enforced at the file level}}. \newline % Row Count 4 (+ 4)
{\bf{Instance-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). \newline % Row Count 8 (+ 4)
{\bf{Database}} or {\bf{object-level}} quotas can be done by housing the DB/object in a tablespace located in dedicated filesystems/under different quota rules.% Row Count 12 (+ 4)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Low level/manual backup/restore flow}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Physical backup: run pg\_backup\_start, copy data files to backup storage, call pg\_backup\_stop and save its output to backup storage. \newline % Row Count 3 (+ 3)
PITR restore: Copy backed up files in cluster "restore" directory, edit postgresql.conf with restore\_command to fetch WAL files, set recovery\_* options with point in time and end-of-restore \seqsplit{(recovery\_target\_action)} options, create restore/recovery.signal file, start instance.% Row Count 9 (+ 6)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.4 cm} x{5.6 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Backup \& restore tools}}  \tn
% Row 0
\SetRowColor{LightBackground}
pg\_dump & Logical dumps of single DB \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
pg\_dumpall & Logical dumps of entire instance \tn 
% Row Count 3 (+ 2)
% Row 2
\SetRowColor{LightBackground}
pg\_restore & Apply logical dumps for restoration \tn 
% Row Count 5 (+ 2)
% Row 3
\SetRowColor{white}
archiver & Archive transaction logs, see archive\_command in postgresql.conf \tn 
% Row Count 8 (+ 3)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{pg\_backup\_start()} & Prepares DB for physical backup \tn 
% Row Count 10 (+ 2)
% Row 5
\SetRowColor{white}
\seqsplit{pg\_backup\_stop()} & Ends physical backup process and returns missing data necessary for backed up cluster to be consistent \tn 
% Row Count 14 (+ 4)
% Row 6
\SetRowColor{LightBackground}
\seqsplit{pg\_basebackup} & Physical backup automation tool, does pg\_backup\_* calls and file copies automatically \{\{nl\}\} Only tool available on Windows \tn 
% Row Count 19 (+ 5)
% Row 7
\SetRowColor{white}
\seqsplit{pg\_verifybackup} & Check "plain" type backup integrity \tn 
% Row Count 21 (+ 2)
% Row 8
\SetRowColor{LightBackground}
\seqsplit{pg\_receivewal} & Transparent, pull-style backup and WAL archiver tool \tn 
% Row Count 23 (+ 2)
% Row 9
\SetRowColor{white}
pgBackRest & Backup/restore utility \tn 
% Row Count 24 (+ 1)
% Row 10
\SetRowColor{LightBackground}
pgBarman & Backup/restore utility (prefer pgBackRest) \tn 
% Row Count 26 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{No diff backups natively before version 17 \newline See \{\{link="https://www.postgresql.org/docs/current/backup.html"\}\}25. Backup and Restore\{\{/link\}\}}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{4 cm} x{4 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{psql commands}}  \tn
% Row 0
\SetRowColor{LightBackground}
Connect from system shell & psql -h {\emph{hostname}} -p {\emph{port}} -U {\emph{role}} -d {\emph{DB}} \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
Connect from psql shell & \textbackslash{}c {\emph{db user host port}} \tn 
% Row Count 5 (+ 2)
% Row 2
\SetRowColor{LightBackground}
Execute sql script & \textbackslash{}i {\emph{file}} \tn 
% Row Count 6 (+ 1)
% Row 3
\SetRowColor{white}
Execute shell command & \textbackslash{}! {\emph{command}} \tn 
% Row Count 8 (+ 2)
% Row 4
\SetRowColor{LightBackground}
Edit psql options & \textbackslash{}set {[}{\emph{variable}}={\emph{value}}{]} \tn 
% Row Count 10 (+ 2)
% Row 5
\SetRowColor{white}
Execute command every 3 seconds 2 times & \textbackslash{}watch i=2 c=3 \tn 
% Row Count 12 (+ 2)
% Row 6
\SetRowColor{LightBackground}
Get help on SQL command & \textbackslash{}h {\emph{command}} \tn 
% Row Count 14 (+ 2)
% Row 7
\SetRowColor{white}
Edit command in external editor and execute & \textbackslash{}e \tn 
% Row Count 17 (+ 3)
% Row 8
\SetRowColor{LightBackground}
Make wide tables readable & \textbackslash{}x on|auto \tn 
% Row Count 19 (+ 2)
% Row 9
\SetRowColor{white}
Profile script & Located in \textasciitilde{}/.psqlrc \tn 
% Row Count 20 (+ 1)
% Row 10
\SetRowColor{LightBackground}
Re-run last command & \textbackslash{}g (\textbackslash{}gx to output as \textbackslash{}x on) \tn 
% Row Count 22 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Can supply connection string instead of connection arguments}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.92 cm} x{6.08 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{List DB objects with psql}}  \tn
% Row 0
\SetRowColor{LightBackground}
{\bf{\textbackslash{}l}} & {\bf{Databases}} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
{\bf{\textbackslash{}dt}} & {\bf{Tables}} \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
{\bf{\textbackslash{}dn}} & {\bf{Namespaces (aka schemas)}} \tn 
% Row Count 3 (+ 1)
% Row 3
\SetRowColor{white}
\textbackslash{}d {\emph{name}} & Describe queryable or index \tn 
% Row Count 4 (+ 1)
% Row 4
\SetRowColor{LightBackground}
\textbackslash{}d & List everything \tn 
% Row Count 5 (+ 1)
% Row 5
\SetRowColor{white}
\textbackslash{}di & Indexes \tn 
% Row Count 6 (+ 1)
% Row 6
\SetRowColor{LightBackground}
\textbackslash{}ds & Sequences \tn 
% Row Count 7 (+ 1)
% Row 7
\SetRowColor{white}
\textbackslash{}dp & Permissions \tn 
% Row Count 8 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\textbackslash{}du & Roles (users/groups) \tn 
% Row Count 9 (+ 1)
% Row 9
\SetRowColor{white}
\textbackslash{}dv & Views \tn 
% Row Count 10 (+ 1)
% Row 10
\SetRowColor{LightBackground}
\textbackslash{}dx & Extensions \tn 
% Row Count 11 (+ 1)
% Row 11
\SetRowColor{white}
\textbackslash{}dn & Namespaces (schemas) \tn 
% Row Count 12 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{See \{\{link="https://www.postgresql.org/docs/current/app-psql.html\#APP-PSQL-META-COMMAND-D"\}\}psql manual\{\{/link\}\} \newline All commands can take a filter pattern as argument. \newline See "search path" above.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Configuration files}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Main config file is {\bf{postgresql.conf}} in the main cluster directory (or specified explicitly as startup argument). \newline % Row Count 3 (+ 3)
Settings changed dynamically (via ALTER SYSTEM) are stored into postgresql.auto.conf which is loaded last and has priority on postgresql.conf. {\bf{Do not modify by hand.}} \newline % Row Count 7 (+ 4)
Settings changes (incl. ALTER SYSTEM) are often not applied immediately: see the context column of pg\_settings for how/when setting changes are applied and the pending\_restart column. \newline % Row Count 11 (+ 4)
See \{\{link="https://cheatography.com/armk/cheat-sheets/postgresql-settings-config-quick-reference/"\}\}PostgreSQL settings/config quick reference\{\{/link\}\} for more info on individual settings.% Row Count 15 (+ 4)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.04 cm} x{4.96 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Search path}}  \tn
% Row 0
\SetRowColor{LightBackground}
Alter for this session & SET search\_path TO {\emph{my\_db}}; \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
Persist for this DB & ALTER DATABASE SET search\_path to ... ; \{\{nl\}\} ALTER DATABASE SET search\_path FROM CURRENT; \tn 
% Row Count 6 (+ 4)
% Row 2
\SetRowColor{LightBackground}
Persist for this instance & postgresql.conf \tn 
% Row Count 8 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{"List" psql commands (`\textbackslash{}d`) only show what is in the search path by default.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Processes \& transaction lifecycle}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{{\bf{Backend}} processes transactions by loading cache pages in memory from datafiles and updating them. One per user session} \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{{\bf{WAL writer}} watches WAL buffers and flushes them to disk periodically} \tn 
% Row Count 5 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{{\bf{BG writer}} watches for individual dirty pages in shared memory and writes them to datafiles.} \tn 
% Row Count 7 (+ 2)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{{\bf{Checkpointer}} periodically uses WAL to flush all shared memory written before a checkpoint (automatic or user-requested) to disk.} \tn 
% Row Count 10 (+ 3)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{{\bf{Autovacuum}} periodically reclaims invalidated cache pages.} \tn 
% Row Count 12 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{WAL writer, checkpointer and autovacuum work on a sleep-wake schedule, BG writer works continuously, backends work during user transactions.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.76 cm} x{6.24 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{External utilities}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{pg\_activity} & `top`-like monitor \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
dbeaver & GUI tool for DDL visualization \tn 
% Row Count 3 (+ 1)
% Row 2
\SetRowColor{LightBackground}
pgadmin & Web-based tool (slow) \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\seqsplit{pgloader} & Data Migration tool from other psql instance, CSV or other DBMS \tn 
% Row Count 7 (+ 3)
% Row 4
\SetRowColor{LightBackground}
pgHero & Performance dashboard for Postgres \tn 
% Row Count 9 (+ 2)
% Row 5
\SetRowColor{white}
pgTune & Performance configuration tuning tool \tn 
% Row Count 11 (+ 2)
% Row 6
\SetRowColor{LightBackground}
\seqsplit{pgBadger} & Web-based monitoring tool (eq. OEM reports) \tn 
% Row Count 13 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.64 cm} x{5.36 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Data files}}  \tn
% Row 0
\SetRowColor{LightBackground}
no extension & Tables, indexes \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
.TOAST & Oversized object storage \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
.FSM & Free space map \tn 
% Row Count 3 (+ 1)
% Row 3
\SetRowColor{white}
.VM & Visibility map \tn 
% Row Count 4 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Filesize is max 1 GB except TOAST files.  \newline Files over 1 GB are split into .1, .2, .3, etc.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.32 cm} x{5.68 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{"Special" data files}}  \tn
% Row 0
\SetRowColor{LightBackground}
pg\_wal & Write-ahead log \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
pg\_xact & Commit data \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{pg\_commit\_ts} & Commit timestamps \tn 
% Row Count 4 (+ 2)
% Row 3
\SetRowColor{white}
\seqsplit{pg\_multixacts} & States of multiple transactions \tn 
% Row Count 6 (+ 2)
% Row 4
\SetRowColor{LightBackground}
pg\_serial & States of serializable transactions \tn 
% Row Count 8 (+ 2)
% Row 5
\SetRowColor{white}
\seqsplit{pg\_twophase} & States of prepared transactions \tn 
% Row Count 10 (+ 2)
% Row 6
\SetRowColor{LightBackground}
\seqsplit{pg\_dynshmem} & Dynamic shared memory \tn 
% Row Count 12 (+ 2)
% Row 7
\SetRowColor{white}
pg\_logical & Logical replication \tn 
% Row Count 13 (+ 1)
% Row 8
\SetRowColor{LightBackground}
pg\_notify & Listen/Notify states \tn 
% Row Count 14 (+ 1)
% Row 9
\SetRowColor{white}
pg\_repslot & Replication slots \tn 
% Row Count 15 (+ 1)
% Row 10
\SetRowColor{LightBackground}
\seqsplit{pg\_snapshots} & Exported snapshots \tn 
% Row Count 17 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{"Private" files, should not be edited in normal operation}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Write-ahead log (WAL)}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Postrgresql's transaction log, stores past transactions + transactions not yet written to datafiles. \newline % Row Count 3 (+ 3)
Stored in the pg\_wal subdirectory, split into 16 MB chunks making up 4 GB logical files. \newline % Row Count 5 (+ 2)
WAL files are considered either "current" or "past". \newline % Row Count 7 (+ 2)
Past WAL files are put into an archive queue \seqsplit{(pg\_wal/archive\_status)} and processed in sequence by the archiver (see \{\{link="https://www.postgresql.org/docs/current/continuous-archiving.html\#BACKUP-ARCHIVING-WAL"\}\}25.3.1. Setting Up WAL Archiving\{\{/link\}\}). \newline % Row Count 13 (+ 6)
`pg\_stat\_archiver` gives info about archiving processes.% Row Count 15 (+ 2)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\{\{link="https://www.postgresql.org/docs/current/continuous-archiving.html\#BACKUP-ARCHIVING-WAL"\}\}25.3.1. Setting Up WAL Archiving\{\{/link\}\} - \{\{link="https://www.postgresql.org/docs/current/wal-configuration.html\#WAL-CONFIGURATION"\}\}28.5. WAL Configuration\{\{/link\}\}}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Storage best practices}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Recommended: ext4 first, xfs second \newline % Row Count 1 (+ 1)
zfs possible but not well-known yet \newline % Row Count 2 (+ 1)
ext4 recommended parameters: `noatime, data=writeback` \newline % Row Count 4 (+ 2)
On Linux, consider scheduler config changes depending on hardware \newline % Row Count 6 (+ 2)
RAID 10 preferable to raid 5 for controller load reasons% Row Count 8 (+ 2)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Better hardware badly configured usually outperforms well-configured worse hardware.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.48 cm} x{5.52 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Official useful plugins}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{pg\_freespacemap} & View free space maps \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
pg\_prewarm & Preload caches from last run at boot \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{pg\_stat\_statements} & Track SQL execution statistics \tn 
% Row Count 6 (+ 2)
% Row 3
\SetRowColor{white}
\seqsplit{auto\_explain} & Trace costly statements automatically \tn 
% Row Count 8 (+ 2)
% Row 4
\SetRowColor{LightBackground}
pgstattuple & Get table stats (live/dead rows, volume of data) \tn 
% Row Count 10 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{View available extensions with \{\{link="https://www.postgresql.org/docs/current/view-pg-available-extensions.html"\}\}pg\_available\_extensions\{\{/link\}\} view or `\textbackslash{}dx{[}+ {\emph{extension}}{]}` \newline Install modules via postgres.conf: \seqsplit{shared\_preload\_libraries} (permanent) or LOAD statement (volatile).}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.2 cm} x{4.8 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{pg\_catalog: system views}}  \tn
% Row 0
\SetRowColor{LightBackground}
pg\_locks & Locks \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\seqsplit{pg\_stat\_database} & DB-wide object statistics \tn 
% Row Count 3 (+ 2)
% Row 2
\SetRowColor{LightBackground}
pg\_class & Object-ID mappings \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\seqsplit{pg\_stat\_*\_tables} & Table-level statistics \tn 
% Row Count 5 (+ 1)
% Row 4
\SetRowColor{LightBackground}
pg\_stats & Column-level stats \tn 
% Row Count 6 (+ 1)
% Row 5
\SetRowColor{white}
\seqsplit{pg\_archiver\_stats} & Archiver status \tn 
% Row Count 8 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Cast table IDs to table names with the ::regclass operator}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}


% That's all folks
\end{multicols*}

\end{document}