Servizi Gestiti (Cloud SQL & Memorystore)

Questa guida spiega come configurare i servizi gestiti Google Cloud per database e caching.

Perché Servizi Gestiti?

[!IMPORTANT] Non gestire database in Nomad/container in produzione a meno che non si abbia un team dedicato DBA. I servizi gestiti eliminano la complessità operativa.

Confronto

Operazione	Self-hosted	Managed
Setup iniziale	Ore/Giorni	Minuti
Backup	Configurazione cron + storage	✅ Automatico
Point-in-time recovery	Complesso	✅ Un click
Failover	Configurare replica + scripts	✅ Automatico
Upgrade versione	Downtime + risk	✅ Zero-downtime
Scaling verticale	Downtime	✅ Near zero-downtime
Monitoring	Setup Prometheus/Grafana	✅ Integrato
Patching sicurezza	Da gestire	✅ Automatico

---

Cloud SQL (PostgreSQL)

Creazione Istanza

# Variabili
PROJECT_ID="visla-gps-prod"
REGION="europe-west1"
INSTANCE_NAME="visla-postgres"

# Crea istanza Cloud SQL
gcloud sql instances create $INSTANCE_NAME \
  --project=$PROJECT_ID \
  --region=$REGION \
  --database-version=POSTGRES_15 \
  --tier=db-custom-2-8192 \
  --storage-type=SSD \
  --storage-size=50GB \
  --storage-auto-increase \
  --backup-start-time=03:00 \
  --enable-point-in-time-recovery \
  --maintenance-window-day=SUN \
  --maintenance-window-hour=04 \
  --availability-type=REGIONAL \
  --enable-google-private-path

Configurazione High Availability

Per produzione, abilitare Regional availability:

gcloud sql instances patch $INSTANCE_NAME \
  --availability-type=REGIONAL

Questo crea automaticamente una replica standby in una zona diversa.

Configurazione Rete (Private IP)

# Abilita Private IP
gcloud sql instances patch $INSTANCE_NAME \
  --network=projects/$PROJECT_ID/global/networks/visla-vpc \
  --no-assign-ip

Creazione Database e Utente

# Crea database
gcloud sql databases create visla_production \
  --instance=$INSTANCE_NAME

# Crea utente applicativo
gcloud sql users create visla_app \
  --instance=$INSTANCE_NAME \
  --password='<GENERA_PASSWORD_SICURA>'

# Crea utente read-only per analytics
gcloud sql users create visla_readonly \
  --instance=$INSTANCE_NAME \
  --password='<GENERA_PASSWORD_SICURA>'

Stringa di Connessione

Per i servizi Nomad, la connection string sarà:

postgresql://visla_app:<password>@<PRIVATE_IP>:5432/visla_production?sslmode=require

Flags Consigliati

gcloud sql instances patch $INSTANCE_NAME --database-flags=\
max_connections=200,\
shared_buffers=2048MB,\
effective_cache_size=6144MB,\
maintenance_work_mem=512MB,\
work_mem=64MB,\
log_min_duration_statement=1000

---

Memorystore (Redis)

Creazione Istanza

# Variabili
REGION="europe-west1"
INSTANCE_NAME="visla-redis"

# Crea istanza Memorystore Redis
gcloud redis instances create $INSTANCE_NAME \
  --project=$PROJECT_ID \
  --region=$REGION \
  --tier=STANDARD_HA \
  --size=2 \
  --redis-version=REDIS_7_0 \
  --network=projects/$PROJECT_ID/global/networks/visla-vpc \
  --connect-mode=PRIVATE_SERVICE_ACCESS

Tier Options

Tier	HA	Use Case	Costo
BASIC	❌	Dev/Test	$
STANDARD_HA	✅	Production	$$

Configurazione Rete

Memorystore richiede Private Service Access:

# Crea range IP per servizi privati
gcloud compute addresses create google-managed-services \
  --global \
  --purpose=VPC_PEERING \
  --prefix-length=16 \
  --network=visla-vpc

# Crea connessione privata
gcloud services vpc-peerings connect \
  --service=servicenetworking.googleapis.com \
  --ranges=google-managed-services \
  --network=visla-vpc

Connessione da Nomad

# Ottieni IP privato
gcloud redis instances describe $INSTANCE_NAME \
  --region=$REGION \
  --format="get(host)"

Connection string per i servizi:

redis://<PRIVATE_IP>:6379

[!NOTE] Memorystore non richiede autenticazione di default quando accessibile solo da VPC privata. Per maggiore sicurezza, abilitare AUTH.

Abilitare AUTH (Opzionale ma Consigliato)

gcloud redis instances update $INSTANCE_NAME \
  --region=$REGION \
  --enable-auth

Poi recupera la password:

gcloud redis instances get-auth-string $INSTANCE_NAME \
  --region=$REGION

---

Migrazione Dati

PostgreSQL: Da VM a Cloud SQL

# 1. Export dal server attuale
pg_dump -h old-server -U postgres -d visla_production \
  -Fc -f backup.dump

# 2. Crea bucket temporaneo
gsutil mb gs://$PROJECT_ID-migration

# 3. Upload backup
gsutil cp backup.dump gs://$PROJECT_ID-migration/

# 4. Import in Cloud SQL
gcloud sql import sql $INSTANCE_NAME \
  gs://$PROJECT_ID-migration/backup.dump \
  --database=visla_production

Redis: Migrazione Dati

Per Redis, generalmente non è necessaria migrazione dati (cache ricostruibile). Se necessario:

# Export da Redis attuale
redis-cli -h old-redis BGSAVE
scp old-redis:/var/lib/redis/dump.rdb .

# Per import, contattare support GCP (non supportato nativamente)

---

Monitoring

Cloud SQL

Metriche disponibili in Cloud Monitoring:

• cloudsql.googleapis.com/database/cpu/utilization
• cloudsql.googleapis.com/database/memory/utilization
• cloudsql.googleapis.com/database/disk/utilization
• cloudsql.googleapis.com/database/postgresql/num_backends
• cloudsql.googleapis.com/database/postgresql/transaction_count

Memorystore

• redis.googleapis.com/stats/cpu_utilization
• redis.googleapis.com/stats/memory/usage_ratio
• redis.googleapis.com/stats/connected_clients
• redis.googleapis.com/stats/commands_processed

Alert Consigliati

# Esempio Cloud Monitoring Alert Policy
displayName: "Cloud SQL High CPU"
conditions:
  - displayName: "CPU > 80%"
    conditionThreshold:
      filter: 'resource.type="cloudsql_database" AND metric.type="cloudsql.googleapis.com/database/cpu/utilization"'
      comparison: COMPARISON_GREATER_THAN
      thresholdValue: 0.8
      duration: 300s

---

Costi Stimati

Servizio	Configurazione	Costo/Mese
Cloud SQL (PostgreSQL)	db-custom-2-8192, Regional HA, 50GB SSD	~$130
Memorystore (Redis)	Standard HA, 2GB	~$90
Totale Data Tier		~$220

[!TIP] Per ambiente di sviluppo, usa tier più bassi:

• Cloud SQL: db-f1-micro (~$10/mese)
• Memorystore: BASIC 1GB (~$25/mese)