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

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

PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases In this tutorial, I’ll walk you through the process of scheduling a Materialized View refresh using the pg_cron extension in PostgreSQL. Specifically, we’ll cover how to execute a scheduled SQL command in a different database using cron.schedule_in_database . Step 1: Enable the pg_cron Extension in Azure Portal On Azure PostgreSQL Flexible Server, the pg_cron extension is managed directly through the Azure portal. Follow these steps: Go to the Azure portal and navigate to your PostgreSQL Flexible Server instance. Under the Server Parameters section, locate the azure.extensions parameter. Add PG_CRON to the list of allowed extensions (as shown in the screenshot). Once enabled, the extension is automatically installed in the default `postgres` database . If you need to schedule jobs in o...

Let's create a table with 20 millions of random data. Be sure to index data column for comparing performance results.  CREATE TABLE not_partitioned_table (     id SERIAL PRIMARY KEY,     user_id INTEGER NOT NULL,     transaction_date DATE NOT NULL,     amount DECIMAL(10, 2),     status VARCHAR(20),     description TEXT,     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create an index on the transaction_date column CREATE INDEX idx_transaction_date ON not_partitioned_table(transaction_date); DO $$  DECLARE      i INT; BEGIN     FOR i IN 1..20000000 LOOP         INSERT INTO partitioned_table (user_id, transaction_date, amount, status, description)         VALUES (             (RANDOM() * 100000)::INT,             ...

Using the at Command for Scheduled Maintenance We may need some maintenance works for our database systems. If we don't want to spend our evening with all the rebooting processes, the at command is here to help us. echo "<command>" | at 22:00 Jan 18 2024 This command above runs a specific job at the desired date and time. Here's a more specific example for maintenance on a PostgreSQL database: echo "yum update -y\nsystemctl stop postgresql-15.service\nreboot" | at 23:00 Jan 20 2024 We can list all jobs with the atq command. Moreover, using at -c <id> , we can check the details of scheduled commands. If a schedule was entered mistakenly, the at -r <id> command would help us remove it. atq at -c <id> at -r <id> This simple yet powerful tool can save you from staying up late for...

 Note: Before all steps, make sure that postgreSQL is installed to both primary and standby servers. If you don't have any primary server yet, make sure to initialize and put into running state. Do not initialize any database on standby side. 1- Be sure about primary and replica database server's firewall policies. They should have access to each other through postgreSQL port. 2- On the primary side, add a line as below in order to replication user access: host     replication     rep_user     <replica_ip>/32     scram-sha-256 3- Create replication user on primary server with postgres user. createuser -U postgres rep_user -P --replication -p <port> 4- Find the relevant lines in postgresql.conf below and edit: max_wal_senders=10 max_replication_slots=10 wal_keep_size=50000MB max_slot_wal_keep_size=50000MB wal_level=replica Note: Adjust wal_keep_size for your needs. Wal files will override when they reach size. 5- In order to bypass...

 Normally, you can't restore a single database from a cluster backup that are taken via pg_dumpall.In order to achieve that, you can follow the steps below: 1- Create a script that greps specific database portion of the dump: # ! / bin / bash [ $# - lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1 ; } sed "/connect.*$2/,\$!d" $ 1 | sed "/PostgreSQL database dump complete/,\$d" 2- Extract the portion via example command below: sh onedbscript.sh full_dump.dmp MY_DATABASE > onlyonedatabase.dmp 3- You may restore and test if it worked if you want: psql -p <portnumber> -d MY_DATABASE < onlyonedatabase.dmp

Initial Steps Summary Let’s sum up the initial steps, then dive into key points: Ensure that the primary and replica servers have proper firewall configurations. Both servers should be able to communicate over PostgreSQL service ports (default: 5432 ). Edit pg_hba.conf on the Primary Database by adding the following line to allow the replica server to connect: host replication rep_user /32 scram-sha-256 Create a replica user on the Primary Database with the following command: createuser -U postgres rep_user -P --replication -p Edit the postgresql.conf file on the Primary Database: wal_keep_size = 10000 MB max_slot_wal_keep_size = 10000 MB Note: Adjust these values based on your needs. For a high data load, increase the value. For a low data load, decrease it. The above values assume that, at most, 10GB of data might fail to be sent during an outage. Key Points 1. Handling Large Databases For large databases, replication may take hou...