Vai al contenuto

Backup PostgreSQL con pg_dump: come salvare e ripristinare

Illustrazione 3D di una sala server con icona di database e tecnici al lavoro.
Immagine generata con IA

Un dump logico è un file da cui PostgreSQL ricostruisce un database nello stato in cui era quando è partito il salvataggio. Questa guida spiega come salvare un singolo database con pg_dump, in formato SQL testo o custom, e come ripristinarlo con psql o pg_restore sullo stesso server o su un altro. Salvo i casi semplici, secondo la documentazione pg_dump generalmente non è lo strumento giusto per i backup periodici dei database di produzione: per quelli vanno valutati gli altri approcci descritti nel capitolo Backup and Restore. Alla fine saprai quale formato scegliere e come ripristinare il dump, in un database nuovo oppure ricreando quello d’origine.

Cosa serve

  • La guida segue la documentazione «current» di PostgreSQL, che dal 19 novembre 2026 corrisponde a PostgreSQL 19. Le stesse pagine indicano come supportate anche le versioni 18, 17, 16 e 15. Gli esempi sono scritti per una shell Unix (Linux e macOS). Per Windows la documentazione consultata dice dove va il file delle password, ma non dove gli installer mettono i programmi.
  • I client pg_dump, pg_restore, psql e createdb devono essere raggiungibili dal terminale. pg_dump è un normale client, quindi puoi lanciarlo anche da un’altra macchina, purché riesca a collegarsi al database.
  • Un utente che possa leggere tutte le tabelle. Secondo la documentazione sul dump SQL, per salvare un database intero serve quasi sempre un superuser. Se non indichi un utente, i client usano lo stesso nome dell’utente del sistema operativo. Per collegarti con un altro utente aggiungi -U seguito dal nome, oppure imposta la variabile PGUSER.
  • Il server si sceglie con -h (host) e -p (porta). Se non li indichi, valgono le variabili PGHOST e PGPORT oppure i valori predefiniti.
  • Versioni compatibili. pg_dump si rifiuta di salvare un server con una versione principale più recente della sua; i server più vecchi invece li salva, fino alla 9.2. Il suo output si carica su server più recenti di pg_dump, mentre su un server con una versione principale più vecchia il caricamento non è garantito.
  • Sul server di destinazione devono già esistere i ruoli che possiedono oggetti del database o hanno permessi su di essi. Come salvarli lo spiega l’ultimo passaggio.

Passare la password senza scriverla nei comandi

Se il server chiede una password, pg_dump e gli altri client la chiedono a ogni avvio, a meno che non la trovino nel file delle password. Secondo la pagina sul file delle password, su Linux e macOS il file si chiama .pgpass e sta nella cartella home dell’utente. Su Windows il percorso è %APPDATA%\postgresql\pgpass.conf. Se vuoi usare un altro file, lo indichi con la variabile PGPASSFILE.

Crea il file con un editor di testo. Ogni connessione occupa una riga, con cinque campi separati da due punti, in quest’ordine:

  1. il nome dell’host del server;
  2. la porta;
  3. il nome del database;
  4. il nome dell’utente;
  5. la password.

Nei primi quattro campi un asterisco va bene per qualsiasi valore. Vale la prima riga che corrisponde alla connessione, quindi le righe più specifiche vanno in alto. Se un valore contiene i due punti o una barra rovesciata, mettigli davanti una barra rovesciata. Le righe che cominciano con # sono commenti.

Il comando seguente cambia solo i permessi di .pgpass nella tua home: dopo, solo il proprietario può leggere e scrivere il file. Su Linux e macOS questo passaggio è obbligatorio, perché se gruppo o altri utenti hanno un accesso qualsiasi al file, il file viene ignorato.

chmod 0600 ~/.pgpass

Su Windows il passaggio non serve: si dà per scontato che il file stia in una cartella già protetta, quindi i permessi non vengono controllati. Negli script che girano senza nessuno alla tastiera puoi aggiungere l’opzione -w: il client non chiede mai la password e, se non la trova da nessuna parte (per esempio in .pgpass), il collegamento fallisce. La variabile PGPASSWORD non è descritta nelle pagine consultate, che rimandano alla sezione 32.15 sulle variabili d’ambiente: per questo la guida usa solo il file.

Fare il backup in formato SQL testo

Il formato predefinito è il testo semplice (-Fp, «plain»): un file di comandi SQL che si ripristina con psql. Questo file si carica anche su macchine e architetture diverse e, con qualche modifica, perfino su altri database SQL. L’esempio della pagina di riferimento di pg_dump è questo:

pg_dump mydb > db.sql

La forma generica che compare nella guida al backup è invece questa:

pg_dump dbname > dumpfile

Sostituisci mydb o dbname con il nome del tuo database, e db.sql o dumpfile con il nome del file da creare. pg_dump scrive sull’output standard e il simbolo > manda quell’output nel file; in alternativa puoi usare l’opzione -f seguita dal nome del file.

Il dump fotografa il database nell’istante in cui parte. Intanto gli altri utenti continuano a leggere e scrivere, tranne nelle operazioni che chiedono un lock esclusivo, come quasi tutte le forme di ALTER TABLE. Gli avvisi escono sullo standard error e vanno letti. Se il comando termina senza avvisi, hai un file di testo con i comandi SQL che ricreano il database.

Fare il backup in formato custom, compresso

Il formato custom (-Fc) è compresso in modo predefinito. Al momento del ripristino puoi scegliere quali oggetti caricare e in che ordine, e puoi ripristinare in parallelo. In cambio il file non è uno script per psql e si ripristina solo con pg_restore.

pg_dump -Fc mydb > db.dump

Se PostgreSQL è stato compilato con la libreria zlib, i dati vengono compressi mentre sono scritti nel file, e la dimensione finale è simile a quella di un dump passato a gzip.

Ripristinare un dump SQL testo con psql

psql non crea il database, quindi lo devi creare prima, vuoto, partendo da template0. Il motivo è che i dump sono relativi a template0: se crei il database da un template1 modificato, le aggiunte locali si ritrovano due volte e producono errori di definizioni duplicate.

createdb -T template0 newdb

Ripristinare un dump fa eseguire alla destinazione codice scelto dai superuser del database d’origine. Se non ti fidi di chi amministrava il server d’origine, leggi i comandi SQL del dump prima di caricarlo. Quando sei pronto, caricalo così:

psql -X -d newdb -f db.sql

L’opzione -X fa partire psql con le impostazioni predefinite, senza leggere il file psqlrc. Se incontra un errore SQL, psql di norma prosegue con i comandi successivi. Per farlo fermare al primo errore, la guida al backup propone questa forma:

psql -X --set ON_ERROR_STOP=on dbname < dumpfile

In questo caso, se trova un errore, psql esce con codice 3. Con entrambe le forme, però, un errore lascia il database ripristinato solo in parte.

Se preferisci che il ripristino riesca per intero oppure venga annullato del tutto, psql ha l'opzione -1 (--single-transaction). Qui c'è una condizione da rispettare: secondo la pagina di riferimento di psql, -1 funziona solo insieme a -f o -c. Se la aggiungi alla forma con < dumpfile, dove il dump arriva dallo standard input, il ripristino non parte. Il modo corretto è riprendere il comando con -f mostrato sopra e aggiungere sia -1 sia --set ON_ERROR_STOP=on: se un comando fallisce e ON_ERROR_STOP è attivo, psql invia ROLLBACK al posto di COMMIT e nessuna modifica resta applicata.

Il rovescio è che anche un errore minimo annulla un ripristino che magari andava avanti da ore. Se il dump contiene già comandi BEGIN, COMMIT o ROLLBACK, -1 non ha l'effetto voluto. Al termine del ripristino la documentazione consiglia di eseguire ANALYZE sul database, così il pianificatore delle query ha statistiche utili.

Ripristinare un dump custom in un database nuovo

Per caricare un archivio custom in un database con un altro nome, crea il database vuoto da template0 come prima e collegati direttamente a quello. Gli esempi sono quelli della pagina di riferimento di pg_restore:

createdb -T template0 newdb
pg_restore -d newdb db.dump

Qui l'opzione -C non serve. Se incontra errori, pg_restore va avanti e alla fine dice quanti erano; per farlo fermare al primo errore c'è l'opzione -e (--exit-on-error). Se vuoi leggere il contenuto di un archivio custom senza caricarlo, la documentazione indica l'opzione --file di pg_restore.

Ripristinare un dump custom ricreando il database d'origine

Con -C (--create), pg_restore crea il database prima di riempirlo. Il database che indichi con -d serve solo per lanciare il CREATE DATABASE, mentre i dati finiscono sempre nel database con il nome scritto nell'archivio. Il comando dipende da una condizione: il database d'origine esiste ancora oppure no.

Se il database d'origine non esiste più

L'esempio ufficiale dà per scontato che il database con il nome scritto nell'archivio (nell'esempio mydb) non ci sia più, perché è stato eliminato subito prima con dropdb mydb.

Attenzione: dropdb mydb cancella il database mydb e tutti i suoi dati, in modo definitivo. Prima di eliminarlo controlla che db.dump sia l'archivio giusto e che il dump sia terminato senza errori. Controlla anche di essere collegato al server giusto: senza -h è quello locale o quello indicato da PGHOST. Tutto quello che è entrato nel database dopo il dump va perso. Una volta eliminato il database d'origine, lo ricrei e lo riempi così:

pg_restore -C -d postgres db.dump

Al posto di postgres puoi indicare qualsiasi database che esiste già nel cluster: serve solo come punto di appoggio per il CREATE DATABASE.

Se il database d'origine esiste ancora

Se vuoi sostituirlo con il contenuto dell'archivio, usa --clean insieme a --create.

Attenzione: con --clean e --create insieme, pg_restore elimina il database di destinazione e lo ricrea prima di collegarsi. Tutto il contenuto del database con il nome scritto nell'archivio va perso, compresi i dati aggiunti dopo il dump. Prima di lanciare il comando controlla di essere sul server giusto (senza -h è quello locale o quello indicato da PGHOST) e che db.dump sia l'archivio giusto.

pg_restore -d postgres --clean --create db.dump

Se alcuni degli oggetti da eliminare non esistono, --clean mostra errori che si possono ignorare; aggiungendo --if-exists quegli errori non compaiono. --create non si può usare insieme a --single-transaction.

Copiare un database su un altro server

pg_dump e psql sanno leggere e scrivere attraverso una pipe, quindi il database passa da un server all'altro senza un file intermedio. Prima di lanciare la pipe vanno preparate due cose sul server di destinazione (host2), non su quello locale.

  1. Il database dbname deve esistere su host2, vuoto e creato da template0. Il createdb -T template0 newdb dei passaggi precedenti non indica nessun host, quindi crea il database sul server locale o su quello indicato da PGHOST. Come visto nei prerequisiti, il server si sceglie con -h: al comando createdb aggiungi quindi -h host2 e usa come nome dbname, lo stesso che userai nella pipe.
  2. Su host2 devono esistere già anche i ruoli (vedi l'ultimo passaggio).

Il comando scrive nel database dbname del secondo server: controlla che host2 sia la destinazione e non la produzione. Sostituisci host1 con il server d'origine, host2 con quello di destinazione e dbname con il nome del database.

pg_dump -h host1 dbname | psql -X -h host2 dbname

Vale la regola delle versioni: il caricamento su un server con una versione principale più vecchia di pg_dump non è garantito e può richiedere modifiche manuali al dump, quindi in quel caso il trasferimento diretto tramite pipe va evitato. Fra i metodi di backup descritti dalla documentazione, pg_dump è anche l'unico che funziona quando si passa a un'architettura diversa, per esempio da un server a 32 bit a uno a 64 bit.

Database grandi: formato directory e dump in parallelo

Con -j, pg_dump salva più tabelle nello stesso momento. Funziona solo con il formato directory (-Fd), l'unico in cui più processi possono scrivere insieme. In questo formato -f indica una cartella, che pg_dump crea, a meno che esista già e sia vuota. Esempio con cinque processi:

pg_dump -Fd mydb -j 5 -f dumpdir

Forma generica, con num processi e cartella out.dir:

pg_dump -j num -F d -f out.dir dbname

pg_dump apre tante connessioni quanti sono i processi, più una: per questo il parametro max_connections del server deve essere abbastanza alto, e il carico sul server aumenta. Anche pg_restore accetta -j, con archivi custom o directory, creati con o senza -j. L'input deve essere un file o una cartella, non una pipe, e -j non si combina con --single-transaction. Per scegliere quanti processi usare, la documentazione suggerisce di partire dal numero di core della CPU del server.

Salvare ruoli e oggetti globali con pg_dumpall

pg_dump salva un solo database. Ruoli e tablespace non li salva, perché appartengono all'intero cluster e non ai singoli database. Se ripristini su un altro server, ti serve quindi anche un dump degli oggetti globali, che si ottiene con pg_dumpall e l'opzione --globals-only. Secondo la documentazione, quando salvi i database uno per uno con pg_dump, questo dump è necessario per avere un backup completo del cluster.

Il file ottenuto si carica sul nuovo server prima di ripristinare i singoli database, con psql -X -f seguito dal nome del file e dal database postgres, come la documentazione fa per i dump di pg_dumpall. Per ricreare ruoli e tablespace servono i permessi di superuser. Se usi i tablespace, controlla che i percorsi scritti nel dump vadano bene anche sulla nuova installazione.

Se qualcosa va storto

  • Gli oggetti vengono ricreati senza il proprietario o i permessi originali: sulla destinazione mancano i ruoli. Ripristina prima gli oggetti globali.
  • Compaiono errori di definizioni duplicate: il database di destinazione non era davvero vuoto, oppure è nato da un template1 modificato. Ricrealo da template0.
  • pg_dump si rifiuta di partire: il server ha una versione principale più recente di pg_dump. Usa un pg_dump della stessa versione o più recente.
  • Il ripristino in un'unica transazione non parte: -1 è stato aggiunto alla forma con < dumpfile. Passa il file con -f.
  • Il dump in parallelo si interrompe: un altro client ha chiesto un lock esclusivo su una tabella mentre il dump era in corso, e pg_dump non può andare avanti. Rilancia il dump quando nessuno sta eseguendo sulle tabelle operazioni che chiedono un lock esclusivo, come quasi tutte le forme di ALTER TABLE.
  • La password viene chiesta lo stesso: su Linux e macOS il file .pgpass ha permessi troppo larghi e viene ignorato, oppure nessuna sua riga corrisponde alla connessione.
  • Problemi di connessione in generale: controlla con psql, usando gli stessi parametri, di riuscire a leggere dati dal database.

Documentazione di riferimento

Pagine ufficiali di PostgreSQL consultate il 20 novembre 2026 (documentazione «current», PostgreSQL 19):

  • pg_dump
  • pg_restore
  • PostgreSQL
  • psql