11. Database Strategy (Detailed)¶

11.1 PostgreSQL Configuration¶

# postgresql.conf highlights
max_connections: 500
shared_buffers: 16GB          # 25% of RAM
effective_cache_size: 48GB    # 75% of RAM
work_mem: 256MB
maintenance_work_mem: 2GB
wal_level: logical            # For CDC via Debezium
max_wal_senders: 10
max_replication_slots: 10
ssl: on
ssl_cert_file: /etc/ssl/certs/pg-server.crt
ssl_key_file: /etc/ssl/private/pg-server.key

# Extensions
- pg_cron            # Scheduled jobs
- pgcrypto           # Encryption functions
- pg_stat_statements # Query analysis
- PostGIS            # Geospatial queries
- Citus              # Horizontal sharding (for citizen table)
- pg_partman         # Table partitioning

11.2 Replication & Backup¶

Strategy	Technology	RPO / RTO
Streaming Replication	PostgreSQL native (sync to standby)	RPO: 0, RTO: <1min
Logical Replication	Debezium → Kafka (CDC)	Near real-time data sync
Point-in-Time Recovery	pgBackRest (to MinIO)	RPO: 5min, RTO: <30min
Cross-site DR	Patroni cluster (Sulaymaniyah primary ↔ Erbil KRDPASS node, expanding to active-active in Phase 4)	Automatic failover
MongoDB Backup	mongodump + Percona Backup	Daily full, hourly incremental
Redis Persistence	AOF + RDB snapshots	RPO: 1sec