Mengelola Database di Production

Database adalah komponen paling kritis — data loss tidak bisa di-undo. Database DevOps memastikan database aman, performant, dan bisa di-recover.

Backup Strategies

# PostgreSQL backup
pg_dump -U postgres myapp > backup_2024-01-15.sql

# Automated backup script
#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
pg_dump -U postgres myapp | gzip > /backups/myapp_$TIMESTAMP.sql.gz

# Hapus backup lebih dari 30 hari
find /backups -name "*.sql.gz" -mtime +30 -delete

# Restore
psql -U postgres myapp < backup.sql

# Point-in-time recovery (PITR) — untuk critical databases
# Butuh WAL archiving enabled

Database Migrations

# Laravel
php artisan make:migration create_orders_table
php artisan migrate

# Knex.js
npx knex migrate:make create_orders
npx knex migrate:latest

# Best practices:
# - Selalu bisa rollback (up & down)
# - Jangan alter column yang sudah berisi data tanpa plan
# - Test migration di staging dulu
# - Backup sebelum migrate di production

Database Monitoring

# Slow query log (PostgreSQL)
ALTER SYSTEM SET log_min_duration_statement = 1000; -- log query > 1 detik

# Connection monitoring
SELECT count(*) FROM pg_stat_activity;

# Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Replication & High Availability

• Primary-Replica — Write ke primary, read dari replica
• Failover — Otomatis promote replica jika primary down
• Managed services — RDS, Cloud SQL handle replication & backup otomatis