Corso per DBA PostgreSQL

Lezione 1 • Avvio, arresto e ricarica del server

Tratta pg_ctl, la gestione dei servizi systemd e il controllo basato su segnali. Gli studenti si esercitano nelle operazioni sicure del ciclo di vita del server, utilizzate in ogni flusso di lavoro del DBA.

Lezione 2 • Concetti fondamentali dei database relazionali

Tratta i principi degli RDBMS, le proprietà ACID e come PostgreSQL si inserisce nel modello relazionale. Fornisce le basi concettuali per tutti gli argomenti di amministrazione successivi.

Lezione 3 • Layout delle directory e file chiave

Illustra la struttura della directory dei dati, i file di configurazione e le posizioni dei log. La familiarità con il layout dei file è essenziale per le attività di configurazione e ripristino.

Lezione 4 • Architettura dei processi e della memoria di PostgreSQL

Esamina postmaster, processi backend, buffer condivisi e WAL. La comprensione di questi meccanismi interni guida le decisioni di ottimizzazione e risoluzione dei problemi durante l'intero corso.

Lezione 1 • Database, schemi e tablespace

Spiega la gerarchia degli spazi dei nomi a tre livelli e la mappatura dello storage dei tablespace. Una corretta progettazione degli spazi dei nomi previene i conflitti tra oggetti e supporta la gestione dello storage.

Lezione 2 • Sequence e colonne identity

Spiega gli oggetti sequence e la sintassi GENERATED AS IDENTITY per le chiavi surrogate. Gli studenti configurano correttamente le colonne ad incremento automatico per gli schemi di produzione.

Lezione 3 • Viste, viste materializzate e funzioni

Introduce viste in sola lettura e aggiornabili, aggiornamento di viste materializzate e funzioni PL/pgSQL di base. Questi oggetti incapsulano la logica e semplificano le interfacce applicative.

Lezione 4 • Tipi di indici e loro utilizzo

Esamina gli indici B-tree, Hash, GIN, GiST e BRIN e i loro casi d'uso appropriati. La scelta dell'indice influisce direttamente sulle prestazioni delle query, trattate nei capitoli successivi.

Lezione 1 • Gestione dei privilegi a livello di oggetto

Spiega GRANT e REVOKE su tabelle, schemi, sequence e funzioni. Gli studenti applicano il principio del privilegio minimo su un database realistico con più schemi.

Lezione 2 • Ruoli e attributi dei ruoli

Tratta CREATE ROLE, LOGIN, SUPERUSER e gli attributi CREATEDB. La progettazione dei ruoli è il fondamento di ogni decisione su privilegi e autenticazione in PostgreSQL.

Lezione 3 • Criteri di sicurezza a livello di riga

Introduce la creazione di criteri RLS, le clausole USING e WITH CHECK e il test dei criteri. RLS impone l'isolamento dei dati senza richiedere logica di filtraggio a livello applicazione.

Lezione 4 • Auditing e hardening della sicurezza

Tratta l'estensione pgaudit, il logging delle connessioni e i parametri di hardening. Gli studenti realizzano una configurazione pronta per l'audit, allineata agli standard di sicurezza comuni.

Lezione 1 • Parametri di costo del query planner

Spiega seq_page_cost, random_page_cost, cpu_tuple_cost e gli obiettivi statistici. Parametri di costo accurati consentono al planner di scegliere piani di esecuzione ottimali.

Lezione 2 • Ottimizzazione di WAL e checkpoint

Tratta wal_buffers, checkpoint_completion_target, max_wal_size e le impostazioni fsync. L'ottimizzazione del WAL bilancia il throughput di scrittura con le garanzie di durabilità per il ripristino in caso di crash.

Lezione 3 • Parametri di configurazione della memoria

Spiega shared_buffers, work_mem, maintenance_work_mem ed effective_cache_size. Una corretta allocazione della memoria è la leva di ottimizzazione di maggior impatto per la maggior parte dei carichi di lavoro.

Lezione 4 • Ottimizzazione a livello di sistema operativo per PostgreSQL

Affronta i parametri del kernel, la selezione dello scheduler I/O, NUMA e le transparent huge pages. La configurazione del sistema operativo spesso limita i miglioramenti ottenibili solo tramite i parametri di PostgreSQL.

Lezione 1 • VACUUM e VACUUM FULL

Tratta la sintassi e gli effetti di VACUUM manuale, VACUUM FULL e VACUUM ANALYZE. Gli studenti distinguono quando ciascuna forma è appropriata senza causare blocchi non necessari.

Lezione 2 • MVCC e meccanica del bloat delle tabelle

Spiega come MVCC crea tuple morte e bloat delle pagine nel tempo. La comprensione di MVCC è un prerequisito per progettare strategie efficaci di VACUUM e autovacuum.

Lezione 3 • Prevenzione del wraparound dell'ID di transazione

Tratta il monitoraggio dell'età dell'XID, autovacuum_freeze_max_age e il VACUUM di emergenza. Il fallimento da wraparound causa l'indisponibilità totale del database e deve essere attivamente prevenuto.

Lezione 4 • Manutenzione degli indici e REINDEX

Tratta il rilevamento del bloat degli indici, REINDEX CONCURRENTLY e pg_stat_user_indexes. La manutenzione regolare degli indici preserva le prestazioni delle query senza richiedere la riscrittura delle tabelle.

Lezione 1 • Backup logico con pg_dump

Tratta i formati di pg_dump, pg_dumpall, i dump paralleli e il ripristino selettivo con pg_restore. I backup logici consentono il ripristino a livello di oggetto e la migrazione tra versioni.

Lezione 2 • Backup fisico con pg_basebackup

Spiega i backup di base in streaming, le modalità di checkpoint e le opzioni di inclusione WAL. I backup fisici sono il fondamento del PITR e del provisioning del server standby.

Lezione 3 • Procedura di Point-in-Time Recovery

Guida il ripristino dal backup di base, i parametri recovery.conf o postgresql.conf e la specifica dell'obiettivo. Gli studenti eseguono e verificano uno scenario PITR completo dall'inizio alla fine.

Lezione 4 • Configurazione dell'archiviazione WAL

Tratta la configurazione di archive_mode, archive_command e archive_cleanup_command. L'archiviazione WAL continua consente il ripristino fino a qualsiasi punto successivo al backup di base.

Lezione 1 • Failover e alta disponibilità basata su Patroni

Tratta la promozione manuale, pg_ctl promote e l'alta disponibilità automatizzata con Patroni ed etcd. Gli studenti simulano un guasto del primario e verificano che il failover automatico si completi correttamente.

Lezione 2 • Fondamenti della replica logica

Spiega pubblicazioni, sottoscrizioni e filtraggio delle righe per la replica selettiva. La replica logica supporta aggiornamenti tra versioni e la distribuzione parziale dei dati.

Lezione 3 • Configurazione della replica in streaming

Tratta gli slot di replica, le voci di replica in pg_hba.conf e primary_conninfo. Gli studenti costruiscono uno standby in streaming funzionante da un server primario attivo.

Lezione 4 • Monitoraggio del lag di replica

Spiega pg_stat_replication, pg_stat_wal_receiver e il calcolo del lag basato su LSN. Il monitoraggio del lag è essenziale per la conformità SLA e il processo decisionale di failover.

Lezione 1 • Statistiche e accuratezza del planner

Tratta pg_statistic, ANALYZE, le statistiche estese e l'interpretazione degli istogrammi. Statistiche obsolete o insufficienti portano il planner a scegliere piani di esecuzione subottimali.

Lezione 2 • Strategie di join e suggerimenti per i piani

Spiega i criteri di selezione e le tecniche di forzatura per nested loop, hash join e merge join. Disallineamenti nella strategia di join sono una causa principale di gravi regressioni delle prestazioni delle query.

Lezione 3 • Identificazione delle query lente con pg_stat_statements

Tratta l'installazione di pg_stat_statements, le colonne chiave e la normalizzazione delle query. I dati aggregati del carico di lavoro rivelano le query di maggior impatto a cui dare priorità nell'ottimizzazione.

Lezione 4 • Lettura di EXPLAIN e EXPLAIN ANALYZE

Decodifica i nodi del piano, le stime di costo, le righe effettive e l'output temporale di EXPLAIN. La lettura accurata del piano è la competenza prerequisita per ogni tecnica di ottimizzazione delle query.