PostgreSQL : Backup einer Datenbank incl. Indexes

Problem : Man möchte eine postgreSQL Datenbank inkl. der Indexes sichern, normalerweise ist die Idee suboptimal aber wenn die Datenbank so groß ist das ein Berechnen der Indexes schon 3 Tage benötigt sichert man diese eben mit.

Lösung : Hier zeige ich einen Weg auf der bei mir funktioniert.

Als erstes legen wir die benötigten Ordner an
mkdir -p /postgres/archives
mkdir -p /postgres/backup
dann passen wir die Konfiguration des Servers an
vi /etc/postgresql/9.4/main/postgresql.conf
ans Ende kommt unsere zusätzliche Konfiguration
max_wal_senders=1
wal_level=hot_standby
archive_mode=on
archive_command='cp %p /postgres/archives/%f'
dann passen wir noch die pg_hba.conf an
vi /etc/postgresql/9.4/main/pg_hba.conf
dort schreiben wir an das Ende
local   replication     postgres                                trust
dann starten wir den Dienst erstmal neu
systemctl restart postgresql
Jetzt können wir ein Backup mit dem Tool pg_basebackup durchführen
pg_basebackup -x -Ft -P -p 5432 -D /postgres/backup/$(date +%Y%m%d)
Wenn der Befehl durchgelaufen ist sollte im Ordner /postgres/backup/[DATUM]/ die Datei base.tar vorhanden sein


Einspielen des Backups
systemctl stop postgresql
in den Data Ordner wechseln z.b. /var/lib/postgresql/9.4/main dort mit tar zurückspielen
tar -xvf /backup/20170726/base.tgz
dann den Server neu starten und glücklich sein
systemctl start postgresql

PostgreSQL : 2. Instanz mit eigener Config auf Server

Problem : Für Testzwecke habe ich auf einem Server eine 2 postgresql Instanz benötigt.

Lösung : Man kann bei Debian basierenden Systemen eine 2. Instanz mit dem Befehl pg_createcluster erzeugen.

In meinem Fall hab ich das so gemacht :
pg_createcluster -u postgres -g postgres -d /var/lib/postgresql/9.4/main-2 -l /var/log/postgresql/ -s /var/run/postgresql/postgresql-9.4-main-2.log -p 5433 --start-conf auto 9.4 main-2 --start

Zur Erklärung :
-u besitzer des clusters (default ist postgres)
-g gruppe der cluster files (default gruppe von postgres also postgres ;-))
-d dort speichert postgres die dazugehörigen Files zu dem Cluster
-l hier wird das Logfile hingeschrieben
-s hier wird das Unix Socketfile geschrieben
--start-conf auto (default ist auto das bedeutet das das normale init Script die Instanz mitstartet)
9.4 main-2 Version der Postgres DB und der Name (main-2) der Instanz
--start startet den node automatisch nach dem erstellen

Nachdem der Befehl durchgelaufen kann man sich den Stand der jeweiligen Instanz mit dem Befehl pg_lsclusters anzeigen.

pg_lsclusters Beispiel
Verbinden kann man sich auf die jeweiligen Instanzen mit dem Befehl
sudo -u postgres psql -p [PORT]

Starten oder Beenden eines Cluster Nodes
sudo -u postgres pg_ctlcluster 9.4 main-2 start
sudo -u postgres pg_ctlcluster 9.4 main-2 stop
Verfügbare Aktionen sind : start / stop / restart / reload / status / promote

“Die Organisationen stecken Millionen von Dollars in Firewalls und Sicherheitssysteme und verschwenden ihr Geld, da keine dieser Maßnahmen das schwächste Glied der Sicherheitskette berücksichtigt: Die Anwender und Systemadministratoren.”
Kevin Mitnick