Postgres DB Remote Backup

Für die folgende Anleitung wird auf dem Datenbank System ein openSSH Server benötigt.

Postgres Backups werden über die Anwendung pg_dump erstellt.

SHH einrichten

Benötigt wird einen User, der per SSH Zugriff auf den Server hat. Für ein automatisiertes Backup wird die Authentifizierung per PubKey ohne Kennwort eingerichtet, da somit kein Passwort in den Skripten gespeichert werden muss. Siehe Digitalocean

Datenbank Zugangsdaten

Wir erstellen auf dem Datenbanksystem (Unix oder Windows) eine .pgpass Datei im Home Verzeichnis des Users, welchen wir später für die SSH Anmeldung verwenden. Die .pgpass Datei wird benötigt damit man das Tool pg_dump ohne Datenbank Passwort ausführen kann. Die Zugangsdaten werden in der .pgpass Datei gespeichert. Die Datei ist wie folgt aufgebaut:

hostname:port:database:username:password

Anschließen müssen die Berechtigungen der Datei auf den User beschränkt werden

chmod 600 .pgpass

Offizielle Dokumentation / StackOverflow

Ob das Datenbank Backup ohne Kennwort funktioniert (also ob auch wirklich die .pgpass Datei verwendet wird) kann man das Backup lokal testen.

pg_dump -h localhost -U dbuser myDatabase -C > myDatebaseBackup.dump

Bekommt man eine Aufforderungen zur Kennworteingabe stimmt etwas noch nicht mit der .pgpass Datei oder dem Zugriff.

OneLine Backup

Auf dem Client, von wo aus das Backup gestartet und gespeichert werden soll wird folgender Befehl per SSH ausgeführt, über stdout wird das Backup lokal gespeichert.

ssh user@dbserver -i "C:\Users\Admin\id_rsa" "pg_dump -h localhost -U dbuser myDatabase -C" > myDatebaseBackup.dump

Damit der Befehl klappt muss natürlich grundsätzlich die Verbindung per SSH per PubKey Authentifizierung funktionieren.

Sicherheit

Da der SSH PrivateKey kein Kennwort hat, sollte der Zugriff für das KeyPair-Value weiter eingeschränkt werden. Hierzu kann die authorized_key Datei editiert werden, so dass nur genau der Befehl erlaubt wird, welchen wir benötigen. Am besten wird dies über ein zusätzliches Skript geprüft. Mehr dazu hier: SSH Sicherheit unter Windows