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

Hai bisogno di un backup PostgreSQL che puoi ripristinare, non solo di un file che sembra un backup. pg_dump e pg_restore sono gli strumenti standard per i backup logici: copiano gli oggetti del database e i dati in un dump portatile, quindi li ricostruiscono successivamente sullo stesso server, su un server di test o su una nuova istanza PostgreSQL.

Questa guida si concentra sui backup logici di singoli database. Per il disaster recovery dell'intero cluster, il ripristino point-in-time o installazioni molto grandi, abbina questo a backup fisici e archiviazione WAL.

Comprendere pg_dump

pg_dump si connette a un database ed esporta il suo schema e i dati. Non esegue il dump di ruoli, tablespace o oggetti globali del cluster; usa pg_dumpall --globals-only quando ne hai bisogno.

Formati di output

pg_dump supporta più formati di output:

Testo semplice (-Fp, predefinito): Scrive SQL che ripristini con psql. È facile da ispezionare e modificare, ma i ripristini di grandi dimensioni possono essere più lenti e meno flessibili.
Archivio personalizzato (-Fc): Scrive un archivio compresso per pg_restore. Supporta il ripristino selettivo e il ripristino parallelo.
Formato directory (-Fd): Scrive una directory di file. Supporta il dump parallelo e il ripristino parallelo, il che aiuta per database di grandi dimensioni.
Formato tar (-Ft): Scrive un archivio tar per pg_restore. È un singolo file, ma non supporta la compressione all'interno del formato come fanno gli archivi personalizzati.

Opzioni comuni di pg_dump

Ecco alcune delle opzioni più frequentemente utilizzate per pg_dump:

-h <hostname>: Connetti a un host.
-p <port>: Connetti a una porta.
-U <username>: Connetti come utente.
-W: Forza una richiesta di password prima di connettersi.
-f <filename>: Scrivi su un file o directory invece dell'output standard.
-F <format>: Scegli p, c, d o t.
-Z <level>: Imposta il livello di compressione per i formati che supportano la compressione.
--exclude-table=<pattern>: Escludi tabelle corrispondenti.
-j <number> o --jobs=<number>: Usa lavori paralleli durante il dump in formato directory.
--clean: Includi comandi di drop prima dei comandi di create nell'output SQL semplice.
--create: Includi comandi per creare il database stesso.

Esempio: Backup di 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 devi specificare un host e una porta:

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

A seconda del tuo metodo di autenticazione, potrebbe essere richiesta la password di myuser.

Esempio: Backup in formato personalizzato (consigliato)

Il formato personalizzato è una buona impostazione predefinita per molti backup di produzione perché funziona con pg_restore e supporta il ripristino selettivo.

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

Questo crea mydatabase_backup.dump in un formato adatto per pg_restore.

Esempio: Backup in formato directory con lavori paralleli

Per database di grandi dimensioni, il dump parallelo può ridurre il tempo di backup. 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 lavori paralleli.

Comprendere pg_restore

pg_restore ripristina archivi personalizzati, directory o tar creati da pg_dump. I file SQL semplici vengono ripristinati con psql, non con pg_restore.

Opzioni comuni di pg_restore

-h <hostname>: Connetti a un host.
-p <port>: Connetti a una porta.
-U <username>: Connetti come utente.
-W: Forza una richiesta di password prima di connettersi.
-d <database>: Connetti a questo database per il ripristino.
-v: Stampa output di ripristino dettagliato.
-c o --clean: Elimina gli oggetti del database prima di ricrearli.
--create: Crea il database di cui è stato eseguito il dump prima di ripristinarlo. Connettiti con -d a un database di manutenzione esistente, comunemente postgres.
-j <number>: Usa lavori paralleli per il ripristino dove supportato.
-F <format>: Specifica il formato dell'archivio se il rilevamento automatico non è sufficiente.

Ripristino da backup SQL in testo semplice

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

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

Se il file di backup include comandi CREATE DATABASE e \connect da pg_dump --create, connettiti a un database di manutenzione come postgres:

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 l'archivio è stato creato con pg_dump --create, puoi chiedere a pg_restore di creare il database di cui è stato eseguito il dump. Il valore di -d deve essere un database esistente utilizzato per la connessione iniziale, non il nuovo database che viene creato:

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

Questo crea il database con il nome specificato all'interno del dump e lo ripristina. Se vuoi ripristinare in un database con un nome diverso, crea prima quel database e ripristina senza --create:

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

Esempio: Ripristino parallelo

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

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

Questo ripristina con 4 lavori worker. Il ripristino parallelo è più utile quando il dump ha più oggetti indipendenti da caricare e indicizzare.

Esempio: Ripristino da un backup in formato directory

Per ripristinare un backup creato in formato directory (ad esempio, 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

Pianifica i backup: Usa cron, timer systemd o la tua piattaforma di orchestrazione. Scegli una pianificazione in base alla quantità di dati che puoi permetterti di perdere.
Usa il formato giusto: Il formato personalizzato è una buona impostazione predefinita. Il formato directory è migliore quando hai bisogno di un dump parallelo per un database di grandi dimensioni.
Testa i ripristini: Ripristina su un database di staging secondo una pianificazione. Un backup che non testi mai è solo un'ipotesi.
Cattura gli oggetti globali: Esegui il dump di ruoli e tablespace separatamente con pg_dumpall --globals-only quando ricostruisci un server da zero.
Conserva copie fuori sede: Tieni i backup lontani dal server primario e proteggili con crittografia e controlli di accesso.
Monitora i lavori: Avvisa in caso di dump falliti, file di backup insolitamente piccoli e timestamp di backup vecchi.
Pianifica la conservazione: Decidi quanti backup orari, giornalieri, settimanali e mensili conservare, quindi automatizza la pulizia.
Fai attenzione con --clean: Elimina gli oggetti nel database di destinazione. Usalo solo quando intendi sostituire oggetti esistenti.

Conclusione

Usa pg_dump -Fc per un backup flessibile di un singolo file, pg_dump -Fd -j quando hai bisogno di dump parallelo, psql per ripristini SQL semplici e pg_restore per ripristini di archivi. Quindi dimostra il processo ripristinando su un database di test prima di averne bisogno in un'emergenza.