Frage Was ist der beste Weg, Backups von PostgreSQL-Datenbanken zu automatisieren?


Ich finde es mühsam, jede Woche Datenbanken sichern zu müssen. Und ich denke auch, dass wöchentliche Backups in tägliche Backups umgewandelt werden sollten. Wenn ich das tun müsste, möchte ich es nicht manuell machen. Was ist der beste Weg, um das Backup von PostgreSQL-Datenbanken täglich zu automatisieren?


20
2017-08-28 02:59


Ursprung


Kleiner Hinweis: Das Dumping der Datenbank kann die Performance beenden, einen Cluster verwenden und auf nicht aktive Knoten ausgeben. - neutrinus
Mit diesem kostenlosen Tool können Sie geplante Backups erstellen postgresql-backup.com - Olek Nilson
Nächste Frage: Wie groß sind Sie, welchen generellen Backup-Mechanismus haben Sie? Zum Beispiel werde ich niemals manuell etwas sichern. Installieren Sie den Agenten meines Backup-Systems, wählen Sie die Elemente aus, die in der UI gesichert werden sollen, planen Sie das Backup (in meinem Fall für Datenbanken: alle 5 Minuten) .... fertig. Aber das setzt voraus, dass es genug ist, dass es sinnvoll ist, ein richtiges System zu installieren. - TomTom
Ich würde postgresql-backup.com keine "kostenlose" Lösung nennen. Es ist nur kostenlos für die ersten 2 Datenbanken ... @OlekNilson - Aidan Melen


Antworten:


Genau wie bei jeder anderen sich wiederholenden Aufgabe, die automatisiert werden kann - Sie schreiben ein Skript für die Sicherung und richten dann einen Cron-Job ein, um sie auszuführen.

ein Skript wie das folgende, zum Beispiel:

(Hinweis: Es muss als Postgres-Benutzer oder jeder andere Benutzer mit denselben Privilegien ausgeführt werden)

#! /bin/bash

# backup-postgresql.sh
# by Craig Sanders <cas@taz.net.au>
# This script is public domain.  feel free to use or modify
# as you like.

DUMPALL='/usr/bin/pg_dumpall'
PGDUMP='/usr/bin/pg_dump'
PSQL='/usr/bin/psql'

# directory to save backups in, must be rwx by postgres user
BASE_DIR='/var/backups/postgres'
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
mkdir -p "$DIR"
cd "$DIR"

# get list of databases in system , exclude the tempate dbs
DBS=( $($PSQL --list --tuples-only |
          awk '!/template[01]/ && $1 != "|" {print $1}') )

# first dump entire postgres database, including pg_shadow etc.
$DUMPALL --column-inserts | gzip -9 > "$DIR/db.out.gz"

# next dump globals (roles and tablespaces) only
$DUMPALL --globals-only | gzip -9 > "$DIR/globals.gz"

# now loop through each individual database and backup the
# schema and data separately
for database in "${DBS[@]}" ; do
    SCHEMA="$DIR/$database.schema.gz"
    DATA="$DIR/$database.data.gz"
    INSERTS="$DIR/$database.inserts.gz"

    # export data from postgres databases to plain text:

    # dump schema
    $PGDUMP --create --clean --schema-only "$database" |
        gzip -9 > "$SCHEMA"

    # dump data
    $PGDUMP --disable-triggers --data-only "$database" |
        gzip -9 > "$DATA"

    # dump data as column inserts for a last resort backup
    $PGDUMP --disable-triggers --data-only --column-inserts \
        "$database" | gzip -9 > "$INSERTS"

done

# delete backup files older than 30 days
echo deleting old backup files:
find "$BASE_DIR/" -mindepth 1 -type d -mtime +30 -print0 |
    xargs -0r rm -rfv

EDIT:
pg_dumpall -D switch (Zeile 27) ist veraltet, jetzt ersetzt durch --column-inserts
https://wiki.postgresql.org/wiki/Deprecated_Features


38
2017-08-28 03:31



+1 für ein tolles Skript - rkthkr
Ich benutze etwas sehr ähnliches als PreDumpCmd für backuppc, mit der Ausnahme, dass ich das Datum nicht in den Pfad codiere, da backuppc mehrere Kopien aufbewahrt. - David Pashley
Tolles Skript, aber ich fand, dass ich die Regex so optimieren musste, dass sie keine Pipes und Leerzeilen als Datenbanknamen enthielt. DBS=$($PSQL -l -t | egrep -v 'template[01]' | awk '{print $1}' | egrep -v '^\|' | egrep -v '^$') - s29
@ s29 Ich denke, es ist besser, eine direkte Abfrage anstelle von all dem grep Hacker zu verwenden, wie: DBS = ($ ($ {PSQL} -t -A -c) wähle datname von pg_database wo datname nicht in ('template0', ' template1 ') ")) - PolyTekPatrick


pg_dump dbname | gzip > filename.gz

Neu laden mit

createdb dbname
gunzip -c filename.gz | psql dbname

oder

cat filename.gz | gunzip | psql dbname

Benutzen split. Das split Mit dem Befehl können Sie die Ausgabe in Teile aufteilen, deren Größe dem zugrunde liegenden Dateisystem entspricht. Um zum Beispiel Stücke von 1 Megabyte zu machen:

pg_dump dbname | split -b 1m - filename

Neu laden mit

createdb dbname
cat filename* | psql dbname

Du könntest einen von denen hineinwerfen /etc/cron.hourly

Ausgeliefert von http://www.postgresql.org/docs/8.1/interactive/backup.html#BACKUP-DUMP-ALL


6
2017-08-28 03:29



Das Teilen der Datei ist eine großartige Idee. Es ist besser, den Dump zu teilen, indem split -C, so dass eine Linie niemals geteilt wird. Das Debuggen einer fehlgeschlagenen Wiederherstellung ist einfacher. - Gianluca Della Vedova


Welche Befehle Sie auch immer "von Hand" ausgeben, - schreiben Sie sie in das Skript und legen Sie den Aufruf dieses Skripts in cron oder in einem anderen Scheduler an.

Sie können das Skript natürlich etwas ausgefallener gestalten, aber im Allgemeinen denke ich, dass Sie dort ankommen werden - fangen Sie einfach an und verfeinern Sie später.

Einfachstes mögliches Skript:

#!/bin/bash
/usr/local/pgsql/bin/pg_dumpall -U postgres -f /var/backups/backup.dump

Speichern Sie es unter /home/randell/bin/backup.sh, fügen Sie es zu cron hinzu:

0 0 * * 0 /home/randell/bin/backup.sh

2
2017-08-28 06:58





Wenn Sie einen vollständigen Cluster mit minimaler Systemlast sichern möchten, tarieren Sie einfach das Stammverzeichnis des postgresql-Clusters. zum Beispiel:

echo "select pg_start_backup('full backup - `date`');" | psql
/usr/bin/rdiff-backup --force --remove-older-than 7D $BACKUP_TARGET
/usr/bin/rdiff-backup --include '/etc/postgresql' --include $PGDATA --exclude '/*' / $BACKUP_TARGET
/bin/tar -cjf /mnt/tmp/$SERVER_NAME.tbz2 $BACKUP_TARGET 2>&1
echo "select pg_stop_backup();" | psql

Das ist der Großteil meines Backup-Skripts.


0
2017-08-28 22:05



Nein, das funktioniert nicht, wenn Sie die WAL-Archivierung nicht aktiviert haben. - Peter Eisentraut


für den Fall, dass jemand seine Postgres auf einer Windows-Maschine ohne die Hilfe von Cygwin usw. sichern muss, habe ich eine Batch-Datei, die den Job recht gut erledigt.

Dadurch werden die Datenbanken jeden Tag in einzelnen Dateien in einem eigenen Verzeichnis gesichert

set dtnm=%date:~-4,4%%date:~-7,2%%date:~0,2%
set bdir=D:\backup\%dtnm%
mkdir %bdir%

FOR /F "tokens=1,2 delims=|" %%a IN ('psql -l -t -A -U postgres') DO (
    IF %%b EQU postgres pg_dump -U postgres -f %bdir%\%%a.sql.gz -Z 9 -i %%a
)

0
2018-02-03 18:38