Comprehensive Guide to Postgresql Commands CheatSheet

This guide provides a structured overview of essential PostgreSQL commands, ranging from high-level database administration to granular data manipulation and system monitoring.

1. Database and Schema Management

• CREATE DATABASE name;: Initializes a new database within the current cluster.
• DROP DATABASE name;: Permanently removes a database and all its contents; this action cannot be undone.
• ALTER DATABASE name SET parameter TO value;: Configures database-specific defaults for runtime parameters.
• CREATE SCHEMA myschema;: Creates a new namespace for organizing objects like tables and functions.
• DROP SCHEMA myschema CASCADE;: Removes a schema and all objects (tables, types, etc.) contained within it.
• SET search_path TO myschema, public;: Defines the order in which schemas are searched for unqualified object names.

2. Table and Index Operations (DDL)

• CREATE TABLE table_name (col type, ...);: Defines a new table structure.
• ALTER TABLE name ADD COLUMN col type;: Adds a new column to an existing table.
• ALTER TABLE name RENAME TO new_name;: Renames a table.
• TRUNCATE table_name;: Quickly removes all rows from a table without individual row deletions.
• CREATE INDEX name ON table (column);: Builds a B-tree index (default) to speed up data retrieval.
• CREATE INDEX CONCURRENTLY ...;: Builds an index without blocking concurrent INSERT, UPDATE, or DELETE operations.
• DROP TABLE name;: Deletes an existing table.

3. Data Manipulation (DML)

• INSERT INTO table (cols) VALUES (vals);: Populates a table with new rows.
• UPDATE table SET col = val WHERE condition;: Modifies existing data.
• DELETE FROM table WHERE condition;: Removes specific rows.
• SELECT * FROM table ORDER BY col LIMIT n;: Retrieves and sorts a specific number of rows.
• COPY table FROM 'file';: Performs high-speed bulk loading of data from a file.
• MERGE INTO target USING source ...;: Performs conditional inserts, updates, or deletes in a single statement.

4. User, Role, and Security Management

• CREATE ROLE name WITH LOGIN PASSWORD 'pwd';: Creates a role capable of logging into the database (effectively a user).
• GRANT privilege ON object TO role;: Assigns specific permissions (e.g., SELECT, UPDATE) to a role.
• REVOKE privilege ON object FROM role;: Removes previously granted permissions.
• ALTER TABLE name ENABLE ROW LEVEL SECURITY;: Activates row-level security (RLS) to restrict data access based on the current user.
• CREATE POLICY name ON table FOR SELECT USING (...);: Defines granular rules for which rows a user can see or modify.

5. Transaction and Concurrency Control

• BEGIN;: Starts a new transaction block.
• COMMIT;: Saves all changes made during the current transaction.
• ROLLBACK;: Discards all changes since the start of the transaction.
• SAVEPOINT name;: Sets a marker within a transaction for partial rollbacks [157, conversation history].
• SET TRANSACTION ISOLATION LEVEL ...;: Sets the isolation level for the current transaction (e.g., SERIALIZABLE).
• LOCK TABLE name IN mode;: Manually acquires a table-level lock to manage concurrency.

6. Common psql Meta-Commands

• \l: Lists all databases in the cluster.
• \dt: Lists all tables in the current schema [psql help, 107].
• \d table_name: Shows detailed information about a specific table’s structure.
• \du: Lists all database roles and their attributes.
• \dp: Displays access privileges (ACLs) and RLS policies for tables.
• \password role_name: Safely changes a role’s password.
• \q: Exits the psql terminal.

7. System Monitoring and Maintenance

• EXPLAIN ANALYZE query;: Shows the execution plan and actual timing/row counts for a query.
• VACUUM ANALYZE table;: Reclaims space from dead rows and updates statistics for the query planner.
• SELECT pg_reload_conf();: Forces the server to reload configuration files like postgresql.conf.
• SELECT pg_terminate_backend(pid);: Forcefully closes a specific database connection.
• SELECT pg_log_backend_memory_contexts(pid);: Dumps detailed memory usage information for a specific process into the server logs [939, conversation history].
• SELECT * FROM pg_stat_activity;: Provides real-time information on currently running queries and their states.

8. Replication and Backup Utilities

• CREATE PUBLICATION name FOR TABLE table;: Sets up the publisher side of logical replication.
• CREATE SUBSCRIPTION name CONNECTION '...' PUBLICATION pub;: Sets up the subscriber side of logical replication.
• pg_dump dbname > file.sql: A client utility to export a database into a script file.
• pg_basebackup -D directory: Creates a physical “base backup” of the entire database cluster for replication or recovery.