SQL Academy · Lezione

Backup logici e fisici

pg_dump e backup di base

Backup logici e fisici è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è un backup del database

Un backup è una copia dei dati del database che può essere utilizzata per ripristinare il sistema dopo una perdita o una corruzione dei dati oppure in seguito a un disastro. Senza backup affidabili, un singolo guasto hardware o un DELETE accidentale può distruggere definitivamente mesi o anni di dati.

PostgreSQL offre due grandi categorie di strategie di backup: backup logici e backup fisici. Ognuna presenta caratteristiche, casi d'uso e compromessi distinti che ogni DBA deve comprendere.

I backup logici

Un backup logico esporta il database sotto forma di istruzioni SQL leggibili — CREATE TABLE, INSERT, COPY e comandi simili. In PostgreSQL, lo strumento più comune per questa operazione è pg_dump.

Poiché l'output è costituito da semplice SQL, un backup logico è portabile: è possibile ripristinarlo su una versione diversa di PostgreSQL, su un sistema operativo diverso o persino ripristinare selettivamente singole tabelle o schemi. Il compromesso è che il dump e il ripristino di database di grandi dimensioni possono essere lenti.

Utilizzare pg_dump per un backup logico

L'utilità pg_dump viene eseguita dalla riga di comando, non all'interno di SQL. Si connette a un server PostgreSQL in esecuzione ed esporta il database scelto. È possibile produrre SQL semplice, un formato compresso personalizzato oppure un formato directory.

Il codice SQL riportato di seguito simula ciò che acquisisce un backup logico: la struttura e i dati di una tabella sotto forma di istruzioni riproducibili.

-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)

CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    customer  TEXT        NOT NULL,
    amount    NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (customer, amount, created_at) VALUES
    ('Alice',  149.99, '2024-01-15 09:30:00+00'),
    ('Bob',     89.50, '2024-01-16 14:00:00+00'),
    ('Carol',  210.00, '2024-01-17 11:15:00+00');

Formati di output di pg_dump

pg_dump supporta quattro formati di output, ciascuno adatto a diversi flussi di lavoro per il ripristino:

plain — uno script SQL semplice, leggibile in qualsiasi editor di testo.
custom — un formato binario compresso, il più flessibile, che supporta il ripristino in parallelo.
directory — un file per ogni tabella, che supporta dump e ripristino in parallelo.
tar — un archivio tar del formato directory.

Il formato custom è consigliato per i database di grandi dimensioni, perché pg_restore può ripristinare gli oggetti in parallelo utilizzando -j N processi di lavoro.

-- Checking which databases exist before choosing what to back up
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datname NOT IN ('template0', 'template1')
ORDER  BY pg_database_size(datname) DESC;

Ripristinare un backup logico

Un backup logico in SQL semplice viene ripristinato con psql. Un backup in formato custom richiede pg_restore. Entrambi gli strumenti rieseguono le istruzioni SQL per ricreare tabelle, indici, vincoli e dati.

Poiché i backup logici contengono SQL, è possibile modificarli prima del ripristino, ad esempio per ripristinare una sola tabella o cambiare il nome di uno schema. Questa flessibilità è uno dei principali vantaggi dell'approccio logico.

-- After restoring a backup, verify row counts match expectations
SELECT
    schemaname,
    relname           AS table_name,
    n_live_tup        AS estimated_rows
FROM  pg_stat_user_tables
ORDER BY n_live_tup DESC;

I backup fisici

Un backup fisico (chiamato anche backup di base) copia i file di dati grezzi che PostgreSQL utilizza sul disco: le pagine, i segmenti WAL (Write-Ahead Log) e i file di configurazione. Il risultato è uno snapshot binario dell'intero cluster in un determinato momento.

I backup fisici sono in genere molto più veloci da ripristinare per i database di grandi dimensioni, perché non è necessario rieseguire SQL: PostgreSQL si limita a ricollocare i file e a rieseguire il WAL per raggiungere uno stato coerente.

pg_basebackup: creare un backup fisico

pg_basebackup è lo strumento PostgreSQL standard per i backup fisici. Trasmette in streaming la directory dei dati da un server primario in esecuzione tramite una connessione di replica. È necessario un utente con privilegi di replica e wal_level impostato su replica o su un valore superiore.

All'interno del database è possibile interrogare le impostazioni di replica per confermare che il server sia configurato correttamente prima di tentare un backup di base.

-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
    'wal_level',
    'max_wal_senders',
    'archive_mode',
    'archive_command'
)
ORDER  BY name;

Archiviazione WAL e recupero a un punto nel tempo

Un backup di base fotografa un determinato momento. Per eseguire il recupero a un punto qualsiasi successivo a quel backup, PostgreSQL riesegue i segmenti WAL archiviati: questa procedura è chiamata Point-in-Time Recovery (PITR).

Quando archive_mode = on e archive_command sono configurati, PostgreSQL copia i segmenti WAL completati in una posizione di archivio. Durante il recupero, restore_command recupera quei segmenti affinché il server possa rieseguirli fino al momento obiettivo desiderato.

-- Inspect current WAL position and archive status
SELECT
    pg_current_wal_lsn()                        AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn())        AS current_wal_file,
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time
FROM  pg_stat_archiver;

Confrontare i backup logici e fisici

La scelta tra backup logici e fisici dipende dai requisiti:

Logico (pg_dump): portabile tra versioni, supporta il ripristino parziale ed è leggibile, ma è lento per i database di grandi dimensioni e non offre granularità a livello di sottotransazione.
Fisico (pg_basebackup + WAL): ripristino rapido per i cluster di grandi dimensioni, supporta PITR, è specifico per versione (deve essere ripristinato nella stessa versione principale) e ripristina l'intero cluster: non è possibile ripristinare una singola tabella.

Negli ambienti di produzione vengono in genere utilizzati entrambi: backup di base fisici ogni notte con archiviazione continua del WAL, oltre a dump logici periodici per garantire portabilità e ripristini mirati.

Verificare l'integrità dei backup

Un backup mai testato non è un backup: è solo una speranza. Convalidi sempre i backup ripristinandoli in un ambiente di test e verificando i dati.

Per i backup logici, un controllo rapido dell'integrità consiste nel contare le righe e confrontare i checksum. Per i backup fisici, PostgreSQL 14+ ha introdotto pg_verifybackup, che verifica il file manifest scritto da pg_basebackup.

-- After a test restore, compare row counts across critical tables
SELECT
    relname                              AS table_name,
    n_live_tup                           AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM  pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC
LIMIT  20;

Monitoraggio e pianificazione dei backup

Automatizzare e monitorare i backup è importante quanto eseguirli. È necessario tenere traccia di quando sono stati eseguiti l'ultima volta, della loro durata e del loro esito. PostgreSQL espone metadati utili a questo scopo.

Per i backup fisici, pg_stat_archiver mostra l'ultima archiviazione completata con successo e gli eventuali errori. Per i backup logici, racchiuda pg_dump in uno script che registri l'ora di inizio, l'ora di fine, la dimensione del file e il codice di uscita in una tabella di monitoraggio o in un sistema di avvisi.

-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
    id          SERIAL PRIMARY KEY,
    backup_type TEXT        NOT NULL CHECK (backup_type IN ('logical', 'physical')),
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    size_bytes  BIGINT,
    status      TEXT        NOT NULL DEFAULT 'running',
    notes       TEXT
);

-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;

Logico o fisico: verifica rapida

Verifichi la propria comprensione delle strategie di backup logico e fisico in PostgreSQL.

Riepilogo della lezione: backup logici e fisici

In questa lezione ha esplorato due strategie fondamentali di backup in PostgreSQL:

I backup logici utilizzano pg_dump per esportare i database sotto forma di istruzioni SQL. Sono portabili, leggibili e supportano i ripristini parziali, ma possono essere lenti per i database di dimensioni molto grandi.
I backup fisici utilizzano pg_basebackup per copiare i file di dati grezzi. In combinazione con l'archiviazione WAL, consentono ripristini rapidi e il Point-in-Time Recovery, ma sono specifici per versione e ripristinano sempre l'intero cluster.
I sistemi di produzione combinano in genere entrambe le strategie: backup di base fisici con archiviazione WAL per un recupero rapido e granulare, oltre a dump logici periodici per la portabilità.
È sempre necessario testare i ripristini. Un backup non testato non può essere considerato affidabile in una situazione di reale emergenza.

Comprendere questi due approcci è essenziale per progettare un piano di disaster recovery solido per qualsiasi distribuzione PostgreSQL.

Domande Frequenti

La lezione «Backup logici e fisici» è gratuita?

Sì — il testo completo di «Backup logici e fisici» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Backup logici e fisici»?

pg_dump e backup di base Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «Backup logici e fisici»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy