DB Notes

Rescuing a Dropped PostgreSQL Database: Two pgBackRest Paths to Success That sinking feeling when a DROP DATABASE command slips through... we've all been there, or at least dreaded it. The good news is that with robust tools like PostgreSQL and pgBackRest, recovery is often very achievable. I recently put this to the test after "accidentally" dropping a database named NEWDB and wanted to share two distinct, successful methods I used to bring it back. The Setup: A PostgreSQL cluster with several databases, including one I created called NEWDB . Data was added to NEWDB . A full backup was taken using pgBackRest while NEWDB existed . Then, the fateful command: DROP DATABASE "NEWDB"; The mission: Restore NEWDB without impacting the other live databases. Method 1: The Classic & Controlled - Restore to Temporary, then pg_dump / pg_restore This is a well-trodden and highly reliable path for surgically extracting specific data f...

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

Oracle 19c Data Guard – Standby Creation, Switchover & Failover Oracle 19c Physical Standby Creation & Management (RMAN Duplicate + Data Guard Broker) This article walks through building a physical standby using RMAN  DUPLICATE FROM ACTIVE DATABASE , then managing switchover / failover with the Data Guard Broker. The commands were tested on Oracle Linux 8 with Oracle Database 19c . Assumptions Two hosts ( oracle1.localdomain & oracle2.localdomain ) with Oracle 19c installed. Primary instance runs on oracle1 ; standby will be created on oracle2 . Listener port 1521 is reachable in both directions (check firewalls). Primary DB name MYDBPROD ; standby unique name MYDBDG . 1 – Primary Server Preparation 1.1 Switch to ARCHIVELOG & Force Logging -- Check mode SELECT log_mode FROM v$database; -- If NOARCHIVELOG: SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCH...