🚀 Production PostgreSQL

-- Check the current WAL level
SHOW wal_level;
-- Default: replica (supports streaming replication)
-- Options: minimal, replica, logical

-- See where WAL files are stored
SHOW data_directory;
-- WAL files live in: {data_directory}/pg_wal/

-- Current WAL position (useful for replication monitoring)
SELECT pg_current_wal_lsn();
-- Result: 0/16B3748  (Log Sequence Number)

-- How much WAL has been generated?
SELECT pg_size_pretty(pg_wal_lsn_diff(
    pg_current_wal_lsn(),
    '0/0'
)) AS total_wal_generated;

-- A checkpoint flushes all dirty data from RAM to disk
-- After a checkpoint, all WAL before that point can be recycled

SHOW checkpoint_timeout;
-- Default: 5min (checkpoint at least every 5 minutes)

SHOW max_wal_size;
-- Default: 1GB (force checkpoint if WAL exceeds this)

-- Force a manual checkpoint (rarely needed)
CHECKPOINT;

# Backup a single database to a SQL file
pg_dump -U postgres -d mystore > mystore_backup.sql

# Backup in custom format (compressed, supports selective restore)
pg_dump -U postgres -d mystore -Fc -f mystore_backup.dump

# Backup only specific tables
pg_dump -U postgres -d mystore -t orders -t products -Fc -f orders_products.dump

# Backup schema only (no data — just table definitions)
pg_dump -U postgres -d mystore --schema-only -f mystore_schema.sql

# Backup data only (no table definitions)
pg_dump -U postgres -d mystore --data-only -f mystore_data.sql

# Restore a plain SQL backup
psql -U postgres -d mystore_new < mystore_backup.sql

# Restore a custom-format backup
pg_restore -U postgres -d mystore_new mystore_backup.dump

# Restore only specific tables from a custom backup
pg_restore -U postgres -d mystore_new -t orders mystore_backup.dump

# Restore with --clean (drops objects before recreating)
pg_restore -U postgres -d mystore_new --clean mystore_backup.dump

# List contents of a backup without restoring
pg_restore -l mystore_backup.dump

# Backup ALL databases, roles, and tablespaces
pg_dumpall -U postgres > full_cluster_backup.sql

# Backup only roles (users & permissions)
pg_dumpall -U postgres --roles-only > roles_backup.sql

# Physical backup: copies the entire data directory
# This is the foundation for PITR and setting up replicas
pg_basebackup -U replicator -h localhost -D /backups/base_backup -Fp -Xs -P

# Flags explained:
# -D : destination directory
# -Fp : plain format (raw files)
# -Xs : stream WAL during backup
# -P  : show progress

#!/bin/bash
# Daily backup script — add to crontab
# crontab: 0 2 * * * /scripts/daily_backup.sh

BACKUP_DIR="/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mystore"
RETENTION_DAYS=7

# Create backup
pg_dump -U postgres -d $DB_NAME -Fc -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# Delete backups older than retention period
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete

# Log the result
echo "$(date): Backup completed — ${DB_NAME}_${DATE}.dump" >> /var/log/pg_backup.log

-- On the PRIMARY: check how far behind each replica is
SELECT client_addr,
       state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag,
       NOW() - backend_start AS connection_duration
FROM pg_stat_replication;

-- On the REPLICA: check lag from replica's perspective
SELECT NOW() - pg_last_xact_replay_timestamp() AS replication_lag;

-- On the PRIMARY: create a publication
CREATE PUBLICATION orders_pub FOR TABLE orders, products;

-- On the SUBSCRIBER (another PostgreSQL instance):
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=primary-server dbname=mystore user=replicator password=...'
    PUBLICATION orders_pub;

-- Create a role (user) with a password and login ability
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

-- Create a read-only role (no login — it's a group)
CREATE ROLE readonly_group;

-- Create a read-write role (group)
CREATE ROLE readwrite_group;

-- Create an admin role with CREATEDB ability
CREATE ROLE db_admin WITH LOGIN PASSWORD 'admin_pass' CREATEDB;

-- Grant read-only access to the readonly_group
GRANT CONNECT ON DATABASE mystore TO readonly_group;
GRANT USAGE ON SCHEMA public TO readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

-- Automatically grant SELECT on future tables too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_group;

-- Grant read-write access
GRANT CONNECT ON DATABASE mystore TO readwrite_group;
GRANT USAGE ON SCHEMA public TO readwrite_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_group;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite_group;

-- Add a user to a group
GRANT readonly_group TO app_user;

-- Revoke a permission
REVOKE DELETE ON orders FROM readwrite_group;

-- RLS lets you control which ROWS a user can see, not just which tables

-- Enable RLS on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own orders
CREATE POLICY user_sees_own_orders ON orders
    FOR SELECT
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

-- Policy: users can only insert orders for themselves
CREATE POLICY user_inserts_own_orders ON orders
    FOR INSERT
    WITH CHECK (user_id = current_setting('app.current_user_id')::INTEGER);

-- Admin policy: admins can see everything
CREATE POLICY admin_full_access ON orders
    FOR ALL
    TO db_admin
    USING (TRUE);

# pg_hba.conf controls WHO can connect and HOW they authenticate
# TYPE    DATABASE    USER          ADDRESS           METHOD

# Local connections (Unix socket) — use peer auth
local   all         postgres                          peer

# Local network — password authentication
host    all         all           127.0.0.1/32        scram-sha-256
host    all         all           ::1/128             scram-sha-256

# Application servers on the internal network
host    mystore     app_user      192.168.1.0/24      scram-sha-256

# Replication connections
host    replication replicator    192.168.1.0/24      scram-sha-256

# DANGER: never do this in production!
# host  all         all           0.0.0.0/0           trust

# postgresql.conf tuning for a 32GB RAM, SSD server

# Memory
shared_buffers = '8GB'              # 25% of 32GB
work_mem = '128MB'                  # per sort/hash operation
maintenance_work_mem = '1GB'        # for VACUUM and CREATE INDEX
effective_cache_size = '24GB'       # 75% of 32GB

# Connections
max_connections = 200               # use connection pooling!

# WAL
wal_buffers = '64MB'                # WAL buffer size
min_wal_size = '1GB'
max_wal_size = '4GB'
checkpoint_completion_target = 0.9  # spread checkpoint over 90% of interval

# Planner
random_page_cost = 1.1              # SSD (default 4.0 is for spinning disks)
effective_io_concurrency = 200      # SSD can handle parallel IO

# Logging
log_min_duration_statement = 1000   # log queries slower than 1 second
log_line_prefix = '%t [%p] %u@%d ' # timestamp, pid, user, database

-- Enable the extension (add to postgresql.conf first):
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries (by total time)
SELECT
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(rows::numeric / NULLIF(calls, 0), 0) AS avg_rows,
    LEFT(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top 10 most frequently called queries
SELECT
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    LEFT(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Reset statistics (do this periodically)
SELECT pg_stat_statements_reset();

-- The problem: each PostgreSQL connection uses ~5-10MB of RAM
-- 200 connections × 10MB = 2GB just for connections!

-- The solution: PgBouncer sits between your app and PostgreSQL
-- Your app opens 200 connections to PgBouncer
-- PgBouncer uses only 20 connections to PostgreSQL
-- Connections are reused and shared — like a carpool!

# pgbouncer.ini (simplified)
[databases]
mystore = host=127.0.0.1 port=5432 dbname=mystore

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
pool_mode = transaction     # release connection after each transaction
default_pool_size = 20      # 20 actual PostgreSQL connections
max_client_conn = 500       # 500 app connections allowed

-- See all active connections and what they're doing
SELECT
    pid,
    usename AS user,
    datname AS database,
    state,
    NOW() - query_start AS duration,
    wait_event_type,
    LEFT(query, 60) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Queries running for more than 5 minutes
SELECT
    pid,
    usename,
    NOW() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes'
ORDER BY query_start;

-- Kill a specific runaway query (use with caution!)
SELECT pg_cancel_backend(12345);    -- graceful: cancel the query
SELECT pg_terminate_backend(12345); -- forceful: terminate the connection

-- Table health overview: reads, writes, dead rows, vacuums
SELECT
    relname AS table_name,
    seq_scan AS full_scans,
    idx_scan AS index_scans,
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_del AS deletes,
    n_dead_tup AS dead_rows,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Find blocked queries (waiting for locks)
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    LEFT(blocked.query, 50) AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    LEFT(blocking.query, 50) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks lk ON lk.locktype = bl.locktype
    AND lk.database = bl.database
    AND lk.relation = bl.relation
    AND lk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = lk.pid
WHERE bl.granted = FALSE;

-- Total database size
SELECT pg_size_pretty(pg_database_size('mystore')) AS db_size;
-- Result: 2.4 GB

-- Size of each table (including indexes and toast)
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Unused indexes (candidates for removal)
SELECT
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;