postgresql
Sichern und Wiederherstellen
Suche…
Bemerkungen
pg_dumpall des Dateisystems anstatt pg_dumpall und pg_dump
Es ist sehr wichtig, dass Sie in diesem pg_start_backup() Funktionen pg_start_backup() vor und pg_stop_backup() danach pg_stop_backup() . Das Sichern von Dateisystemen ist ansonsten nicht sicher. Selbst ein ZFS- oder FreeBSD-Snapshot des Dateisystems, das ohne diese Funktionsaufrufe gesichert wurde, versetzt die Datenbank in den Wiederherstellungsmodus und kann Transaktionen verlieren.
Ich würde vermeiden, Dateisystem-Backups anstelle von regulären Postgres-Backups durchzuführen, sowohl aus diesem Grund als auch, weil Postgres-Backup-Dateien (insbesondere im benutzerdefinierten Format) äußerst vielseitig sind, um alternative Wiederherstellungen zu unterstützen. Da es sich um Einzeldateien handelt, sind sie auch weniger umständlich zu verwalten.
Eine Datenbank sichern
pg_dump -Fc -f DATABASE.pgsql DATABASE
Das -Fc wählt das "benutzerdefinierte Sicherungsformat" aus, das Ihnen mehr Leistung als -Fc SQL bietet. Weitere Informationen finden Sie unter pg_restore . Wenn Sie eine Vanilla-SQL-Datei benötigen, können Sie stattdessen Folgendes tun:
pg_dump -f DATABASE.sql DATABASE
oder auch
pg_dump DATABASE > DATABASE.sql
Backups wiederherstellen
psql < backup.sql
Eine sicherere Alternative verwendet -1 , um die Wiederherstellung in eine Transaktion einzuwickeln. -f gibt den Dateinamen an, anstatt die Shell-Umleitung zu verwenden.
psql -1f backup.sql
Benutzerdefinierte pg_restore müssen mit pg_restore und der Option -d wiederhergestellt werden, um die Datenbank anzugeben:
pg_restore -d DATABASE DATABASE.pgsql
Das benutzerdefinierte Format kann auch wieder in SQL konvertiert werden:
pg_restore backup.pgsql > backup.sql
Die Verwendung des benutzerdefinierten Formats wird empfohlen, da Sie auswählen können, welche Elemente wiederhergestellt werden sollen, und optional die parallele Verarbeitung aktivieren.
Möglicherweise müssen Sie einen pg_dump gefolgt von einem pg_restore ausführen, wenn Sie von einer postgresql-Version auf eine neuere aktualisieren.
Das gesamte Cluster sichern
$ pg_dumpall -f backup.sql
Dies funktioniert im Hintergrund, indem mehrere Verbindungen zum Server einmal für jede Datenbank hergestellt und pg_dump auf dieser Datenbank ausgeführt werden.
Manchmal können Sie versucht sein, dies als Cron-Job einzurichten. Sie möchten also das Datum sehen, an dem die Sicherung als Teil des Dateinamens erstellt wurde:
$ postgres-backup-$(date +%Y-%m-%d).sql
Beachten Sie jedoch, dass dies täglich große Dateien erzeugen kann. Postgresql hat einen viel besseren Mechanismus für regelmäßige Backups - WAL-Archive
Die Ausgabe von pg_dumpall reicht aus, um eine identisch konfigurierte Postgres-Instanz wiederherzustellen. Die Konfigurationsdateien in $PGDATA ( pg_hba.conf und postgresql.conf ) sind jedoch nicht Teil der Sicherung. Sie müssen sie daher separat sichern.
postgres=# SELECT pg_start_backup('my-backup');
postgres=# SELECT pg_stop_backup();
Um eine Sicherung des Dateisystems durchzuführen, müssen Sie diese Funktionen verwenden, um sicherzustellen, dass sich Postgres in einem konsistenten Zustand befindet, während die Sicherung vorbereitet wird.
Kopieren zum Importieren verwenden
So kopieren Sie Daten aus einer CSV-Datei in eine Tabelle
COPY <tablename> FROM '<filename with path>';
Einfügen eines user in eine Tabelle mit dem Namen user_data.csv in /home/user/ :
COPY user FROM '/home/user/user_data.csv';
So kopieren Sie Daten aus einer durch Pipes getrennten Datei in eine Tabelle
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
Hinweis: Ohne die Option with delimiter ist das Standardbegrenzungszeichen Komma ,
Kopfzeile beim Importieren ignorieren
Verwenden Sie die Header-Option:
COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;
Hinweis: Wenn Daten in Anführungszeichen stehen, sind die Anführungszeichen der Daten standardmäßig in doppelte Anführungszeichen gesetzt. Wenn die Daten mit einem anderen Zeichen zitiert werden, verwenden Sie die Option QUOTE . Diese Option ist jedoch nur bei Verwendung des CSV-Formats zulässig.
Kopieren zum Exportieren verwenden
So kopieren Sie die Tabelle in den Standardbetrieb
COPY <Tabellenname> TO STDOUT (DELIMITER '|');
So exportieren Sie den Tabellenbenutzer in die Standardausgabe:
Benutzer KOPIEREN ZU STDOUT (DELIMITER '|');
Tabelle in Datei kopieren
Benutzer KOPIEREN FROM '/ home / user / user_data' WITH DELIMITER '|';
So kopieren Sie die Ausgabe der SQL-Anweisung in eine Datei
COPY (SQL-Anweisung) TO '<Dateiname mit Pfad>';
COPY (SELECT * FROM Benutzer WO Benutzer_Name LIKE 'A%') TO '/ Home / Benutzer / Benutzerdaten';
So kopieren Sie in eine komprimierte Datei
Benutzer KOPIEREN ZUM PROGRAMM 'gzip> /home/user/user_data.gz';
Hier wird das Programm gzip ausgeführt, um die Daten der Benutzertabellen zu komprimieren.
Verwenden von psql zum Exportieren von Daten
Daten können mit dem Kopierbefehl oder mit den Befehlszeilenoptionen des Befehls psql exportiert werden.
So exportieren Sie CSV-Daten aus dem Tabellenbenutzer in die CSV-Datei:
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
Hier macht die Kombination von -A und -F den Trick.
-F ist als Trennzeichen anzugeben
-A or --no-align
Wechselt in den nicht ausgerichteten Ausgabemodus. (Der Standardausgabemodus ist ansonsten ausgerichtet.)