Come eseguire il backup e il ripristino dei database utilizzando pg_dump e pg_restore

Come Eseguire il Backup e il Ripristino di Database Usando pg_dump e pg_restore

Nel mondo della gestione dei database, strategie robuste di backup e ripristino non sono solo raccomandate; sono assolutamente critiche. La perdita di dati può derivare da varie fonti, inclusi guasti hardware, errori umani o attacchi malevoli. PostgreSQL, un potente database relazionale open-source, fornisce utility da riga di comando essenziali, pg_dump e pg_restore, per garantire l'integrità e la recuperabilità dei tuoi dati. Questo tutorial ti guiderà attraverso le migliori pratiche per utilizzare questi strumenti al fine di creare backup affidabili ed eseguire ripristini sicuri dei tuoi database PostgreSQL.

Comprendere queste utility è fondamentale per qualsiasi amministratore o sviluppatore PostgreSQL responsabile della protezione dei dati. pg_dump viene utilizzato per estrarre un database PostgreSQL in un file script o altro file archivio, mentre pg_restore può interpretare tali file per ricostruire il database. Padroneggiarli ti consente di salvaguardare le tue preziose informazioni e mantenere la continuità aziendale.

Comprendere pg_dump

pg_dump è un'utility per creare backup di un database PostgreSQL. Genera un file contenente comandi SQL che possono essere utilizzati per ricreare gli oggetti del database (tabelle, funzioni, indici, ecc.) e popolarli con i dati. pg_dump funziona connettendosi al database e quindi generando l'output. Può generare output in diversi formati, ognuno con i propri vantaggi.

Formati di Output

pg_dump supporta più formati di output:

Script SQL in testo semplice (-Fp o predefinito): Questo è il formato più semplice. Genera un file contenente una serie di comandi SQL. Questo formato è leggibile dall'uomo e facilmente scriptabile, ma può essere grande e più lento da ripristinare per database molto grandi.
Archivio Personalizzato (-Fc): Questo formato crea un file archivio personalizzato compresso. È generalmente il formato raccomandato per i backup in quanto è compresso per impostazione predefinita, supporta il ripristino parallelo (pg_restore) ed è generalmente più flessibile.
Formato Directory (-Fd): Questo crea una directory contenente più file, uno per ogni tabella e altri oggetti. Supporta anche il ripristino parallelo ed è utile per database molto grandi.
Formato Tar (-Ft): Questo crea un archivio tar. È simile al formato directory ma è un singolo file. È utile per la compatibilità con altri strumenti che gestiscono archivi tar.

Opzioni Comuni di pg_dump

Ecco alcune delle opzioni più frequentemente utilizzate per pg_dump:

-h <hostname>: Specifica il nome host della macchina su cui è in esecuzione il server.
-p <port>: Specifica la porta TCP su cui il server è in ascolto per le connessioni.
-U <username>: Connettiti come l'utente specificato.
-W: Forza pg_dump a richiedere una password prima di connettersi.
-f <filename>: Specifica il nome del file di output. Se omesso, pg_dump scrive sull'output standard.
-F <format>: Specifica il formato del file di output (p, c, d, t).
-Z <0-9>: Specifica il livello di compressione per i formati compressi (es. Fc).
--exclude-table=<table_name>: Esclude la tabella specificata dal dump.
--jobs=<number>: Numero di job paralleli da utilizzare durante il dump (solo per il formato directory).
--clean: Includi comandi per eliminare gli oggetti del database prima di crearli. Questo è utile per ripristini completi per garantire uno stato pulito.
--create: Includi un comando per creare il database stesso. Utile quando si ripristina su un nuovo server.

Esempio: Backup Base in Testo Semplice

Per creare un backup in testo semplice di un database chiamato mydatabase di proprietà dell'utente myuser e salvarlo in mydatabase_backup.sql:

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

Se hai bisogno di specificare un host e una porta:

pg_dump -h localhost -p 5432 -U myuser -f mydatabase_backup.sql mydatabase

Questo comando richiederà la password per myuser.

Esempio: Backup in Formato Personalizzato (Consigliato)

L'utilizzo del formato archivio personalizzato è generalmente preferito per la sua flessibilità e compressione. Per creare un backup personalizzato compresso di mydatabase:

pg_dump -U myuser -Fc -f mydatabase_backup.dump mydatabase

Questo creerà un file chiamato mydatabase_backup.dump che è compresso e in un formato adatto per pg_restore.

Esempio: Backup in Formato Directory con Job Paralleli

Per database molto grandi, il dump parallelo può accelerare significativamente il processo di backup. Questo richiede il formato directory.

pg_dump -U myuser -Fd -j 4 -f mydatabase_backup_dir mydatabase

Questo comando creerà una directory chiamata mydatabase_backup_dir contenente più file, utilizzando 4 job paralleli.

Comprendere pg_restore

pg_restore è un'utility per ripristinare un database PostgreSQL da un archivio creato da pg_dump in uno dei formati personalizzato, directory o tar. Non viene utilizzato per i dump SQL in testo semplice (che vengono ripristinati usando il comando psql).

Opzioni Comuni di pg_restore

-h <hostname>: Specifica il nome host della macchina su cui è in esecuzione il server.
-p <port>: Specifica la porta TCP su cui il server è in ascolto per le connessioni.
-U <username>: Connettiti come l'utente specificato.
-W: Forza pg_restore a richiedere una password prima di connettersi.
-d <database>: Specifica il nome del database su cui ripristinare. Questo è obbligatorio.
-v: Modalità verbose.
-c o --clean: Pulisci (elimina) gli oggetti del database prima di ricrearli.
--create: Crea il database prima di ripristinarvi i dati. Richiede la connessione a un database diverso inizialmente (es. postgres).
-j <number>: Numero di job paralleli da utilizzare per il ripristino. Questo accelera significativamente il ripristino, specialmente per i formati personalizzati e directory.
-F <format>: Specifica il formato dell'archivio (c, d, t). Di solito, pg_restore può rilevarlo automaticamente.

Ripristino da Backup SQL in Testo Semplice

Se hai creato un backup SQL in testo semplice usando pg_dump (es. mydatabase_backup.sql), lo ripristini usando psql:

psql -U myuser -d mydatabase -f mydatabase_backup.sql

Se il file di backup include i comandi CREATE DATABASE e \connect, potresti dover connetterti a un database diverso (come postgres) e omettere l'argomento -d mydatabase:

psql -U myuser -f mydatabase_backup.sql postgres

Esempio: Ripristino da un Backup in Formato Personalizzato

Per ripristinare il backup in formato personalizzato mydatabase_backup.dump in un database chiamato restored_db:

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Se il database di destinazione restored_db non esiste, puoi usare l'opzione --create. In questo caso, devi connetterti a un database diverso (come postgres) poiché il database di destinazione verrà creato.

pg_restore -U myuser --create -d restored_db mydatabase_backup.dump

Questo comando creerà il database restored_db e quindi ripristinerà i dati al suo interno.

Esempio: Ripristino Parallelo

Per un ripristino più veloce, specialmente con grandi insiemi di dati, usa l'opzione -j:

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup.dump

Questo tenterà di ripristinare il database utilizzando 4 job paralleli.

Esempio: Ripristino da Backup in Formato Directory

Per ripristinare un backup creato in formato directory (es. mydatabase_backup_dir):

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

Nota che l'ultimo argomento è il percorso della directory.

Migliori Pratiche per i Backup

Pianificazione Regolare: Automatizza i tuoi backup usando cron (Linux/macOS) o Task Scheduler (Windows) per eseguirli a intervalli regolari (es. giornaliero, orario).
Scegli il Formato Giusto: Per la maggior parte dei casi d'uso, il formato personalizzato (-Fc) è raccomandato grazie alla sua compressione e compatibilità con le funzionalità parallele di pg_restore. Usa il formato directory (-Fd) per database estremamente grandi dove il dump/ripristino parallelo è cruciale.
Testa i Tuoi Backup: Un backup è utile solo se può essere ripristinato. Testa regolarmente il tuo processo di ripristino ripristinando in un ambiente di staging o di sviluppo per garantire l'integrità dei dati e la validità dei tuoi file di backup.
Archivia i Backup Fuori Sede: Mantieni copie dei tuoi backup in una posizione fisica separata dal tuo server di database primario per proteggerti da disastri a livello di sito.
Monitora i Job di Backup: Implementa il monitoraggio per assicurarti che i tuoi job di backup pianificati vengano eseguiti con successo e per avvisarti in caso di fallimento.
Considera la Compressione: Usa le opzioni di compressione di pg_dump o reindirizza l'output attraverso strumenti di compressione esterni (gzip, zstd) se non stai usando formati che comprimono automaticamente.
Usa --clean e --create con Saggezza: Quando si esegue un ripristino completo in un ambiente esistente, --clean può essere molto utile. Quando ci si sposta in un nuovo ambiente o si vuole garantire uno stato completamente nuovo, --create è essenziale. Sii cauto con queste opzioni sui sistemi di produzione.
Controllo della Versione dei Backup: Implementa una strategia per la gestione della conservazione dei backup, inclusi per quanto tempo i backup vengono mantenuti e come quelli più vecchi vengono eliminati.

Conclusione

pg_dump e pg_restore sono strumenti indispensabili per salvaguardare i tuoi dati PostgreSQL. Comprendendo le loro varie opzioni, formati di output e implementando una strategia di backup e ripristino coerente con test regolari, puoi mitigare significativamente il rischio di perdita di dati e garantire la resilienza dei tuoi sistemi di database. Rendi queste utility un pilastro delle tue pratiche di amministrazione dei database.