Managing Databases

Managing Databases

Purpose

Operate and maintain production databases with reliability and performance:

• Implement backup and disaster recovery strategies
• Configure monitoring and alerting
• Manage replication and high availability
• Perform routine maintenance operations
• Troubleshoot performance issues

Quick Start

# PostgreSQL backup
pg_dump -Fc -d mydb > backup_$(date +%Y%m%d).dump

# Restore
pg_restore -d mydb backup_20241230.dump

# Check database health
psql -c "SELECT pg_database_size('mydb');"
psql -c "SELECT * FROM pg_stat_activity;"

Features

Common Patterns

Backup Strategies

# Full backup with compression
pg_dump -Fc -Z9 -d production > backup_$(date +%Y%m%d_%H%M%S).dump

# Parallel backup for large databases
pg_dump -Fc -j 4 -d production > backup.dump

# Base backup for PITR (Point-in-Time Recovery)
pg_basebackup -D /backups/base -Fp -Xs -P -R

# Continuous WAL archiving (postgresql.conf)
archive_mode = on
archive_command = 'cp %p /archive/%f'

# Restore to specific point in time
recovery_target_time = '2024-12-30 14:30:00'

-- Verify backup integrity
SELECT pg_is_in_recovery();
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

Monitoring Queries

-- Active connections and queries
SELECT pid, usename, application_name, state, query,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Table sizes and bloat
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
       pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;  -- Unused indexes at top

-- Lock monitoring
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Replication Setup

-- On primary: Create replication user
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret';

-- pg_hba.conf on primary
host replication replicator replica_ip/32 scram-sha-256

# On replica: Initialize from primary
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/data -Fp -Xs -P -R

# Verify replication status (on primary)
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

# Check replication lag (on replica)
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Connection Pooling (PgBouncer)

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction  # transaction, session, statement
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

Maintenance Operations

-- Manual VACUUM and ANALYZE
VACUUM ANALYZE orders;

-- Aggressive vacuum for bloat
VACUUM FULL orders;  -- Locks table, use pg_repack instead

-- Reindex without locking (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- Tune autovacuum per table (high-churn tables)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

-- Check autovacuum status
SELECT schemaname, relname, last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

# pg_repack: Online VACUUM FULL alternative
pg_repack -d mydb -t orders

Security Hardening

-- Create role with minimal privileges
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Read-only user for reporting
CREATE ROLE readonly WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Revoke public access
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

# pg_hba.conf - Secure access rules
# TYPE  DATABASE  USER       ADDRESS         METHOD
local   all       postgres                   peer
host    mydb      app_user   10.0.0.0/8      scram-sha-256
hostssl mydb      app_user   0.0.0.0/0       scram-sha-256

Use Cases

• Setting up production database infrastructure
• Troubleshooting slow queries and locks
• Implementing disaster recovery plans
• Scaling with read replicas
• Security audits and compliance

Best Practices

Daily Health Check

-- Run this checklist daily
-- 1. Database size and growth
SELECT pg_size_pretty(pg_database_size('mydb'));

-- 2. Connection count
SELECT count(*) FROM pg_stat_activity;

-- 3. Long-running queries (>5 min)
SELECT * FROM pg_stat_activity
WHERE state != 'idle' AND query_start < now() - interval '5 minutes';

-- 4. Replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

-- 5. Bloat check (dead tuples)
SELECT relname, n_dead_tup FROM pg_stat_user_tables
WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;

-- 6. Failed/pending transactions
SELECT * FROM pg_prepared_xacts;

Emergency Procedures

-- Kill long-running query
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE query_start < now() - interval '30 minutes' AND state != 'idle';

-- Cancel query without killing connection
SELECT pg_cancel_backend(pid);

-- Emergency: Kill all connections to database
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'mydb' AND pid != pg_backend_pid();

Related Skills

See also these related skill documents:

• optimizing-databases - Query and index optimization
• managing-database-migrations - Safe schema changes
• designing-database-schemas - Schema architecture

Converted and distributed by TomeVault — claim your Tome and manage your conversions.