PostgreSQL

SQL Dump

L'idea dietro a questo metodo di dump è quello di generare un file con comandi SQL che, quando eseguiti dal server, ricrea il database nello stesso stato come quando è stato eseguito il dump. PostgreSQL fornisce uno strumento che si chiama pg_dump che serve proprio a questo scopo. L'uso base di qeusto comando è il seguente:

pg_dump dbname > dumpfile

Come è facilmente intuibile, il risultato viene scritto nello standard output (nell'esempio precedente è reindirizzato verso il file dumpfile). Si vedrà più avanti come questo può essere utile, infatti pg_dump può creare file in altri formati oltre a quelli testuali; è un client PostgresSQL classico, ciò significa che può eseguire i backup da qualsiasi postazione remota che abbia un accesso al database. Bisogna ricordarsi che pg_dump non opera con alcun permesso speciale. In particolare, deve avere un accesso in lettura a tutte le tabelle delle quali si vuole fare un backup, mentre per eseguire un backup di tutto il database è necessario avviarlo come super-utente (se non si hanno abbastanza privilegi per eseguire il backup di un intero database, è comunque possibile farlo tramite le opzioni -n schema o -t tabella).

Per specificare quale database pg_dump debba contattare, si usa l'opzione -h host ed eventualmente, specificandone la porta con -p porta. L'host predefinito è localhost o se impostata, viene recuperato il valore dalla variabile d'ambiente PGHOST, analogamente, la porta predefinita è la 5432, altrimenti viene recuperata dalla variabile d'ambiente PGPORT.

Come qualsiasi altro client di PostgreSQL, pg_dump si connette usando la username dell'utente che attualmente sta usando il sistema operativo. Per poter specificare un'altra utenza, si usa l'opzione -U utente o impostare la variabile d'ambiente PGUSER.

Tipicamente, i dump generati con pg_dump possono essere caricati anche nelle nuove versioni di PostgreSQL e sono internamente consistenti, ciò significa che rappresentano un'esatta immagine del database nell'istante in cui l'operazione di backup è stata avviata. Si consideri inoltre che pg_dump non blocca le altre operazioni sul database mentre questo sta lavorando (eccezion fatta per quelle operazioni necessitano di un lock esclusivo, come le più diffuse varianti di ALTER TABLE.

Ripristinare il dump

Il file creati con pg_dump sono pensati per essere letti dal programma psql. Il comando generico per ripristinare un dump è il seguente:

psql dbname < dumpfile

Dove dumpfile è il file di output generato tramite il comando pg_dump. Il database dbname non viene creato con questo comando, quindi bisogna crearlo manualmente da template0 prima di eseguire psql (per esempio con createdb -T template0 dbname). psql supporta le stesse opzioni di pg_dump per specificare il server al quale connettersi e l'utenza da usare. I dump che non sono salvati su file devono essere ripristinati tramite lo strumento pg_restore.

Prima di ripristinare un dump SQL, tutti gli utenti che sono proprietari o che hanno permessi su oggetti che sono finiti nel dump devono essere già presenti, se non lo sono, il ripristino fallirà cercando di ricreare gli oggetti con i permessi originali (qualche volta è quello che si vuole, ma tipicamente non è così).

psql in modo predefinito continua l'esecuzione dopo che si verifica un errore SQL, si potrebbe desiderare di eseguire psql con la variabile ON_ERROR_STOP impostata per alterarne il comportamento ed avere psql che si interrompe con un exit status pari a 3.

psql --set ON_ERROR_STOP=on dbname < dumpfile

Altrimenti, si ottiene un database ripristinato parzialmente. Altrimenti è possibile specificare che un intero dump debba essere ripristinato come una singola transazione, quindi il restore è completamente ripristinato o viene effettauto un rollback completo. Questa modalità può essere specificata passando l'opzione -1 o --single-transaction. Quando si usa questa modalità, bisogna essere consapevoli che anche un errore minore può causare un rollback di un ripristino che è stato eseguito molte ore prima. Comunque, è sempre preferibile piuttosto che pulire manualmente un database complesso dopo un ripristino parziale.

L'abilità di pg_dump e pssql di scrivere e leggere dalle pipe, rende possibile la creazione di un dump direttamente da un server ad un altro, per esempio:

pg_dump -h host1 dbname | psql -h host2 dbname

IMPORTANTE: i dump prodotti da pg_dump sono relativi a template0, questo significa che qualsiasi lingauggio, procedura, ecc. aggiunti tramite template1 saranno messi nel dump da pg_dump. Come risultato, quando si esegue il ripristino, se si sta usando un template1 personalizzato, si deve creare un database vuoto da template0, come nell'esempio precedente.

Usare pg_dumpall

pg_dump effettua il dump di un singolo database alla volta, non effettua il dump delle informazioni su ruoli e tablespace. Per effettuare il dump di un intero cluster di database, esiste il programma pg_dumpall. L'utilizzo base del comando è il seguente:

pg_dumpall > dumpfile

Il dump che ne risulta può essere ripristinato tramite psql:

psql -f dumpfile postgres

Gestire grandi database

Alcuni sistemi operativi hanno i limiti massimi sulla dimensione dei file che potrebbero causare problemi creando grandi file con pg_dump. Fortunatamente, pg_dump può scrivere sullo standard output, così lo si può usare insieme agli strumenti standard di Unix per aggirare questo potenziale problema. Ci sono molteplici metodi:

Usare dei dump compressi:

Si può usare qualsiasi programma di compressione che si preferisce, ad esempio con gzip

pg_dump dbname | gzip > filename.gz

Si ripristina con:

gunzip -c filename.gz | psql dbname

Oppure

cat filename.gz | gunzip | psql dbname

Use split. The split command allows you to split the output into smaller files that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:

pg_dump dbname | split -b 1m - filename

Reload with:

cat filename* | psql dbname

Use pg_dump's custom dump format. If PostgreSQL was built on a system with the zlib compression library installed, the custom dump format will compress data as it writes it to the output file. This will produce dump file sizes similar to using gzip, but it has the added advantage that tables can be restored selectively. The following command dumps a database using the custom dump format:

pg_dump -Fc dbname > filename

A custom-format dump is not a script for psql, but instead must be restored with pg_restore, for example:

pg_restore -d dbname filename

See the pg_dump and pg_restore reference pages for details.

For very large databases, you might need to combine split with one of the other two approaches.

Use pg_dump's parallel dump feature. To speed up the dump of a large database, you can use pg_dump's parallel mode. This will dump multiple tables at the same time. You can control the degree of parallelism with the -j parameter. Parallel dumps are only supported for the "directory" archive format.

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

You can use pg_restore -j to restore a dump in parallel. This will work for any archive of either the "custom" or the "directory" archive mode, whether or not it has been created with pg_dump -j.