SQL Academy · Lezione

Ripristino point-in-time

Ripristini il database a qualsiasi momento usando WAL

Ripristino point-in-time è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 2 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'è il recupero a un punto nel tempo

Il Point-in-Time Recovery (PITR) consente di ripristinare un database a un momento specifico qualsiasi nel passato, non soltanto al momento dell'ultimo snapshot di backup.

Questo è possibile perché PostgreSQL scrive continuamente ogni modifica in un flusso chiamato Write-Ahead Log (WAL). Rieseguendo i record WAL sopra un backup di base, è possibile raggiungere esattamente il punto necessario.

Il Write-Ahead Log (WAL)

Ogni istruzione INSERT, UPDATE, DELETE e DDL viene scritta prima nel WAL, prima di modificare i file di dati effettivi. Questo garantisce la durabilità anche se il server si arresta in modo anomalo durante la scrittura.

Per il PITR, i segmenti WAL archiviati sono i nastri della macchina del tempo. Il backup di base è il punto di partenza; il WAL completa il quadro con tutto ciò che è accaduto in seguito.

-- Check current WAL write location
SELECT pg_current_wal_lsn() AS current_lsn,
       pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

Abilitare l'archiviazione WAL

Prima che sia possibile eseguire il PITR, è necessario attivare l'archiviazione WAL in postgresql.conf. I parametri principali sono wal_level, archive_mode e archive_command.

Il comando di archiviazione copia ogni segmento WAL completato in una posizione sicura, ad esempio una directory locale, un mount NFS o un bucket di storage cloud.

-- View current WAL and archive settings
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
  'wal_level',
  'archive_mode',
  'archive_command',
  'archive_status'
)
ORDER  BY name;

Creare un backup di base

Un backup di base è una copia coerente dell'intera directory dei dati, eseguita mentre il server è in esecuzione. PostgreSQL fornisce l'utilità pg_basebackup e funzioni SQL di basso livello per coordinare questo processo.

Il backup, insieme ai segmenti WAL archiviati a partire dall'inizio del backup, costituisce tutto ciò che serve per il PITR.

-- Check the latest base backup information
SELECT backup_start,
       backup_end,
       pg_size_pretty(backup_total) AS total_size
FROM   pg_stat_basebackup;

Impostare il momento obiettivo del recupero

Quando si verifica un disastro, il primo passo consiste nell'individuare il timestamp esatto a cui si desidera ripristinare il database. Un approccio comune consiste nel trovare il timestamp appena prima dell'evento dannoso, ad esempio un DELETE di massa accidentale.

Le tabelle dei log delle transazioni o i log dell'applicazione contengono spesso questo timestamp. Il WAL stesso registra i timestamp di commit quando track_commit_timestamp è attivo.

-- Find the commit timestamp of recent transactions (requires track_commit_timestamp=on)
SELECT xid,
       pg_xact_commit_timestamp(xid::xid) AS committed_at
FROM   pg_stat_activity
WHERE  state = 'idle'
LIMIT  10;

Parametri di recovery.conf / recovery

Il comportamento del recupero è controllato dai parametri inseriti in postgresql.conf (PostgreSQL 12+) o nel precedente file recovery.conf. I più importanti sono:

restore_command — come recuperare i segmenti WAL archiviati
recovery_target_time — il timestamp esatto a cui fermarsi
recovery_target_action — cosa fare quando viene raggiunto l'obiettivo (promote, pause, shutdown)

-- Inspect recovery settings that are currently active
SELECT name, setting
FROM   pg_settings
WHERE  name IN (
  'restore_command',
  'recovery_target_time',
  'recovery_target_action',
  'recovery_target_inclusive'
)
ORDER  BY name;

Altri tipi di destinazione del ripristino

Il tempo non è l'unico modo per definire una destinazione di ripristino. PostgreSQL supporta quattro tipi di destinazione:

recovery_target_time — una stringa che rappresenta un timestamp
recovery_target_xid — un ID di transazione specifico
recovery_target_lsn — un numero di sequenza del log WAL
recovery_target_name — un punto di ripristino denominato creato con pg_create_restore_point()

-- Create a named restore point before a risky migration
SELECT pg_create_restore_point('before_migration_2024_07_01') AS restore_lsn;

Simulare il disastro

Per esercitarsi con il PITR in un ambiente di test, corrompa o elimini intenzionalmente i dati dopo aver annotato l'ora corrente. In questo modo potrà verificare che il ripristino raggiunga il momento corretto e che le righe eliminate vengano recuperate.

-- Record the 'safe' timestamp, then simulate an accident
SELECT now() AS safe_point;

-- (In a test DB only!) accidentally delete important rows
DELETE FROM orders WHERE created_at < '2024-01-01';

-- Confirm the damage
SELECT COUNT(*) AS remaining_orders FROM orders;

Eseguire il ripristino

Il ripristino prevede tre passaggi:

Arrestare l'istanza PostgreSQL in esecuzione.
Ripristinare il backup di base nella directory dei dati.
Configurare i parametri di ripristino e avviare PostgreSQL in modalità di ripristino — il server riproduce il WAL fino a raggiungere l'ora di destinazione, quindi viene promosso a server normale in lettura e scrittura.

I log del server mostrano ogni file WAL applicato, rendendo semplice monitorare l'avanzamento.

-- After recovery completes, confirm the cluster is no longer in recovery
SELECT pg_is_in_recovery() AS in_recovery,
       pg_last_xact_replay_timestamp() AS last_replayed_txn;

Verificare il ripristino

Quando il server viene promosso, verifichi che i dati si trovino nello stato previsto. Controlli il numero di righe, i timestamp delle righe più recenti e tutti i valori essenziali per l'attività.

È consigliabile eseguire queste query di verifica prima di riaprire il database al traffico dell'applicazione, così da poter far avanzare il ripristino o modificare l'ora di destinazione se necessario.

-- Verify row counts and the latest transaction timestamp after recovery
SELECT 'orders'       AS tbl, COUNT(*) AS rows FROM orders
UNION ALL
SELECT 'order_items'  AS tbl, COUNT(*) AS rows FROM order_items
UNION ALL
SELECT 'customers'    AS tbl, COUNT(*) AS rows FROM customers
ORDER  BY tbl;

Buone pratiche per il PITR

Una configurazione PITR affidabile richiede più della semplice attivazione dell'archiviazione. Tra le pratiche fondamentali:

Testare regolarmente i ripristini — un backup non testato non è un backup.
Monitorare il ritardo dell'archivio WAL — le lacune nel WAL archiviato rendono impossibile un ripristino completo.
Conservare una cronologia WAL sufficiente — mantenga i segmenti almeno fino al backup di base valido più vecchio.
Usare una posizione di archiviazione separata — se il disco primario si guasta, anche il WAL archiviato sullo stesso disco andrà perso.

-- Check for any WAL archiving failures
SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_archived_time,
       last_failed_wal,
       last_failed_time
FROM   pg_stat_archiver;

Verifica rapida

Verifichi la Sua comprensione del ripristino a un punto nel tempo.

Riepilogo: ripristino a un punto nel tempo

In questa lezione ha appreso come il Write-Ahead Log di PostgreSQL consenta il ripristino a qualsiasi momento passato:

Archiviazione del WAL acquisisce ogni modifica successiva a un backup di base.
Le destinazioni di ripristino possono essere un timestamp, un ID di transazione, un LSN o un punto di ripristino denominato.
Il processo di ripristino ripristina il backup di base, riproduce il WAL archiviato e promuove il server quando viene raggiunta la destinazione.
La verifica conferma l'integrità dei dati prima di riaprire il database al traffico.
Le buone pratiche includono test regolari dei ripristini, monitoraggio dell'archivio e archiviazione del WAL fuori sede.

Il PITR è la vostra rete di sicurezza contro gli errori umani e la corruzione dei dati: imparare a usarlo significa padroneggiare il disaster recovery.

Domande Frequenti

La lezione «Ripristino point-in-time» è gratuita?

Sì — il testo completo di «Ripristino point-in-time» è 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 «Ripristino point-in-time»?

Ripristini il database a qualsiasi momento usando WAL 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 2 di 4.

Quanto tempo richiede la lezione «Ripristino point-in-time»?

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