PostgreSQL, MySQL & SQLper Svogliati
Non sei un DBA e non vuoi diventarlo, ma qualcuno deve pur far girare quel database. PostgreSQL e MySQL/MariaDB spiegati insieme, con l'SQL che serve davvero per leggerlo, scriverci e non romperlo.
"Ho fatto UPDATE senza WHERE" — la seconda frase più temuta dopo "ho fatto mkfs sul disco sbagliato", e stavolta la rete di salvataggio si chiama backup, non lsblk.
Cos'è davvero un RDBMS
Perché non basta un mucchio di file CSV, e cosa vuol dire davvero quella sigla, ACID, che tutti citano senza spiegare.
Un file CSV è uno scaffale: ci metti roba, la rileggi, sperando che nessun altro la tocchi mentre lo fai. Un RDBMS (Relational Database Management System) è il registro di una banca: quando sposti 100€ da un conto all'altro, o la banca registra entrambi i movimenti o nessuno dei due — non può esistere un istante in cui i soldi sono spariti da un conto e non ancora arrivati sull'altro, nemmeno se in quell'istante salta la corrente. Questa garanzia si chiama transazione, ed è la ragione d'essere di un database relazionale.
🔒 ACID: le quattro promesse
| Lettera | Significa | In pratica |
|---|---|---|
| Atomicity | Atomicità | Una transazione con più operazioni va a buon fine tutta o fallisce tutta, mai a metà |
| Consistency | Coerenza | Il database rispetta sempre i vincoli che hai definito (chiavi esterne, univocità, check) prima e dopo ogni transazione |
| Isolation | Isolamento | Due transazioni concorrenti non si vedono a vicenda gli stati intermedi, come se girassero una alla volta |
| Durability | Durabilità | Una volta confermata (COMMIT), la modifica sopravvive anche a un crash immediato dopo |
📊 Tabelle, righe, colonne
Il modello relazionale organizza i dati in tabelle (entità, es. clienti), dove ogni riga è un record e ogni colonna un attributo con un tipo definito (testo, numero, data...). Le relazioni tra tabelle (un cliente ha molti ordini) si esprimono con chiavi, non duplicando i dati — è il cuore di tutto il capitolo 4 e del JOIN (cap. 6).
⚖️ SQL vs NoSQL, in una frase
NoSQL (MongoDB, Redis, Cassandra...) sacrifica parte delle garanzie relazionali per scalare orizzontalmente o per schemi molto flessibili. Non è "meglio" o "peggio": se i tuoi dati hanno relazioni chiare e serve integrità forte (soldi, inventario, prenotazioni), un RDBMS resta la scelta di default e questa guida parla solo di quello.
PostgreSQL vs MySQL/MariaDB
La domanda sbagliata è "qual è il migliore". Quella giusta è "qual è il migliore per QUESTO lavoro".
🐬 PostgreSQL
Nato a Berkeley come "Postgres" (erede di Ingres), poi "Postgres95" con supporto SQL, infine PostgreSQL dal 1996. Sviluppato da una community indipendente, mai controllato da una singola azienda. Un unico motore di storage, ma esteso quasi all'infinito: JSON nativo, ricerca full-text, PostGIS per dati geografici, foreign data wrapper per interrogare altre fonti come se fossero tabelle locali.
🐬 MySQL / MariaDB
MySQL nasce nel 1995 (Widenius & Axmark), pensato da subito per essere veloce e facile da installare: è diventato lo standard de facto del web (la "M" di LAMP). Dopo l'acquisizione di Sun/MySQL da parte di Oracle (2010), gli autori originali hanno creato MariaDB come fork completamente libero, oggi il default su Debian/RHEL al posto di MySQL. Architettura a motori di storage intercambiabili: InnoDB (transazionale, il default oggi) o il vecchio MyISAM (senza transazioni, quasi mai la scelta giusta ormai).
| Aspetto | PostgreSQL | MySQL / MariaDB |
|---|---|---|
| Licenza | PostgreSQL License (permissiva, stile BSD) | GPL (MySQL, + dual license commerciale Oracle) / GPL puro (MariaDB) |
| Motore di storage | Uno solo, molto curato (heap + MVCC) | Intercambiabile: InnoDB (default), MyISAM (legacy) |
| JSON | JSONB nativo, indicizzabile, query ricche | JSON supportato (5.7+/MariaDB), meno maturo |
| Window function & CTE | Supporto completo da anni | Solo da MySQL 8.0 / MariaDB 10.2+ |
| Full-text search | Integrato, potente | Presente ma più limitato |
| Estensioni | PostGIS, pgvector, decine di altre | Ecosistema più chiuso |
| Reputazione | "Il più corretto e ricco di feature" | "Il più semplice e diffuso nel web hosting" |
| Chi lo richiede | Analytics, GIS, dati complessi, backend custom | WordPress, Drupal e gran parte del software web "pronto" |
Installazione & anatomia
Stessi concetti, nomi di file diversi. Una volta imparata la mappa, ti muovi in entrambi i mondi senza cercare ogni volta su Google.
| Voce | PostgreSQL | MySQL / MariaDB |
|---|---|---|
| Pacchetto (Debian) | postgresql | mariadb-server |
| Porta di default | 5432 | 3306 |
| Client a riga di comando | psql | mysql |
| Servizio systemd | postgresql | mariadb / mysql |
| Data directory | /var/lib/postgresql/<ver>/main | /var/lib/mysql |
| Config principale | postgresql.conf | /etc/mysql/mariadb.conf.d/*.cnf |
| Config accessi | pg_hba.conf | dentro lo stesso .cnf / grant utente |
| Utente amministrativo OS | postgres | root di MySQL (utente DB, non OS) |
🐬 Connettersi a PostgreSQL
# Postgres usa l'utente OS "postgres" per l'accesso amministrativo iniziale
sudo -u postgres psql
# Dentro psql: elenca database, cambia database, esci
\l
\c nomedb
\dt # elenca le tabelle
\q
🐬 Connettersi a MySQL/MariaDB
sudo mysql # root via socket, nessuna password richiesta
mysql -u utente -p # login con utente/password
# Dentro il client: elenca database, cambia database, esci
SHOW DATABASES;
USE nomedb;
SHOW TABLES;
EXIT;
sudo -u postgres psql funziona grazie al metodo peer in pg_hba.conf: il sistema fida dell'utente OS locale. Su MySQL, il pacchetto Debian/Ubuntu configura root@localhost con lo stesso meccanismo via socket auth_socket/unix_socket. Nessuno dei due è "meno sicuro": entrambi richiedono comunque accesso come utente privilegiato della macchina.
Il modello relazionale: schema & tipi di dato
Prima di scrivere una sola query, serve una tabella. E prima di creare una tabella, serve capire come i dati si collegano tra loro.
📋 Chiavi: primaria ed esterna
Ogni tabella ha (quasi sempre) una chiave primaria (primary key): una colonna, o combinazione di colonne, che identifica univocamente ogni riga. Una chiave esterna (foreign key) in un'altra tabella punta a quella chiave primaria, creando la relazione: la colonna cliente_id nella tabella ordini punta a id nella tabella clienti. Il database impone questo vincolo: non puoi creare un ordine per un cliente che non esiste.
CREATE TABLE clienti (
id SERIAL PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
creato_il TIMESTAMP DEFAULT NOW()
);
CREATE TABLE ordini (
id SERIAL PRIMARY KEY,
cliente_id INTEGER REFERENCES clienti(id),
totale NUMERIC(10,2) NOT NULL CHECK (totale >= 0),
creato_il TIMESTAMP DEFAULT NOW()
);
CREATE TABLE clienti (
id INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
creato_il TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE ordini (
id INT AUTO_INCREMENT PRIMARY KEY,
cliente_id INT,
totale DECIMAL(10,2) NOT NULL CHECK (totale >= 0),
creato_il TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (cliente_id) REFERENCES clienti(id)
) ENGINE=InnoDB;
| Concetto | PostgreSQL | MySQL / MariaDB |
|---|---|---|
| Auto-incremento | SERIAL / GENERATED ALWAYS AS IDENTITY | AUTO_INCREMENT |
| Testo lungo | TEXT (nessun limite pratico) | TEXT / VARCHAR (limiti diversi per storage) |
| Booleano | BOOLEAN vero e proprio | TINYINT(1) per convenzione |
| JSON | JSONB (binario, indicizzabile) | JSON |
| Data/ora | TIMESTAMP, TIMESTAMPTZ | DATETIME, TIMESTAMP |
clienti, non cercare tutte le righe di ordini che lo ripetevano. Le "forme normali" (1NF, 2NF, 3NF) sono solo un modo formale di dire questo: ogni fatto, in un unico posto.
SQL: leggere i dati con SELECT
Il comando che userai il 90% delle volte. Buone notizie: la sintassi base è identica su tutti e due i motori.
🔍 La struttura di base
-- Tutte le colonne, tutte le righe (evitalo su tabelle grandi)
SELECT * FROM clienti;
-- Solo le colonne che ti servono
SELECT nome, email FROM clienti;
-- Filtra le righe
SELECT * FROM ordini WHERE totale > 100;
SELECT * FROM clienti WHERE email = 'mario@esempio.it';
-- Ordina e limita i risultati
SELECT * FROM ordini ORDER BY totale DESC LIMIT 10;
-- Valori distinti
SELECT DISTINCT cliente_id FROM ordini;
🔎 Pattern matching: LIKE
SELECT * FROM clienti WHERE nome LIKE 'Mar%'; -- inizia con "Mar"
SELECT * FROM clienti WHERE email LIKE '%@gmail.com';
Su PostgreSQL LIKE è case-sensitive di default; esiste ILIKE per ignorare maiuscole/minuscole. Su MySQL/MariaDB dipende dalla collation della colonna (spesso case-insensitive di default con le collation *_ci).
📌 LIMIT / OFFSET: la paginazione
SELECT * FROM ordini
ORDER BY id
LIMIT 20 OFFSET 40; -- "pagina 3" di 20 risultati
SELECT * su una tabella con milioni di righe senza LIMIT è il modo più comune per bloccare un client (o una connessione web) per minuti. Abitua le dita a scrivere sempre un LIMIT quando stai solo esplorando i dati.
SQL: JOIN, il vero superpotere
Il motivo per cui non duplichi i dati (cap. 4): li ricongiungi al volo, in lettura, con un JOIN.
Immagina l'elenco clienti (id, nome) e l'elenco ordini (id, cliente_id, totale) come due fogli separati. Un JOIN è l'operazione di affiancarli riga per riga dove la chiave combacia: per ogni ordine, trova il cliente con quell'id e mettili sulla stessa riga. Cambia solo cosa fare quando una riga da un lato non ha corrispondenza dall'altro.
| Tipo di JOIN | Restituisce |
|---|---|
| INNER JOIN | Solo le righe che combaciano su entrambi i lati (l'intersezione) |
| LEFT JOIN | Tutte le righe di sinistra, con NULL a destra se non c'è corrispondenza |
| RIGHT JOIN | Speculare al LEFT: tutte le righe di destra |
| FULL OUTER JOIN | Tutte le righe da entrambi i lati, NULL dove manca corrispondenza |
-- Solo clienti che hanno fatto almeno un ordine
SELECT clienti.nome, ordini.totale
FROM clienti
INNER JOIN ordini ON clienti.id = ordini.cliente_id;
-- TUTTI i clienti, anche quelli senza ordini (totale = NULL)
SELECT clienti.nome, ordini.totale
FROM clienti
LEFT JOIN ordini ON clienti.id = ordini.cliente_id;
-- Clienti SENZA nessun ordine (il trucco: LEFT JOIN + WHERE IS NULL)
SELECT clienti.nome
FROM clienti
LEFT JOIN ordini ON clienti.id = ordini.cliente_id
WHERE ordini.id IS NULL;
FULL OUTER JOIN nativamente. MySQL e MariaDB no: va simulato con LEFT JOIN UNION RIGHT JOIN (o UNION ALL + filtro sui duplicati). È una delle differenze più sentite da chi migra query da un motore all'altro.
SQL: aggregazioni & subquery
Da "dammi le righe" a "dammi un numero che le riassume": conteggi, somme, medie, e query dentro altre query.
🔢 GROUP BY e le funzioni di aggregazione
-- Quanti ordini e quanto totale per ogni cliente
SELECT cliente_id, COUNT(*) AS num_ordini, SUM(totale) AS totale_speso
FROM ordini
GROUP BY cliente_id;
-- HAVING filtra DOPO l'aggregazione (WHERE filtra PRIMA)
SELECT cliente_id, SUM(totale) AS totale_speso
FROM ordini
GROUP BY cliente_id
HAVING SUM(totale) > 500;
-- Le altre funzioni classiche
SELECT AVG(totale), MIN(totale), MAX(totale) FROM ordini;
WHERE filtra le righe prima di raggrupparle, HAVING filtra i gruppi dopo l'aggregazione. Non puoi scrivere WHERE SUM(totale) > 500: al momento del WHERE, quella somma non esiste ancora.
📋 Subquery
-- Clienti che hanno speso piu' della media
SELECT nome FROM clienti
WHERE id IN (
SELECT cliente_id FROM ordini
GROUP BY cliente_id
HAVING SUM(totale) > (SELECT AVG(totale) FROM ordini)
);
📋 CTE: le subquery leggibili
WITH totali_cliente AS (
SELECT cliente_id, SUM(totale) AS spesa
FROM ordini GROUP BY cliente_id
)
SELECT clienti.nome, totali_cliente.spesa
FROM clienti JOIN totali_cliente
ON clienti.id = totali_cliente.cliente_id;
Le CTE (Common Table Expression, il blocco WITH) rendono leggibile una query complessa spezzandola in passi con nome. Postgres le supporta da sempre (incluse le CTE ricorsive); MySQL e MariaDB solo dalla versione 8.0/10.2 in poi.
SQL: scrivere dati & transazioni
INSERT, UPDATE, DELETE: le tre operazioni che modificano davvero i dati, e la rete di sicurezza che le rende reversibili finché non confermi.
-- Inserire una riga
INSERT INTO clienti (nome, email) VALUES ('Mario Rossi', 'mario@esempio.it');
-- Aggiornare righe ESISTENTI (la WHERE non e' opzionale, vedi sotto)
UPDATE clienti SET email = 'mario.rossi@esempio.it' WHERE id = 42;
-- Cancellare righe
DELETE FROM ordini WHERE id = 17;
UPDATE clienti SET email = 'x' senza WHERE imposta la STESSA email per ogni cliente esistente, in silenzio, senza conferma. La prassi sana: scrivi sempre prima il SELECT con la stessa WHERE, controlla che le righe siano quelle giuste, poi trasformalo in UPDATE/DELETE.
🔐 Transazioni: il tuo "ctrl+Z" prima del commit
BEGIN;
UPDATE conti SET saldo = saldo - 100 WHERE id = 1;
UPDATE conti SET saldo = saldo + 100 WHERE id = 2;
-- Controlla che sia andato tutto come previsto...
SELECT * FROM conti WHERE id IN (1,2);
-- ...poi conferma per davvero, oppure annulla tutto
COMMIT;
-- ROLLBACK; -- annulla TUTTO quello che hai fatto dentro BEGIN/COMMIT
COMMIT, puoi sempre fare ROLLBACK e tornare indietro come se nulla fosse successo — anche dopo un UPDATE/DELETE sbagliato. Su una modifica delicata, avvolgila sempre in BEGIN/COMMIT e verifica prima di confermare.
RETURNING, che restituisce le righe appena modificate senza una SELECT separata (es. INSERT INTO clienti (...) VALUES (...) RETURNING id;). MySQL/MariaDB non ce l'ha: per recuperare l'ID appena generato si usa LAST_INSERT_ID() subito dopo l'INSERT.
Indici & performance
Perché la stessa query può impiegare 2 millisecondi o 20 secondi, e come scoprire in quale dei due casi sei.
Senza un indice, cercare "Mario Rossi" in una tabella di 10 milioni di clienti significa leggerli tutti, uno per uno (full table scan) — come cercare una parola in un libro di 900 pagine senza indice analitico, sfogliando ogni riga. Un indice (di default una struttura B-tree) è l'equivalente dell'indice del libro: una struttura ordinata separata che dice esattamente dove guardare, senza leggere tutto il resto.
✅ Quando aiutano
- Colonne usate spesso in
WHERE - Colonne usate in
JOIN(le foreign key, quasi sempre) - Colonne usate in
ORDER BYsu tabelle grandi - Le chiavi primarie hanno già un indice automatico, sempre
❌ Il prezzo che pagano
- Ogni
INSERT/UPDATE/DELETEdeve aggiornare anche gli indici — più indici, scritture più lente - Occupano spazio su disco, a volte quanto la tabella stessa
- Un indice su una colonna quasi mai filtrata è puro costo, zero beneficio
CREATE INDEX idx_ordini_cliente ON ordini(cliente_id);
CREATE UNIQUE INDEX idx_clienti_email ON clienti(email);
🔍 EXPLAIN: chiedere al database cosa sta facendo
-- PostgreSQL: ANALYZE esegue davvero la query e misura i tempi reali
EXPLAIN ANALYZE SELECT * FROM ordini WHERE cliente_id = 42;
-- MySQL / MariaDB
EXPLAIN SELECT * FROM ordini WHERE cliente_id = 42;
Seq Scan (Postgres) o type: ALL (MySQL) su una tabella grande, la query sta leggendo tutto: quasi sempre manca un indice sulla colonna filtrata. Se invece vedi Index Scan/ref, l'indice viene usato.
Utenti, permessi & sicurezza
La tua applicazione web non dovrebbe mai connettersi al database come superuser. Mai. Nemmeno "solo per oggi".
🐬 Utenti in PostgreSQL
CREATE ROLE app_utente WITH LOGIN PASSWORD 'segreta';
GRANT CONNECT ON DATABASE negozio TO app_utente;
GRANT SELECT, INSERT, UPDATE ON ordini TO app_utente;
REVOKE DELETE ON ordini FROM app_utente;
🐬 Utenti in MySQL/MariaDB
CREATE USER 'app_utente'@'localhost' IDENTIFIED BY 'segreta';
GRANT SELECT, INSERT, UPDATE ON negozio.ordini TO 'app_utente'@'localhost';
REVOKE DELETE ON negozio.ordini FROM 'app_utente'@'localhost';
FLUSH PRIVILEGES;
'app'@'localhost' e 'app'@'%' (da qualsiasi host) sono utenti diversi con permessi potenzialmente diversi. PostgreSQL invece controlla l'origine della connessione separatamente, nel file pg_hba.conf (che decide anche il metodo di autenticazione: peer, md5, scram-sha-256).
✓ Principio del minimo privilegio
- Un utente dedicato per applicazione, mai condiviso
- Solo i permessi (SELECT/INSERT/UPDATE/DELETE) che servono davvero
- Password lunghe e uniche, gestite da un secret manager, non hardcoded
- Connessioni cifrate (SSL/TLS) se il DB non è sulla stessa macchina dell'app
✗ Errori classici
- App che si connette come
postgres/root"perché funziona subito" - Utente MySQL
'app'@'%'raggiungibile da tutta Internet, porta 3306 aperta - Password nel repository Git, in chiaro, per errore
- Nessuna distinzione tra utente di lettura (reporting) e utente di scrittura
Backup & restore
Il backup che non hai mai testato non è un backup: è una speranza. Qui i comandi per farlo davvero, su entrambi i motori.
💾 PostgreSQL: pg_dump
# Un solo database, formato SQL testuale
pg_dump -U postgres negozio > negozio.sql
# Formato custom (compresso, permette restore selettivo)
pg_dump -U postgres -Fc negozio > negozio.dump
# Ripristino
psql -U postgres negozio < negozio.sql
pg_restore -U postgres -d negozio negozio.dump
# Tutti i database dell'istanza in un colpo
pg_dumpall -U postgres > tutto.sql
💾 MySQL/MariaDB: mysqldump
# Un solo database
mysqldump -u root -p negozio > negozio.sql
# Tutti i database
mysqldump -u root -p --all-databases > tutto.sql
# Ripristino
mysql -u root -p negozio < negozio.sql
📋 Backup logico vs fisico, e il "log" che rende possibile il PITR
Un backup logico (pg_dump/mysqldump) esporta le istruzioni SQL per ricreare i dati: portabile tra versioni, ma più lento su database enormi. Un backup fisico copia i file grezzi del data directory (più veloce, ma legato a versione/architettura). Entrambi i motori tengono anche un log delle modifiche continuo — il WAL (Write-Ahead Log) in PostgreSQL, il binlog in MySQL/MariaDB — che, combinato con un backup fisico, permette il Point-In-Time Recovery: tornare allo stato esatto di, ad esempio, le 14:32 di ieri, non solo all'ultimo backup notturno.
Replica & alta affidabilità
Un solo database è anche un solo punto di guasto. Ecco come si imposta una replica sul serio su entrambi i motori, e come si tiene in salute nel tempo.
🐬 PostgreSQL: streaming replication passo passo
Un nodo primary spedisce in continuo il proprio WAL a uno o più nodi standby, che lo riapplicano restando quasi in tempo reale allineati. Gli standby possono servire query in sola lettura (per scaricare il carico) e, in caso di guasto del primary, uno di loro può essere promosso a nuovo primary.
# postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
# pg_hba.conf: consenti connessioni di replica dallo standby
host replication replicator 10.0.0.20/32 scram-sha-256
# Crea un utente dedicato SOLO per la replica
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'segreta';
# pg_basebackup copia l'intero data directory dal primary, gia' pronto per lo standby
sudo -u postgres pg_basebackup -h 10.0.0.10 -U replicator \
-D /var/lib/postgresql/17/main -Fp -Xs -P -R
# -R genera automaticamente postgresql.auto.conf con primary_conninfo
# e crea il file standby.signal: e' questo file che dice "sono uno standby"
ls /var/lib/postgresql/17/main/standby.signal
sudo systemctl start postgresql
-- Sul primary: chi e' agganciato, e quanto e' indietro
SELECT client_addr, state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- Sullo standby: quanto WAL ha ricevuto vs quanto ha davvero riapplicato
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
SELECT pg_is_in_recovery(); -- true = sono uno standby
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/17/main
-- oppure, da una connessione psql sullo standby stesso:
SELECT pg_promote();
-R in pg_basebackup, o creato a mano con pg_create_physical_replication_slot) dice al primary "non cancellare il WAL finché questo standby non l'ha consumato" — utile se lo standby si scollega per un po'. Il rovescio della medaglia: se lo standby resta spento o irraggiungibile a lungo, il WAL si accumula senza limiti sul primary finché non riempie il disco. Monitora sempre pg_replication_slots e la dimensione di pg_wal/.
🐬 MySQL/MariaDB: replica basata su GTID
Storicamente la replica si basava su posizioni nel binlog (file + offset), fragili da sincronizzare a mano dopo un failover. Gli identificatori GTID (Global Transaction Identifier) risolvono il problema: ogni transazione ha un ID globale univoco, e un replica può agganciarsi a qualunque source dicendo semplicemente "dammi tutto quello che non ho ancora visto".
# my.cnf / mariadb.conf.d/*.cnf
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_strict_mode = ON # gtid_mode=ON,ENFORCE_GTID_CONSISTENCY=ON su MySQL 8
# Utente dedicato SOLO per la replica
CREATE USER 'repl'@'%' IDENTIFIED BY 'segreta';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# server-id DIVERSO su ogni nodo, e' l'errore di setup piu' comune
server-id = 2
# Clona i dati di partenza: mysqldump per DB piccoli, Percona XtraBackup per DB grandi (hot, senza lock lunghi)
mysqldump -u root -p --all-databases --source-data=2 --single-transaction > seed.sql
mysql -u root -p < seed.sql
# Aggancia il replica al source (sintassi moderna MySQL 8.0.23+, con GTID auto-posizionato)
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.10', SOURCE_USER='repl', SOURCE_PASSWORD='segreta',
SOURCE_AUTO_POSITION=1;
START REPLICA;
# MariaDB: stessa idea, sintassi CHANGE MASTER TO / START SLAVE, con MASTER_USE_GTID=slave_pos
SHOW REPLICA STATUS\G -- MySQL 8 (SHOW SLAVE STATUS su MariaDB / MySQL piu' vecchi)
-- Le righe che contano davvero:
-- Replica_IO_Running: Yes -> sta ricevendo il binlog dal source
-- Replica_SQL_Running: Yes -> sta riapplicando le transazioni
-- Seconds_Behind_Source: 0 -> e' allineato (NULL o alto = problema)
Replica_SQL_Running diventa No (tipicamente un duplicate key o un vincolo violato perché qualcuno ha scritto per errore sul replica), la riparazione "veloce" con GTID è iniettare una transazione vuota con lo stesso GTID per saltare quella specifica (SET GTID_NEXT='...'; BEGIN; COMMIT;) — ma prima capisci perché i dati divergono, o la prossima volta si romperà di nuovo. Se il replica è troppo indietro o incoerente, spesso conviene ripartire da un seed fresco piuttosto che rincorrere il problema.
🔄 Manutenzione continua
- Allarme automatico se il lag (
replay_lag/Seconds_Behind_Source) supera una soglia, non scoprirlo a occhio - NTP sincronizzato su tutti i nodi: senza orologi allineati, i numeri di lag mentono
- Verifica periodica che le repliche siano davvero leggibili e coerenti (una query di conteggio periodica confrontata col primary)
- Switchover pianificato (promozione controllata, tutti sincronizzati) è sempre preferibile a un failover di emergenza (nodo sparito, dati potenzialmente non ancora replicati)
🧠 Oltre il fai-da-te
Gestire manualmente promozione, riconfigurazione degli altri nodi e redirezione dell'applicazione dopo un failover è lento ed error-prone. Strumenti come Patroni (PostgreSQL, con etcd/Consul per il consenso) o Orchestrator (MySQL/MariaDB) automatizzano rilevamento del guasto e promozione; un connection pooler davanti (PgBouncer, ProxySQL) evita di dover riconfigurare ogni applicazione client con il nuovo indirizzo.
Cheat Sheet & Glossario
Tutto quello che serve, su una pagina. Bookmark questa sezione e dimentica il resto.
🐬 PostgreSQL essenziale
sudo -u postgres psql
\l \c db \dt \d tabella \du
pg_dump -U postgres db > db.sql
pg_restore -d db db.dump
EXPLAIN ANALYZE SELECT ...;
🐬 MySQL/MariaDB essenziale
sudo mysql
SHOW DATABASES; USE db; SHOW TABLES;
mysqldump -u root -p db > db.sql
mysql -u root -p db < db.sql
EXPLAIN SELECT ...;
🔍 SQL essenziale
SELECT col FROM t WHERE cond ORDER BY col LIMIT n;
SELECT ... FROM a JOIN b ON a.id = b.a_id;
SELECT col, COUNT(*) FROM t GROUP BY col HAVING ...;
INSERT INTO t (col) VALUES (val);
UPDATE t SET col = val WHERE cond;
DELETE FROM t WHERE cond;
🔐 Transazioni & permessi
BEGIN; ... COMMIT; / ROLLBACK;
GRANT SELECT, INSERT ON t TO utente;
REVOKE DELETE ON t FROM utente;
CREATE INDEX idx_x ON t(colonna);
✓ Regole d'oro
SELECTcon la stessaWHEREprima di ogniUPDATE/DELETE- Transazioni (
BEGIN/COMMIT) per modifiche multi-step o delicate - Un utente per applicazione, permessi minimi indispensabili
- Indici sulle colonne di JOIN e sulle WHERE più frequenti
EXPLAINprima di ottimizzare "a sentimento"- Backup testato con restore reale, non solo eseguito
✗ Errori classici
UPDATE/DELETEsenzaWHERESELECT *senzaLIMITsu tabelle enormi- App connessa come superuser (
postgres/root) - Indice su ogni colonna "non si sa mai" (scritture rallentate per nulla)
- Confondere replica con backup
- Password del database in chiaro nel codice sorgente
📚 Glossario essenziale
- RDBMS — sistema di gestione di database relazionali
- ACID — le quattro garanzie di una transazione affidabile
- Chiave primaria / esterna — identificano e collegano righe tra tabelle
- JOIN — ricongiunge righe di tabelle diverse in lettura
- GROUP BY / HAVING — raggruppano righe e filtrano i gruppi
- Transazione — blocco di operazioni atomiche tra
BEGINeCOMMIT - Indice / B-tree — struttura che accelera le ricerche a scapito delle scritture
- EXPLAIN — mostra il piano di esecuzione scelto dal motore
- InnoDB — motore di storage transazionale di MySQL/MariaDB (il default)
- MVCC — Multi-Version Concurrency Control, come Postgres isola le transazioni senza bloccare tutto
- WAL / binlog — log delle modifiche usato per replica e PITR
- pg_dump / mysqldump — strumenti di backup logico dei due motori
- Replica — copia continuamente aggiornata dei dati su un altro nodo
- PITR — Point-In-Time Recovery, ripristino a un istante preciso
- Normalizzazione — organizzare i dati per evitare duplicazioni
- CTE — Common Table Expression, il blocco
WITH - Ruolo / grant — identità e permessi assegnati a un utente del database
📚 Risorse
- postgresql.org/docs — documentazione ufficiale PostgreSQL, ottima
- mariadb.com/kb — MariaDB Knowledge Base
- dev.mysql.com/doc — documentazione ufficiale MySQL
- use-the-index-luke.com — il riferimento classico sugli indici SQL
- sqlzoo.net / pgexercises.com — esercizi pratici per allenarsi
🔗 Guide collegate
- WordPress e Drupal — i CMS che girano proprio su questi database
- Alta Affidabilità — quorum, fencing e failover, oltre la sola replica del database
- Redis — la cache in RAM da affiancare al database, non da sostituirlo
- Docker — far girare Postgres/MySQL in container, con volumi persistenti
- Linux Admin — systemd, dischi e il resto dell'infrastruttura sotto al database
- Gestione Dischi — dove e come vivono davvero i file del data directory
- Ansible — automatizzare installazione e provisioning dei database