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