Postgres cheat sheet

-- Basic connection
psql -U username -d database_name

-- Full connection with all parameters
psql -h localhost -p 5432 -U postgres -d mydb

-- Connection URL format
psql "postgresql://username:password@localhost:5432/dbname"

-- Connect to specific database from within psql
\c database_name

-- Create new database
CREATE DATABASE sales_db;

-- Create database with specific encoding and owner
CREATE DATABASE sales_db 
    WITH ENCODING='UTF8' 
    OWNER=sales_admin;

-- Drop database (be careful!)
DROP DATABASE IF EXISTS sales_db;

-- Rename database
ALTER DATABASE sales_db RENAME TO new_sales_db;

-- Create table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add column
ALTER TABLE products ADD COLUMN description TEXT;

-- Drop column
ALTER TABLE products DROP COLUMN description;

-- Rename table
ALTER TABLE products RENAME TO items;

-- Add index
CREATE INDEX idx_products_name ON products(name);

-- Insert data
INSERT INTO products (name, price) VALUES ('Laptop', 999.99);

-- Bulk insert
INSERT INTO products (name, price) VALUES 
    ('Mouse', 24.99),
    ('Keyboard', 59.99),
    ('Monitor', 299.99);

-- Update data
UPDATE products SET price = 899.99 WHERE name = 'Laptop';

-- Delete data
DELETE FROM products WHERE price < 50;

-- Truncate table (remove all data)
TRUNCATE TABLE products;

-- List all tables
\dt

-- Describe table structure
\d products

-- List all schemas
\dn

-- List all users
\du

-- Show table size
SELECT pg_size_pretty(pg_total_relation_size('products'));

-- Show running queries
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';

-- Kill query by PID
SELECT pg_cancel_backend(pid);

# Create backup
pg_dump dbname > backup.sql

# Create compressed backup
pg_dump -Fc dbname > backup.dump

# Backup specific tables
pg_dump -t table1 -t table2 dbname > tables_backup.sql

# Restore from backup
psql dbname < backup.sql

# Restore compressed backup
pg_restore -d dbname backup.dump

-- Create user
CREATE USER john WITH PASSWORD 'secure_password123';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE sales_db TO john;
GRANT SELECT, INSERT ON products TO john;

-- Create role with login
CREATE ROLE sales_admin WITH LOGIN PASSWORD 'admin123';

-- Add user to role
GRANT sales_admin TO john;

-- Revoke privileges
REVOKE ALL PRIVILEGES ON products FROM john;

-- Check table sizes
SELECT 
    relname as table_name,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Find unused indexes
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_catalog.pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname NOT IN ('pg_catalog', 'pg_toast');

-- Check cache hit ratio
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

-- Analyze table statistics
ANALYZE products;

-- Vacuum table (recover space and update statistics)
VACUUM (VERBOSE, ANALYZE) products;

-- Reindex table
REINDEX TABLE products;

-- View locks
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.usename AS blocked_user,
       blocking_activity.usename AS blocking_user
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Get all tables and their sizes
SELECT
    table_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) as total_size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;

-- Find blocking queries
SELECT pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Check index usage
SELECT 
    t.schemaname,
    t.tablename,
    c.reltuples::bigint AS rows,
    pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_tables t ON t.tablename = c.relname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(c.oid) DESC;