DB Notes

PostgreSQL Maintenance Cheat Sheet PostgreSQL Maintenance Commands Cheat Sheet VACUUM Frees space from dead tuples and makes pages reusable without locking out writers. -- vacuum a single table VACUUM store.customers; -- vacuum all tables in the current database VACUUM; VACUUM FULL Fully rewrites a table to compact it and return space to the OS (locks the table). -- reclaim disk space on a large table VACUUM FULL store.orders; ANALYZE Collects statistics on column distributions for the planner to choose optimal plans. -- update stats for specific tables ANALYZE store.emp; ANALYZE store.dept; -- analyze entire database ANALYZE; REINDEX Rebuilds an index to remove fragmentation and bloat. -- rebuild a single index REINDEX INDEX store.ix_orderlines_orderid; -- rebuild all indexes on a table REINDEX TABLE store.orderlines; Best Practices Enable autovacuum to run VACUUM & ANALYZE autom...

PostgreSQL Restore Commands Reference PostgreSQL Restore Commands Reference This guide covers the three primary methods to restore PostgreSQL data: using psql for plain‐text dumps, pg_restore for archive-format dumps, and pg_dumpall for full-cluster plain‐text restores. Copy & paste these one-liner commands into your shell and adjust connection or file paths as needed. 1. Restore Plain‐Text Dump with psql Basic command: psql -U <user> -h <host> -p <port> -d <target_db> -f <dumpfile.sql> -d <target_db> : database to restore into. -f <dumpfile.sql> : path to the SQL dump. -1 (optional): wrap in a single transaction to abort on first error. 2. Restore Archive‐Format Dump with pg_restore Basic command: pg_restore -U <user> -h <host> -p <port> -d <target_db> <archive-file> -Fc / -Fd / -Ft : custom, directory...

PostgreSQL Backup Commands Reference 1. Schema-Only Dump (Store Schema) Command: pg_dump --schema-only --schema=store edbstore > store_schema.sql --schema-only : Dumps only the database structure (DDL). --schema=store : Limits the dump to the store schema. Redirects output to store_schema.sql . 2. Data-Only Dump (Triggers Disabled + INSERTs) Command: pg_dump --data-only --disable-triggers --inserts edbstore > edbstore_data.sql --data-only : Dumps only data (INSERT statements). --disable-triggers : Disables all triggers during data load. --inserts : Uses INSERT statements instead of COPY . Redirects output to edbstore_data.sql . 3. Table-Specific Full Dump (Customers Table) Command: pg_dump --table=edbuser.customers edbstore > edbstore_customers.sql --table=edbuser.customers : Dumps only the customers table (DDL + data)....

1. Create the Override Drop-In Open the drop-in editor for the service: sudo systemctl edit postgresql-16.service In the editor that appears, paste the following above the “### Lines below…” comment: [Service] Environment=PGDATA=/your/custom/pgdata/path ### Lines below this comment will be discarded Save and exit: • Nano: Ctrl+O → Enter , then Ctrl+X • Vim: :wq → Enter 2. Reload systemd and Restart PostgreSQL sudo systemctl daemon-reload sudo systemctl restart postgresql-16 sudo systemctl enable postgresql-16 3. Manual File Creation (Alternative) If the editor method fails, create the drop-in file directly: sudo mkdir -p /etc/systemd/system/postgresql-16.service.d sudo tee /etc/systemd/system/postgresql-16.service.d/override.conf <<EOF [Service] Environment=PGDATA=/your/custom/pgdata/path EOF sudo systemctl daemon-reload sudo systemctl restart postgresql-16 4. Verify Your New Data Directory Check service...

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