postgresql
Backup e ripristino
Ricerca…
Osservazioni
pg_dumpall backup del filesystem invece di usare pg_dumpall e pg_dump
È molto importante che se lo usi, chiami la funzione pg_start_backup() prima e la funzione pg_stop_backup() dopo. In caso contrario, il backup del filesystem non è sicuro; anche un'istantanea di ZFS o FreeBSD del file system di cui è stato eseguito il backup senza quelle chiamate di funzione collocherà il database in modalità di ripristino e potrebbe perdere le transazioni.
Eviterei di fare backup del filesystem invece dei regolari backup di Postgres, sia per questo motivo, sia perché i file di backup di Postgres (specialmente nel formato personalizzato) sono estremamente versatili nel supportare i ripristini alternativi. Dal momento che sono file singoli, sono anche meno problematici da gestire.
Backup di un database
pg_dump -Fc -f DATABASE.pgsql DATABASE
Il -Fc seleziona il "formato di backup personalizzato" che ti dà più potenza di SQL raw; vedi pg_restore per maggiori dettagli. Se vuoi un file SQL vanilla, puoi farlo invece:
pg_dump -f DATABASE.sql DATABASE
o anche
pg_dump DATABASE > DATABASE.sql
Ripristino dei backup
psql < backup.sql
Un'alternativa più sicura utilizza -1 per avvolgere il ripristino in una transazione. -f specifica il nome del file piuttosto che usare il reindirizzamento della shell.
psql -1f backup.sql
I file in formato personalizzato devono essere ripristinati utilizzando pg_restore con l'opzione -d per specificare il database:
pg_restore -d DATABASE DATABASE.pgsql
Il formato personalizzato può anche essere riconvertito in SQL:
pg_restore backup.pgsql > backup.sql
Si consiglia di utilizzare il formato personalizzato perché è possibile scegliere quali elementi ripristinare e, facoltativamente, abilitare l'elaborazione parallela.
Potrebbe essere necessario fare un pg_dump seguito da un pg_restore se si esegue l'aggiornamento da una versione postgresql a una nuova.
Backup dell'intero cluster
$ pg_dumpall -f backup.sql
Questo funziona dietro le quinte realizzando più connessioni al server una volta per ogni database ed eseguendo pg_dump su di esso.
A volte, potresti essere tentato di impostarlo come cron job, quindi vuoi vedere la data in cui il backup è stato preso come parte del nome file:
$ postgres-backup-$(date +%Y-%m-%d).sql
Tuttavia, si noti che questo potrebbe produrre file di grandi dimensioni su base giornaliera. Postgresql ha un meccanismo molto migliore per i backup regolari: gli archivi WAL
L'output di pg_dumpall è sufficiente per ripristinare un'istanza Postgres con configurazione identica, ma i file di configurazione in $PGDATA ( pg_hba.conf e postgresql.conf ) non fanno parte del backup, quindi dovrai eseguirne il backup separatamente.
postgres=# SELECT pg_start_backup('my-backup');
postgres=# SELECT pg_stop_backup();
Per eseguire un backup del filesystem, è necessario utilizzare queste funzioni per garantire che Postgres sia in uno stato coerente durante la preparazione del backup.
Usando Copia per importare
Copiare dati da un file CSV in una tabella
COPY <tablename> FROM '<filename with path>';
Per inserire nella tabella user da un file denominato user_data.csv inserito in /home/user/ :
COPY user FROM '/home/user/user_data.csv';
Per copiare i dati dal file separato da pipe alla tabella
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
Nota: in assenza dell'opzione with delimiter , il delimitatore predefinito è una virgola ,
Ignorare la riga di intestazione durante l'importazione del file
Usa l'opzione Intestazione:
COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;
Nota: se i dati sono quotati, per impostazione predefinita i caratteri di virgolette sono doppi apici. Se i dati sono quotati utilizzando qualsiasi altro carattere, utilizzare l'opzione QUOTE ; tuttavia, questa opzione è consentita solo quando si utilizza il formato CSV.
Usando Copia per esportare
Per copiare la tabella su standard o / p
COPIA <tablename> TO STDOUT (DELIMITER '|');
Per esportare l'utente della tabella su Uscita standard:
COPY user TO STDOUT (DELIMITER '|');
Per copiare la tabella nel file
COPY utente DA '/ home / utente / user_data' WITH DELIMITER '|';
Copiare l'output dell'istruzione SQL nel file
COPY (istruzione sql) TO '<nome file con percorso>';
COPIA (SELEZIONA * DA utente WHERE user_name LIKE 'A%') A '/ home / utente / user_data';
Per copiare in un file compresso
COPY user TO PROGRAM 'gzip> /home/user/user_data.gz';
Qui programma gzip viene eseguito per comprimere i dati della tabella utente.
Usando psql per esportare i dati
I dati possono essere esportati utilizzando il comando copia o utilizzando le opzioni della riga di comando del comando psql.
Per esportare dati csv da utente tabella a file csv:
psql -p \<port> -U \<username> -d \<database> -A -F<delimiter> -c\<sql to execute> \> \<output filename with path>
psql -p 5432 -U postgres -d test_database -A -F, -c "select * from user" > /home/user/user_data.csv
Qui la combinazione di -A e -F fa il trucco.
-F indica il delimitatore
-A or --no-align
Passa alla modalità di uscita non allineata. (La modalità di output predefinita è diversamente allineata).