Database Reliability
Overview
Data is the one thing you cannot redeploy. You prove backups by restoring them, change schemas with reversible migrations, and treat every write or failover as critical.
When to use
- Design HA for a database, configure backups and PITR, run a restore drill.
- Replication lag, connection exhaustion, slow queries, disk full.
- Version upgrades and schema/data migrations.
Engine
infra action=discover provider=aws|gcp|azure|kubernetes and infra action=graph op=impact node=<db> to see every consumer.
infra action=catalog query=<rds|cloudsql|snapshot> and describe for backup/restore operations and their verification.
- Every change:
infra action=check (data operations are critical), infra action=plan with a snapshot step first, infra action=run, infra action=verify; infra action=recover for restores.
Workflow
- State - engine, version, topology, replicas, backup retention, last successful backup, PITR window.
- HA - Multi-AZ / regional instance / Patroni / replica set / Redis Sentinel or cluster / Kafka replication factor 3 and min.insync.replicas 2.
- Backups - automated + logical (pg_dump) where needed, encryption, cross-region copy, retention matching RPO.
- Restore drill - restore to a new instance, run integrity checks, measure RTO, delete the drill instance.
- Migrations - expand/contract pattern, backward-compatible steps, tested on a restored copy, lock-aware (CREATE INDEX CONCURRENTLY).
- Upgrades - snapshot, test on a clone, blue/green (RDS Blue/Green deployments) or replica promotion.
Command families
psql -c 'select version()'; psql -c 'select * from pg_stat_replication'; psql -c 'select * from pg_stat_activity where state <> $$idle$$'
pg_dump -Fc -d <db> -f db.dump; pg_restore -l db.dump; pg_restore -d <newdb> db.dump
mysql -e 'SHOW REPLICA STATUS\G'; mysqldump --single-transaction <db>
redis-cli INFO replication; redis-cli INFO memory; redis-cli --latency
mongosh --eval 'rs.status()'; mongodump --uri <uri>
kafka-topics.sh --bootstrap-server <b> --describe; kafka-consumer-groups.sh --bootstrap-server <b> --describe --all-groups
aws rds describe-db-instances; aws rds describe-db-snapshots --db-instance-identifier <id>
aws rds create-db-snapshot --db-instance-identifier <id> --db-snapshot-identifier <id>-pre-change
aws rds restore-db-instance-to-point-in-time --source-db-instance-identifier <id> --target-db-instance-identifier <id>-drill --restore-time <ts>
gcloud sql instances describe <i>; gcloud sql backups list --instance <i>
az postgres flexible-server show -g <rg> -n <n>
Safety rules
- Writes, DROP/TRUNCATE, failovers, parameter changes needing reboot, restores over an existing DB and version upgrades are critical: user validation.
- Snapshot before any change. Never restore over the source: restore to a new target and switch.
- Never print connection strings with passwords.
Verification and rollback
- Verify: replication healthy, lag near zero, application error rate, row counts/checksums after migrations.
- Rollback: down migration, snapshot/PITR restore to a new instance and endpoint switch, replica promotion.
Deliverable
Topology, backup/PITR status with last tested restore and measured RTO, changes with run ids, verification evidence, rollback.
1---2name: database-reliability3description: Run databases reliably - PostgreSQL, MySQL, Redis, MongoDB, Kafka, Amazon RDS/Aurora, Cloud SQL, Azure Database - HA and failover, backups, PITR, restore drills, replication, performance and safe schema migrations. Use for any database design, incident, backup or migration.4metadata:5 source: https://skillmd.com/skills/navinspire-ia/database-reliability6 navin:7 emoji: 🗄️8 category: devops9---1011# Database Reliability1213## Overview1415Data is the one thing you cannot redeploy. You prove backups by restoring them, change schemas with reversible migrations, and treat every write or failover as critical.1617## When to use1819- Design HA for a database, configure backups and PITR, run a restore drill.20- Replication lag, connection exhaustion, slow queries, disk full.21- Version upgrades and schema/data migrations.2223## Engine24251. `infra action=discover provider=aws|gcp|azure|kubernetes` and `infra action=graph op=impact node=<db>` to see every consumer.262. `infra action=catalog query=<rds|cloudsql|snapshot>` and `describe` for backup/restore operations and their verification.273. Every change: `infra action=check` (data operations are critical), `infra action=plan` with a snapshot step first, `infra action=run`, `infra action=verify`; `infra action=recover` for restores.2829## Workflow30311. **State** - engine, version, topology, replicas, backup retention, last successful backup, PITR window.322. **HA** - Multi-AZ / regional instance / Patroni / replica set / Redis Sentinel or cluster / Kafka replication factor 3 and min.insync.replicas 2.333. **Backups** - automated + logical (pg_dump) where needed, encryption, cross-region copy, retention matching RPO.344. **Restore drill** - restore to a new instance, run integrity checks, measure RTO, delete the drill instance.355. **Migrations** - expand/contract pattern, backward-compatible steps, tested on a restored copy, lock-aware (CREATE INDEX CONCURRENTLY).366. **Upgrades** - snapshot, test on a clone, blue/green (RDS Blue/Green deployments) or replica promotion.3738## Command families3940```bash41psql -c 'select version()'; psql -c 'select * from pg_stat_replication'; psql -c 'select * from pg_stat_activity where state <> $$idle$$'42pg_dump -Fc -d <db> -f db.dump; pg_restore -l db.dump; pg_restore -d <newdb> db.dump43mysql -e 'SHOW REPLICA STATUS\G'; mysqldump --single-transaction <db>44redis-cli INFO replication; redis-cli INFO memory; redis-cli --latency45mongosh --eval 'rs.status()'; mongodump --uri <uri>46kafka-topics.sh --bootstrap-server <b> --describe; kafka-consumer-groups.sh --bootstrap-server <b> --describe --all-groups47aws rds describe-db-instances; aws rds describe-db-snapshots --db-instance-identifier <id>48aws rds create-db-snapshot --db-instance-identifier <id> --db-snapshot-identifier <id>-pre-change49aws rds restore-db-instance-to-point-in-time --source-db-instance-identifier <id> --target-db-instance-identifier <id>-drill --restore-time <ts>50gcloud sql instances describe <i>; gcloud sql backups list --instance <i>51az postgres flexible-server show -g <rg> -n <n>52```5354## Safety rules5556- Writes, DROP/TRUNCATE, failovers, parameter changes needing reboot, restores over an existing DB and version upgrades are critical: user validation.57- Snapshot before any change. Never restore over the source: restore to a new target and switch.58- Never print connection strings with passwords.5960## Verification and rollback6162- Verify: replication healthy, lag near zero, application error rate, row counts/checksums after migrations.63- Rollback: down migration, snapshot/PITR restore to a new instance and endpoint switch, replica promotion.6465## Deliverable6667Topology, backup/PITR status with last tested restore and measured RTO, changes with run ids, verification evidence, rollback.