Consigli tecnologici: Come eseguire il backup e il ripristino del database Postgres?
·
19 giu 2013
Punti Chiave
I backup di PostgreSQL possono mirare a una singola tabella, un singolo database o tutti i database a seconda delle tue esigenze.
pg_dumpcrea un file di testo SQL contenente DDL e dati necessari per ricreare la tabella o il database target.pg_dumpallesegue il backup di ogni database sull'istanza Postgres.Il ripristino viene fatto con
psql, che esegue il dump SQL di nuovo nel database target.I ripristini a livello di tabella richiedono di garantire che la tabella non esista già per evitare conflitti.
Quando si eseguono backup di tabelle individuali, fai attenzione alle dipendenze relazionali.
I dump possono essere presi da locale e ripristinati su istanze Postgres remote.
I backup includono i comandi
CREATE,ALTEReCOPYa seconda di ciò che viene esportato.Una corretta strategia di backup dipende dalla comprensione delle relazioni tra le tabelle, dell'uso dello schema e degli ambienti di distribuzione.
Assicurati sempre dell'integrità dei dati eseguendo backup che includano tabelle referenziate quando necessario.
Punti salienti del Q&A
Cosa fa effettivamente pg_dump?
pg_dumpgenera un file SQL basato su testo contenente l'insieme completo di comandi necessari per ricreare una tabella o un database esattamente come esisteva al momento del backup.Posso eseguire il backup solo di una tabella invece di un intero database?
Sì. Usa
pg_dump --table NOME_TABELLAper esportare una tabella specifica. Se esistono tabelle con lo stesso nome in più schemi, usa--schemaper specificare quello corretto.Dovrei preoccuparmi delle tabelle correlate quando eseguo il backup di una singola tabella?
Assolutamente. PostgreSQL è relazionale, quindi altre tabelle potrebbero fare riferimento a quella che stai eseguendo il backup. Se ripristini solo una tabella senza quelle correlate, potresti compromettere l'integrità referenziale.
Come faccio a eseguire il backup di un database completo?
Esegui un comando come:
pg_dump -U <user> <database> -f <output.sql>Questo esporta tutte le tabelle, le definizioni degli schemi, gli indici e i dati per quel database.
Cosa fa pg_dumpall?
pg_dumpallesegue il backup di tutti i database sul server in un unico file SQL. È utile durante la migrazione o la creazione di snapshot di un'intera istanza Postgres.Posso ripristinare una tabella in un database esistente?
Sì. Usa
psql -U <user> -f <file.sql> <database>.Ma assicurati che la tabella non esista già — altrimenti riceverai errori di "esiste già" durante il ripristino.
Cosa succede durante un ripristino completo del database?
Il file di ripristino viene eseguito:
CREA TABELLAALTERA TABELLACOPIA(per i dati della tabella)
riportando il database al suo stato esatto al momento del dump.
Posso ripristinare su un server diverso da quello da cui ho effettuato il backup?
Sì. I dump sono portabili. Puoi eseguire un backup localmente e ripristinarlo su un'istanza Postgres remota, a condizione che le versioni siano compatibili.
pg_dump influisce sul database in esecuzione?
No. Funziona tramite query SQL e non blocca l'intero database. Acquisisce in modo sicuro uno snapshot coerente.
Come posso ripristinare tutti i database da un file pg_dumpall?
Usa:
psql -U <user> -f all_dump.sqlQuesto ricrea ogni database trovato nel dump.
In primo luogo, una dichiarazione. Questo post fornisce una guida generale su come eseguire il backup di una tabella del database Postgres. Gli esempi forniti non si riferiscono a nessuna tabella o database specifico. Se stai cercando di eseguire il backup di una tabella specifica, dovresti anche eseguire il backup delle tabelle che fanno riferimento alle tabelle originali. Poiché Postgres è un database relazionale, ci saranno sempre riferimenti tra un certo numero di tabelle.
Questo è un documento ‘come fare’ che fornisce solo comandi di esempio; come viene effettivamente fatto dipende esclusivamente dalla persona che lo esegue e dai casi d'uso specifici. Mantieni sempre l'integrità dei dati che stai cercando di eseguire il backup. Eseguire il backup dell'intero database è sempre un'opzione sicura, ma quando si eseguono il backup di tabelle specifiche bisogna essere cauti.
SQL-dump/pg_dump:
L'idea dietro il metodo SQL-dump è quella di generare un file di testo con comandi SQL che, una volta reimmessi nel server, ricreerà il database nello stesso stato in cui si trovava al momento del dump. PostgreSQL fornisce il programma di utilità pg_dump a questo scopo.
pg_dump è uno strumento efficace e completo per eseguire il backup dei database Postgres e utilizzare i backup per ripristinare il database postgres. Tuttavia, non è limitato solo ai database. Possiamo usare pg_dump per eseguire il backup delle tabelle e poi usarlo per ripristinare anche singole tabelle. Utilizzando pg_dump puoi eseguire il backup del database locale e ripristinarlo su un database remoto.
Come eseguire il backup del database Postgres:
Come ripristinare il database Postgres
Panoramica delle opzioni di ripristino di Postgres
Ambito di ripristino | Strumento utilizzato | File di input | Requisito chiave | Problema comune |
|---|---|---|---|---|
Singola tabella | psql | Dump della tabella | La tabella non deve già esistere | Errori di “già esiste” |
Singolo database | psql | Dump del database | Il DB di destinazione deve esistere | Scrittura involontaria di dati |
Tutti i database | psql | Dump completo | Accesso superuser raccomandato | Ripristino di più di quanto previsto |
Ripristina una tabella Postgres :
$ /opt/msys/3rdParty/bin/psql -U ecuser -f ms_table.sql peIl comando sopra installerà la tabella che è stata eseguita nel file ms_table.sql nel database ‘pe’. Assicurati che questa tabella non esista già, altrimenti vedrai molti errori di ‘già esiste’. Questo comando crea la tabella ed esporta tutti i dati nella nuova tabella creata.
Ripristina un database Postgres :
$ /opt/msys/3rdParty/bin/psql -U ecuser -d pe -f pe_dump.sqlSimile al ripristino della tabella, possiamo usare il comando sopra per ripristinare l'intero database. Qui stiamo ripristinando il database ‘pe’ utilizzando il file pe_dump.sql che abbiamo creato durante il backup del database nella sezione Backup sopra.
Ripristina tutti i database :
$ /opt/msys/3rdParty/bin/psql -u ecuser -f all_dump.sqlRipristina tutti i database utilizzando il comando sopra. ‘all_dump.sql’ era il file creato utilizzando
pg_dumpall. Il comando sopra ci fornirà tutti i database Postgres nello stato esatto in cui si trovavano quando è stato eseguito un dump dal server del database originale.
