PostgreSQL Administration: High Availability and Disaster Recovery Guide

Master PostgreSQL database administration, backup strategies, and high availability configurations. Learn advanced PostgreSQL features, replication setup, and disaster recovery planning for enterprise environments.

PostgreSQL Administration Fundamentals

PostgreSQL is a powerful, enterprise-class relational database system with advanced features for high availability, data integrity, and scalability.

1. PostgreSQL Installation and Configuration

Installation on Ubuntu

# Install PostgreSQL 15
sudo apt update
sudo apt install postgresql-15 postgresql-client-15 -y

# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Set password for postgres user
sudo -u postgres psql -c "ALTER USER postgres PASSWORD "secure_password";"

# Create application database and user
sudo -u postgres createdb webapp_db
sudo -u postgres psql -c "CREATE USER webapp_user WITH ENCRYPTED PASSWORD "webapp_password";"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE webapp_db TO webapp_user;"

2. Performance Configuration

# /etc/postgresql/15/main/postgresql.conf
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB
max_connections = 200
log_min_duration_statement = 1000

3. Backup Strategies

#!/bin/bash
# Automated backup script
DB_NAME="webapp_db"
BACKUP_DIR="/var/backups/postgresql"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

mkdir -p "$BACKUP_DIR"

pg_dump -h localhost -U webapp_user 
        --format=custom --compress=9 
        --file="$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.backup" 
        "$DB_NAME"

# Cleanup old backups
find "$BACKUP_DIR" -name "*.backup" -mtime +30 -delete

4. High Availability Setup

Master Configuration

# postgresql.conf additions
wal_level = replica
max_wal_senders = 3
synchronous_commit = on

# Create replication user
sudo -u postgres psql -c "CREATE USER replicator WITH REPLICATION;"

Standby Setup

# Create base backup
sudo -u postgres pg_basebackup -h master_host -D /var/lib/postgresql/15/main 
    -U replicator -R -S standby1

# Configure standby
echo "hot_standby = on" >> postgresql.conf
sudo systemctl start postgresql

5. Monitoring and Maintenance

# Monitor connections
SELECT count(*) FROM pg_stat_activity;

# Check replication lag
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));

# Find slow queries
SELECT query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

6. Performance Optimization

# Create indexes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
CREATE INDEX CONCURRENTLY idx_orders_date ON orders (created_at);

# Analyze tables
ANALYZE users;
ANALYZE orders;

# Vacuum maintenance
VACUUM ANALYZE;

Conclusion

PostgreSQL administration requires careful configuration, backup strategies, and monitoring. Implementing proper high availability and maintenance procedures ensures reliable database operations.