DB Notes

PostgreSQL 16 Data Dictionary & Catalog Cheat-Sheet PostgreSQL ships with a rich “data dictionary” — a collection of catalog tables , views and utility functions stored in the builtin pg_catalog schema. They let you inspect almost every aspect of a running cluster without touching the on-disk files. All catalog names in this guide are lower-case and live in pg_catalog unless noted otherwise. 1 · The pg_catalog schema Automatically created for every database Always implicitly included at the front of search_path Contains: system tables (e.g. pg_class ), builtin functions (e.g. pg_database_size() ), and handy views (e.g. pg_stat_activity ) 2 · High-Value Catalog Tables Table What you get pg_tables view All user tables visible in current database pg_indexes view Index list plus definition (handy for DDL generators) pg_constraints CHECK, PK, FK & UNIQUE definitions pg_trigger All triggers & their firin...

PostgreSQL 16 Server Configuration & Tuning Cheatsheet 1 · Parameter Fundamentals Case-insensitive names. Value types: boolean · integer · float · string · enum. Precedence: SET (in-session) → ALTER SYSTEM ( postgresql.auto.conf ) → postgresql.conf → internal defaults. 2 · Inspecting & Changing Parameters Scope Command Effect Session SET work_mem = '64MB'; Lasts until disconnect or RESET . Database ALTER DATABASE mydb SET work_mem = '128MB'; For every new session in mydb . Role ALTER ROLE analytics SET work_mem = '256MB'; Overrides DB setting for that user. Cluster-wide ALTER SYSTEM SET work_mem = '512MB'; Stored in postgresql.auto.conf . Reload SELECT pg_reload_conf(); No downtime for most GUCs. 3 · Core Categories & Quick Rules 3.1 Connection GUC Default Note listen_addresses * Bind address list. port 5432 Postgres TCP port. max_connections 100 Back-ends allowed. superuser_reserved_connecti...

Managing a PostgreSQL Cluster with pg_ctl & pg_controldata PostgreSQL Cluster Control Cheat-Sheet 1 · What Exactly Is a “Cluster”? In PostgreSQL terminology, a cluster is a self-contained instance: one data directory, one listening port, one set of background processes. Multiple databases live inside, but they all share the same WAL stream and config files ( postgresql.conf , pg_hba.conf , …). 2 · Starting a Cluster ( pg_ctl start ) # basic invocation (foreground wait) pg_ctl -D /pgdata -l /var/log/pg_start.log start # common flags -l logfile # redirect server stdout/stderr -w / -W # wait / don’t wait for startup confirmation -o "-c port=5434" # extra postmaster options (here: override port) systemd users: prefer systemctl start postgresql-16 . But pg_ctl is still essential for ad-hoc clusters, containers, or single-user mode troubleshooting. 3 · Stopping a Cluster ( pg_ctl stop ) ...

Resolving PostgreSQL 17 Dependency Error on Oracle Linux 8 Fixing perl(IPC::Run) Dependency Error When Installing PostgreSQL 17 on Oracle Linux 8 Background While preparing a fresh Oracle Linux 8 server for a PostgreSQL 17 deployment, an attempt to install all PostgreSQL packages in one go failed with an unsatisfied dependency error: Problem 1: cannot install the best candidate for the job - nothing provides perl(IPC::Run) needed by postgresql17-devel-17.4-1PGDG.rhel8.x86_64 ... Root Cause The perl(IPC::Run) module (along with several tool‑chain libraries) resides in the CodeReady Builder repository, which is disabled by default on Oracle Linux 8. Because postgresql17-devel and postgresql17-test depend on that module, dnf / yum cannot resolve the full dependency tree unless the repository is enabled. CodeReady Builder hosts developer‑oriented packages—compilers, debuggers, Perl/Python modules—that ma...

Mastering psql — Command-Line Power Tips psql Cheat-Sheet & Power User Guide 1 · Startup Sequence Environment – PGHOST , PGPORT , PGUSER , PGDATABASE are honoured. User profile – reads $HOME/.psqlrc (skip with -X ). Single-shot execution -f FILE – run file then quit -c "COMMAND" – inline SQL / meta cmd then quit --help prints all options · --version shows build info. 2 · Line Editing & History Arrow-up / down cycles past commands (libreadline). Tab completion on Unix (SQL keywords, objects, filenames). History & Buffer Description \s Show command history (same as ~/.psql_history ) \s FILE Save history to FILE \e Edit current query buffer in $EDITOR , then execute \e FILE Open FILE in editor, then execute contents \w FILE Write current buffer to FILE (do not execute) 3 · Controlling Output Command Effect -o F...

Enterprise‑Grade PostgreSQL 16 Installation on RHEL PostgreSQL 16 Enterprise‑Grade Installation Guide RHEL 8/9 Edition 1 · Preparation Checklist 64‑bit RHEL 8/9 minimal install, fully patched. Dedicated LVM volume: /pgdata  → data files (XFS, noatime) /pgwal  → WAL (optional, SSD / NVMe for low latency) Outbound HTTPS for the official PostgreSQL YUM repository. Root (or sudo) access for initial setup; thereafter run the service as a locked postgres OS user. 2 · Create the  postgres  Service Account # groupadd --system postgres # useradd --system --gid postgres --home-dir /pgdata --shell /bin/bash postgres # passwd -l postgres # lock password (SSH key / sudo only) Why? Running the server under an unprivileged user isolates the cluster and its files from the rest of the OS—exactly as recommended in the EDB training. 3 · Kernel & System Tuning cat >/etc/sysctl.d/99-postgresql-tuning.conf ...

PostgreSQL Reference Guide PostgreSQL Reference Guide General Database Limits Maximum Database Size: Unlimited Maximum Table Size: 32 TB Maximum Row Size: 1.6 TB Maximum Field Size: 1 GB Maximum Rows per Table: Unlimited Maximum Columns per Table: 250–1600 (depends on column types) Maximum Indexes per Table: Unlimited Common Database Object Names Industry Term PostgreSQL Term Table or Index Relation Row Tuple Column Attribute Data Block Page (on disk) Page Buffer (in memory) Process & Memory Architecture 1. Top‑Level Process postmaster (also postgres ) is the parent daemon. It listens on 5432 (by default), accepts client connections, and forks a dedicated backend for each session. 2. Shared Memory Areas Component Purpose Shared Buffers Main buffer cache holding data pages read from disk. WAL Buffers Staging area for write‑ahead log ...