Migration Strategies
Big Bang (Downtime)
Stop app → backup → migrate → restart। Small DB only।
Zero-Downtime (Expand-Contract) ⭐
1. Expand: Add new column nullable 2. Dual-write old + new 3. Backfill old rows 4. Code read from new 5. Contract: drop old column
ORM Migration
dotnet ef migrations add AddEmail dotnet ef database update php artisan make:migration add_email php artisan migrate knex migrate:make add_email knex migrate:latest
Best Practices
- Reversible (Up/Down)
- Small atomic migrations
- Never delete column immediately
- ONLINE/CONCURRENTLY for large index
- Test on staging first
Backup Types
| Type | বর্ণনা |
|---|---|
| Full | সব data |
| Incremental | last full/inc থেকে |
| Differential | last full থেকে |
| PITR | Point-In-Time (WAL/binlog) |
3-2-1 Rule
3 copies, 2 different media, 1 off-site।
RPO & RTO
- RPO — কত data loss acceptable
- RTO — কত time-এ recover
Backup Commands
mysqldump -u root -p shop_db > backup.sql pg_dump shop_db > backup.sql BACKUP DATABASE Shop TO DISK = 'C:\shop.bak'; # Test restore! Untested = no backup
Row-Level Security
-- PG
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_iso ON orders
USING (tenant_id = current_setting('app.tenant_id')::int);
Encryption
- At Rest — TDE
- In Transit — TLS/SSL
- Application — column encrypt, key vault
Sensitive Data
- Password — bcrypt/Argon2
- Credit card — PCI-DSS, tokenize
- PII — encrypt, audit
- GDPR — right to erasure
Access Control
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secret'; GRANT SELECT, INSERT ON shop.* TO 'app_user'@'%'; -- Principle of least privilege